Board index » delphi » SQL-Typecasting to boolean ?

SQL-Typecasting to boolean ?

I'd like to create a table like

SELECT Name, Amount, TooHeavy
FROM xyz
where Amount>0

And TooHeavy shold be a boolean value
that is true for Amount>100,
else false

It doen't work with
SELECT Name, Amount , Amount>100 TooHeavy
Delphi doesn't like the ">" in the Select-Statement.

Can this be achieved in another way ?

Any Ideas,
Post here AND EMAIL (please!!)

Thanks,
Ralf S.

 

Re:SQL-Typecasting to boolean ?


Hi there,

you could try a calculated field in the TQuery or TTable component. Right
click, select 'Create New Field' and select calculated. Then, in the
OnCalculateField-event, set the field to True if the amount > 100.
--
Martijn Tonies

<- remove some characters from my email adress to reply ->
m.ton...@somecharacters.upscene.demon.nl
http://www.upscene.demon.nl

Ralf Steinhaeusser heeft geschreven in bericht
<37246660.53EED...@sbox.tu-graz.ac.at>...

Quote
>I'd like to create a table like

>SELECT Name, Amount, TooHeavy
>FROM xyz
>where Amount>0

>And TooHeavy shold be a boolean value
>that is true for Amount>100,
>else false

>It doen't work with
>SELECT Name, Amount , Amount>100 TooHeavy
>Delphi doesn't like the ">" in the Select-Statement.

>Can this be achieved in another way ?

>Any Ideas,
>Post here AND EMAIL (please!!)

>Thanks,
>Ralf S.

Re:SQL-Typecasting to boolean ?


Quote
>SELECT Name, Amount, TooHeavy
>FROM xyz
>where Amount>0

>And TooHeavy shold be a boolean value
>that is true for Amount>100,
>else false

>It doen't work with
>SELECT Name, Amount , Amount>100 TooHeavy
>Delphi doesn't like the ">" in the Select-Statement.

>Can this be achieved in another way ?

>Any Ideas,
>Post here AND EMAIL (please!!)

Now is TooHeavy a column in your table or a variable that you are trying to do
inside of SQL?  What DB are you using?  Delphi isn't the one that doesn't like
the Select statement, it's the DB you are using that doesn't like it.

What you could try (if I'm understanding what you are trying to do) is
something like this:

Select Name, Amount, 'True' As TooHeavy
from xyz
where
Amount > 100

HTH

********************************
Michael Glatz              
glatzfa...@aol.com
mgl...@briefcase.com
Accept that some days you're the pigeon,
some days you're the statue.

Re:SQL-Typecasting to boolean ?


btw :
I just want to show whether a triggervalue is exceeded
or not - just an extra info - in a dbgrid

And I'm using Paradox-tables

that's all for now...

Re:SQL-Typecasting to boolean ?


On Mon, 26 Apr 1999 15:13:04 +0200, Ralf Steinhaeusser

Quote
<r...@sbox.tu-graz.ac.at> wrote:
>I'd like to create a table like

>SELECT Name, Amount, TooHeavy
>FROM xyz
>where Amount>0

>And TooHeavy shold be a boolean value
>that is true for Amount>100,
>else false

>It doen't work with
>SELECT Name, Amount , Amount>100 TooHeavy
>Delphi doesn't like the ">" in the Select-Statement.

>Can this be achieved in another way ?

Ordinarily, this could be handled using a CASE construct, part of the
SQL-92 specification. However, since you are using Paradox tables, local
SQL rules apply. Local SQL -- while a subset of SQL-92 -- simply does not
include this construct in that subset. You can do what you ask in local SQL
through other means, though.

One way you can do this in local SQL is with a UNION join concatenating two
SELECT statements. Each of the two sub-statements would return rows that
meet one of two criteria: the Amount column contains a value greater than
100 or Amount is less than 100. A literal BOOLEAN value is given to the
TooHeavy column in these statements: a value of TRUE in the result set
where the first criteria is met and FALSE when the the second criteria is
met.

  SELECT Name, Amount, TRUE AS TooHeavy
  FROM xyz
  WHERE (Amount > 100)
  UNION ALL
  SELECT Name, Amount, FALSE
  FROM xyz
  WHERE (Amount > 0)

//////////////////////////////////////////////////////////////////////////
Steve Koterski                   "There are two kinds of pedestrians...the
Technical Publications           quick and the dead."
INPRISE Corporation                            -- Lord Thomas Robert Dewar
http://www.borland.com/delphi                                  (1864-1930)

Re:SQL-Typecasting to boolean ?


On Mon, 26 Apr 1999 16:27:06 GMT, skoter...@NOSPAMinprise.com (Steve

Quote
Koterski) wrote:

[...]

Looking at what I posted, believe the SQL was not quite what was needed.
Instead of this...

Quote
>  SELECT Name, Amount, TRUE AS TooHeavy
>  FROM xyz
>  WHERE (Amount > 100)
>  UNION ALL
>  SELECT Name, Amount, FALSE
>  FROM xyz
>  WHERE (Amount > 0)

...the statement should be as below. The WHERE clause of the second SELECT
should filter to rows where the Amount column is greater than zero *and*
less than 100. Otherwise, the greater-than-100 rows would be in both SELECT
result sets.

  SELECT Name, Amount, TRUE AS TooHeavy
  FROM xyz
  WHERE (Amount > 100)
  UNION ALL
  SELECT Name, Amount, FALSE
  FROM xyz
  WHERE (Amount > 0) AND (Amount <= 100)

//////////////////////////////////////////////////////////////////////////
Steve Koterski                   "There are two kinds of pedestrians...the
Technical Publications           quick and the dead."
INPRISE Corporation                            -- Lord Thomas Robert Dewar
http://www.borland.com/delphi                                  (1864-1930)

Re:SQL-Typecasting to boolean ?


Mon, 26 Apr 1999 15:13:04 +0200 in comp.lang.pascal.delphi.databases,
Ralf Steinhaeusser <r...@sbox.tu-graz.ac.at>

Quote
> I'd like to create a table like

> SELECT Name, Amount, TooHeavy
> FROM xyz
> where Amount>0

> And TooHeavy shold be a boolean value
> that is true for Amount>100,
> else false

> It doen't work with
> SELECT Name, Amount , Amount>100 TooHeavy
> Delphi doesn't like the ">" in the Select-Statement.

try this

select Name,Amount,'T' as TooHeavy from xyz
where Amount>100
union
select Name,Amount,'F' as TooHeavy from xyz
where Amount>0 and Amount<=100

Other Threads