SQL-Typecasting to boolean ? 
Author Message
 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.



Wed, 18 Jun 1902 08:00:00 GMT  
 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 ->

http://www.upscene.demon.nl

Ralf Steinhaeusser heeft geschreven in bericht

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.



Wed, 18 Jun 1902 08:00:00 GMT  
 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              


Accept that some days you're the pigeon,
some days you're the statue.



Wed, 18 Jun 1902 08:00:00 GMT  
 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...



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL-Typecasting to boolean ?
On Mon, 26 Apr 1999 15:13:04 +0200, Ralf Steinhaeusser

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 ?

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)



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL-Typecasting to boolean ?

[...]

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)



Wed, 18 Jun 1902 08:00:00 GMT  
 SQL-Typecasting to boolean ?
Mon, 26 Apr 1999 15:13:04 +0200 in comp.lang.Pascal.delphi.databases,

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



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Boolean in SQL with Paradox DB

2. boolean in SQL

3. BDE, dBASE, SQL, and Logical fields (boolean)

4. Typecast SET to INTEGER ?

5. Invalid Class Typecast

6. value typecasts

7. A question about typecasting

8. typecasting preferences

9. Typecasting in OOP

10. Interesting TypeCasting Problem

11. Delphi newbie: Integer to extended typecast

12. boolean question

 

 
Powered by phpBB® Forum Software