Re: IF Statement

I saw CASE syntax but it seems that it is designed for procedural SQL with CREATE PROCEDURE blocks.I 'm using Informix v7.3. I doesn't seem to work in pure SQL statements or I missed something :). When I try ... SELECT CASE when field1=0 then 0 else field1 end case FROM table WHERE condition I got an error (-201) (State:S1000,Native Code: FFFFFF37) I'm using WinSQL to test my SQL statements then I put the query in a macro in Excel via openrecordest command. Nicky. "June C. Hunt" <june_c_hunt@hotmail.com> a 'crit dans le message news: 2oeu9cF9tsm0U1@uni-berlin.de... > Nicolas Mainczyk wrote: > > Hi, > > > > I'm newbie on Informix RDBMS. > > > > I would like to know the equivalent syntax for the following mysql query: > > > > SELECT if(condition1,operation1,0),if(condition2,operation1,0),etc... FROM > > table WHERE whereclause > > You might try either the CASE expression or the DECODE function. You didn't > say which version of Informix that you are using, so I might be leading you > on a bit of a goose chase with one or both of those. I'm not positive how > long these have been available, but for a quick answer that should work with > at least a reasonably recent version of IDS, an example of the CASE > expression follows: > > SELECT cust_num, cust_name, > CASE > WHEN order_total <= 100 > THEN "Small" > WHEN order_total > 100 and order_total <= 200 > THEN "Medium" > ELSE "Large" > END AS order_size > FROM custorders;> > Both the CASE expression and the DECODE function are documented in the IBM > Informix Guide to SQL: Syntax (Version 9.4) manual. This documentation, as > well as that for older versions, can be found online. See: > http://www.ibm.com/informix/pubs/library/lists.html > > -- > June Hunt > >

Nicolas Mainczyk wrote: > I saw CASE syntax but it seems that it is designed for procedural SQL with > CREATE PROCEDURE blocks.> I 'm using Informix v7.3. > I doesn't seem to work in pure SQL statements or I missed something :). > > When I try ... > > SELECT CASE > when field1=0 > then 0 > else field1 > end case > FROM table > WHERE condition> > I got an error (-201) (State:S1000,Native Code: FFFFFF37) > > I'm using WinSQL to test my SQL statements then I put the query in a macro > in Excel via openrecordest command. > > Nicky. > > "June C. Hunt" <june_c_hunt@hotmail.com> a 'crit dans le message news: > 2oeu9cF9tsm0U1@uni-berlin.de... > >>Nicolas Mainczyk wrote: >> >>>Hi, >>> >>>I'm newbie on Informix RDBMS. >>> >>>I would like to know the equivalent syntax for the following mysql > > query: > >>>SELECT if(condition1,operation1,0),if(condition2,operation1,0),etc... > > FROM > >>>table WHERE whereclause >> >>You might try either the CASE expression or the DECODE function. You > > didn't > >>say which version of Informix that you are using, so I might be leading > > you > >>on a bit of a goose chase with one or both of those. I'm not positive how >>long these have been available, but for a quick answer that should work > > with > >>at least a reasonably recent version of IDS, an example of the CASE >>expression follows: >> >>SELECT cust_num, cust_name, >> CASE >> WHEN order_total <= 100 >> THEN "Small" >> WHEN order_total > 100 and order_total <= 200 >> THEN "Medium" >> ELSE "Large" >> END AS order_size >>FROM custorders;>> >>Both the CASE expression and the DECODE function are documented in the IBM >>Informix Guide to SQL: Syntax (Version 9.4) manual. This documentation, > > as > >>well as that for older versions, can be found online. See: >>http://www.ibm.com/informix/pubs/library/lists.html >> >>-- >>June Hunt >> >> > > > You have "end case" instead of "end". Regards.

It is the same with END or END CASE "Fernando Nunes" <spam@domus.online.pt> a 'crit dans le message news: 2ogkm4Fa5d5pU1@uni-berlin.de... > Nicolas Mainczyk wrote: > > I saw CASE syntax but it seems that it is designed for procedural SQL with > > CREATE PROCEDURE blocks.> > I 'm using Informix v7.3. > > I doesn't seem to work in pure SQL statements or I missed something :). > > > > When I try ... > > > > SELECT CASE > > when field1=0 > > then 0 > > else field1 > > end case > > FROM table > > WHERE condition> > > > I got an error (-201) (State:S1000,Native Code: FFFFFF37) > > > > I'm using WinSQL to test my SQL statements then I put the query in a macro > > in Excel via openrecordest command. > > > > Nicky. > > > > "June C. Hunt" <june_c_hunt@hotmail.com> a 'crit dans le message news: > > 2oeu9cF9tsm0U1@uni-berlin.de... > > > >>Nicolas Mainczyk wrote: > >> > >>>Hi, > >>> > >>>I'm newbie on Informix RDBMS. > >>> > >>>I would like to know the equivalent syntax for the following mysql > > > > query: > > > >>>SELECT if(condition1,operation1,0),if(condition2,operation1,0),etc... > > > > FROM > > > >>>table WHERE whereclause > >> > >>You might try either the CASE expression or the DECODE function. You > > > > didn't > > > >>say which version of Informix that you are using, so I might be leading > > > > you > > > >>on a bit of a goose chase with one or both of those. I'm not positive how > >>long these have been available, but for a quick answer that should work > > > > with > > > >>at least a reasonably recent version of IDS, an example of the CASE > >>expression follows: > >> > >>SELECT cust_num, cust_name, > >> CASE > >> WHEN order_total <= 100 > >> THEN "Small" > >> WHEN order_total > 100 and order_total <= 200 > >> THEN "Medium" > >> ELSE "Large" > >> END AS order_size > >>FROM custorders;> >> > >>Both the CASE expression and the DECODE function are documented in the IBM > >>Informix Guide to SQL: Syntax (Version 9.4) manual. This documentation, > > > > as > > > >>well as that for older versions, can be found online. See: > >>http://www.ibm.com/informix/pubs/library/lists.html > >> > >>-- > >>June Hunt > >> > >> > > > > > > > > You have "end case" instead of "end". > Regards.

