SQL-Typecasting to boolean ?
Author |
Message |
Ralf Steinhaeusse #1 / 7
|
 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 |
|
 |
Martijn Tonie #2 / 7
|
 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 |
|
 |
Glatzfam #3 / 7
|
 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 |
|
 |
Ralf Steinhaeusse #4 / 7
|
 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 |
|
 |
Steve Koters #5 / 7
|
 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 |
|
 |
Steve Koters #6 / 7
|
 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 |
|
 |
billy ch #7 / 7
|
 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 |
|
|
|