Re: IF Statement

"Nicolas Mainczyk" <nmainczyk@hotmail.com> wrote in message news:<cfv1dc$7ho$1@ngspool-d02.news.aol.com>... > 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 > > > > finderr output: -201 A syntax error has occurred. This general error message indicates mistakes in the form of an SQL statement. Look for missing or extra punctuation (such as missing or extra commas, omission of parentheses around a subquery, and so on), keywords misspelled (such as VALEUS for VALUES), keywords misused (such as SET in an INSERT statement or INTO in a subquery), keywords out of sequence (such as a condition of "value IS NOT" instead of "NOT value IS"), or a reserved word used as an identifier. Database servers that provide full NIST compliance do not reserve any words; queries that work with these database servers might fail and return error -201 when they are used with earlier versions of Informix database servers. The cause of this error might be an attempt to use round-robin syntax with CREATE INDEX or ALTER FRAGMENT INIT on an index. You cannot useround-robin indexes. -201 A syntax error has occurred. This general error message indicates mistakes in the form of an SQL statement. Look for missing or extra punctuation (such as missing or extra commas, omission of parentheses around a subquery, and so on), keywords misspelled (such as VALEUS for VALUES), keywords misused (such as SET in an INSERT statement or INTO in a subquery), keywords out of sequence (such as a condition of "value IS NOT" instead of "NOT value IS"), or a reserved word used as an identifier. Database servers that provide full NIST compliance do not reserve any words; queries that work with these database servers might fail and return error -201 when they are used with earlier versions of Informix database servers. The cause of this error might be an attempt to use round-robin syntax with CREATE INDEX or ALTER FRAGMENT INIT on an index. You cannot useround-robin indexes. I ran this sql from dbaccess: select case when tabid = 1 then 0 else tabid end case from systables ; it seems to work. I was worried about "end case" "case" becomes the return field name here. It should read just "end" or "end returnfieldname" It might be an error somewhere else in the sql. Since you didn't post all of the sql I couldn't evaluate this possibility, but certainly a table named "table" should fail.