Board index » delphi » SQL "IF" Reserved word

SQL "IF" Reserved word

Hi,

I've tryed to separate data from one field into to 2 fields, like MSAccess
allows to with the IF (IIF) reserved word... something like this

SELECT DISCTINCT
T.Item, IF(T.Type = 1,T.Qty,0) AS T.Type1, IF(T.Type = 2,T.Qty,0) AS T.Type2
FROM "Data.db" T;

I have some data like this
Item    Type    Qty
N1        1        100
N2        1        50
N1        2        20
N2        2        70

I would like the next result:
Item    Type1    Type2
N1        100          0
N2        50            0
N1        0            20
N2        0            70

This is for a computer system meant to get inventories from items, where the
field Type says if it was an entrance or a retrieval from the warehouse...
so I have to distinguish if I have to add or decrease from my inventory...

Is there some help out there about each reserved word in SQL with examples?

Thanx in advance

 

Re:SQL "IF" Reserved word


Jose,

Use UNION -

Select T.Qty, 0 from data T where t.Type = 1
UNION
Select 0, T.Qty from data T where t.Type = 2

You may have to CAST the 0 to the correct data type.

"Ing. Jos Manuel Gmez-Ferrer y Reguero" wrote:

Quote

> Hi,

> I've tryed to separate data from one field into to 2 fields, like MSAccess
> allows to with the IF (IIF) reserved word... something like this

> SELECT DISCTINCT
> T.Item, IF(T.Type = 1,T.Qty,0) AS T.Type1, IF(T.Type = 2,T.Qty,0) AS T.Type2
> FROM "Data.db" T;

> I have some data like this
> Item    Type    Qty
> N1        1        100
> N2        1        50
> N1        2        20
> N2        2        70

> I would like the next result:
> Item    Type1    Type2
> N1        100          0
> N2        50            0
> N1        0            20
> N2        0            70

> This is for a computer system meant to get inventories from items, where the
> field Type says if it was an entrance or a retrieval from the warehouse...
> so I have to distinguish if I have to add or decrease from my inventory...

> Is there some help out there about each reserved word in SQL with examples?

> Thanx in advance

--
Ken Bailey
Falmouth Software Works Ltd.

Re:SQL "IF" Reserved word


Wow! That was a fast 4 minute response !!!
That will solve the problem...

Thanx, but how do I use the IF reserved word? and what is it for?

Ken Bailey escribi en mensaje <3655DD11.37C76...@maine.rr.com>...

Quote
>Jose,

>Use UNION -

>Select T.Qty, 0 from data T where t.Type = 1
>UNION
>Select 0, T.Qty from data T where t.Type = 2

>You may have to CAST the 0 to the correct data type.

>"Ing. Jos Manuel Gmez-Ferrer y Reguero" wrote:

>> Hi,

>> I've tryed to separate data from one field into to 2 fields, like
MSAccess
>> allows to with the IF (IIF) reserved word... something like this

>> SELECT DISCTINCT
>> T.Item, IF(T.Type = 1,T.Qty,0) AS T.Type1, IF(T.Type = 2,T.Qty,0) AS
T.Type2
>> FROM "Data.db" T;

>> I have some data like this
>> Item    Type    Qty
>> N1        1        100
>> N2        1        50
>> N1        2        20
>> N2        2        70

>> I would like the next result:
>> Item    Type1    Type2
>> N1        100          0
>> N2        50            0
>> N1        0            20
>> N2        0            70

>> This is for a computer system meant to get inventories from items, where
the
>> field Type says if it was an entrance or a retrieval from the
warehouse...
>> so I have to distinguish if I have to add or decrease from my
inventory...

>> Is there some help out there about each reserved word in SQL with
examples?

>> Thanx in advance

>--
>Ken Bailey
>Falmouth Software Works Ltd.

Re:SQL "IF" Reserved word


On Fri, 20 Nov 1998 15:36:33 -0600, "Ing. Jos Manuel Gmez-Ferrer y

Quote
Reguero" <carl...@compuserve.com.mx> wrote:
>Thanx, but how do I use the IF reserved word? and what is it for?

If you are talking about local SQL, you cannot. Local SQL is a subset of
SQL-92. While SQL-92 includes IF..THEN and CASE constructs, these are not
part if that subset. And there is no equivalent <sigh> for them in local
SQL. The UNION join suggestion from Ken Bailey is about as close as you are
going to get with a single SQL statement. With multiple steps/SQL
statements or Delphi calculated fields...

Local SQL is documented in the online help file LOCALSQL.HLP, found in the
main BDE directory. In this help file is a topic on SQL reserved words,
listing the words you cannot use as token names because local SQL
recognizes them as keywords. There is also a topic listing SQL-92 languahe
elements not supported by local SQL.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                 "What is success in this world? I would say
Technical Publications         it consists of four simple things: to live
INPRISE Corporation            a lot, to love a lot, to laugh a lot, and
http://www.inprise.com/delphi  from it all, to learn a lot."
                                                     -- Richard J. Needham

Re:SQL "IF" Reserved word


Duh - Can't believe I had never thought of using UNION for this - I've been
using calculated fields.  I often have to extract data for a mailing list where
the user wants the billing address whether it be home or business.  Instead of
using a calculated field, I realize I could just UNION two queries, one getting
the home address fields WHERE BILL_TO = 'HOME' and one getting the billing
address where BILL_TO = 'WORK'.

I prefer to wait a bit longer on the query (with a UNION) and then not have any
delay moving through the records (as it is with calculated fields).

Thanks for mentioning it...

Other Threads