Nicolas Mainczyk wrote: > It is the same with END or END CASE > > "Fernando Nunes" <spam@domus.online.pt> a 'crit dans le message news: > 2ogkm4Fa5d5pU1@uni-berlin.de... > >>Nicolas Mainczyk wrote: >> >>>I saw CASE syntax but it seems that it is designed for procedural SQL > > with > >>>CREATE PROCEDURE blocks.>>>I 'm using Informix v7.3. >>>I doesn't seem to work in pure SQL statements or I missed something :). >>> >>>When I try ... >>> >>>SELECT CASE >>> when field1=0 >>> then 0 >>> else field1 >>> end case >>>FROM table >>>WHERE condition>>> >>>I got an error (-201) (State:S1000,Native Code: FFFFFF37) >>> >>>I'm using WinSQL to test my SQL statements then I put the query in a > > macro > >>>in Excel via openrecordest command. >>> >>>Nicky. >>> >>>"June C. Hunt" <june_c_hunt@hotmail.com> a 'crit dans le message news: >>>2oeu9cF9tsm0U1@uni-berlin.de... >>> >>> >>>>Nicolas Mainczyk wrote: >>>> >>>> >>>>>Hi, >>>>> >>>>>I'm newbie on Informix RDBMS. >>>>> >>>>>I would like to know the equivalent syntax for the following mysql >>> >>>query: >>> >>> >>>>>SELECT if(condition1,operation1,0),if(condition2,operation1,0),etc... >>> >>>FROM >>> >>> >>>>>table WHERE whereclause >>>> >>>>You might try either the CASE expression or the DECODE function. You >>> >>>didn't >>> >>> >>>>say which version of Informix that you are using, so I might be leading >>> >>>you >>> >>> >>>>on a bit of a goose chase with one or both of those. I'm not positive > > how > >>>>long these have been available, but for a quick answer that should work >>> >>>with >>> >>> >>>>at least a reasonably recent version of IDS, an example of the CASE >>>>expression follows: >>>> >>>>SELECT cust_num, cust_name, >>>> CASE >>>> WHEN order_total <= 100 >>>> THEN "Small" >>>> WHEN order_total > 100 and order_total <= 200 >>>> THEN "Medium" >>>> ELSE "Large" >>>> END AS order_size>>> >>>>FROM custorders; >>> >>>>Both the CASE expression and the DECODE function are documented in the > > IBM > >>>>Informix Guide to SQL: Syntax (Version 9.4) manual. This documentation, >>> >>>as >>> >>> >>>>well as that for older versions, can be found online. See: >>>>http://www.ibm.com/informix/pubs/library/lists.html >>>> >>>>-- >>>>June Hunt >>>> >>>> >>> >>> >>> >>You have "end case" instead of "end". >>Regards. > > > Test the statement in dbaccess... also note that the statement you're writing is equivalent to select field1 from table where condition Regards.

Nicolas Mainczyk wrote: > I saw CASE syntax but it seems that it is designed for procedural SQL with > CREATE PROCEDURE blocks. The SPL CASE statement (currently only available with XPS) and the CASE expression are different animals. > I 'm using Informix v7.3. > I doesn't seem to work in pure SQL statements or I missed something :). The Guide to SQL manual that seems to match your version would be this one: http://publib.boulder.ibm.com/epubs/pdf/4367.pdf See page 4-39 for information on the CASE expression and the valid syntax options to make sure you're covered. > When I try ... > > SELECT CASE > when field1=0 > then 0 > else field1 > end case > FROM table > WHERE condition> > I got an error (-201) (State:S1000,Native Code: FFFFFF37) > > I'm using WinSQL to test my SQL statements then I put the query in a macro > in Excel via openrecordest command. >[snipping rest...] I agree with Fernando's suggestion - try the SELECT statement through DB-Access (if possible), or otherwise take as much out of the equation as possible. We use Delphi here and have found the BDE will barf on some things that are otherwise syntactically correct. You may be running into something similar... At least you'll know where the real problem is occurring. -- June Hunt