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,operatio n1,0),etc... FROM
table WHERE whereclause

TIA,
Nicky.

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,operatio n1,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:

> 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,operatio n1,0),etc... FROM
> table WHERE whereclause
>
> TIA,
> Nicky.
>
>

It seems like the more common "DECODE()" ou CASE statements.
Check the SQL syntax guide. Ther is a chapter called "segments" and then
go to expressions/functions or something like that... A find will be
more helpful than this tips

The SQL syntax guide is available in PDF format at:

http://www-306.ibm.com/software/data.../pubs/library/

Regards.

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,operatio n1,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,operatio n1,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,operatio n1,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,operatio n1,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

"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,operatio n1,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 use
round-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 use
round-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.

"Nicolas Mainczyk" <nmainczyk@hotmail.com> wrote in message news:<cftc1n$kc4$1@ngspool-d02.news.aol.com>...
> 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,operatio n1,0),etc... FROM
> table WHERE whereclause
>
> TIA,
> Nicky.

Another way of doing this ( Disclaimer: I AM NOT ADVOCATING THIS WAY.
nor do I like it in general ) is to use the union clause.

select
<operation1>
from
<table1>
where
(<condition1>) and <whereclause>
union
select
0
from
<table1>
where
not(<condition1>) and <whereclause>
;

Of course this way sucks bad if you have many fields to "decode". But
if your stuck doing it it can be done this way.

If you want to decode non-null fields you can create decode tables
with key, decoded key value pair.

such as
decode_pet_code table
code,value

100,"dog"
200,"cat"


Of course since "null" doesn't equal "null" you can't use a decode
table for those pesky nulls.

Wait hold everything am I missing something here can't you just write
your own limited if(<condition>,<operation1>,0) using informix's
stored procedures. You may need one for each data type but that
shouldn't be hard.


create function if_int( cond1 boolean, ret_true int, ret_false int )

returning int;

if ( cond1 ) then
return ret_true;
else
return ret_false;
end if

end function ;

select tabname, tabid, if_int( greaterthan(tabid,1), 5555555, tabid )
from systables;

You have to use the operator functions instead of "tabid > 1" because
you get a syntax error if you don't.

<Begin stealing from the manual>
Using Operator Functions in Place of Relational Operators
Each relational operator is bound to a particular operator function,
as shown
in the table below. The operator function accepts two values and
returns a
boolean value of true, false, or unknown.
Relational Operator Associated Operator Function
< lessthan()
<= lessthanorequal()
> greater than()
>= greaterthanorequal()
= equal()
<> notequal()
!= notequal()
<End stealing from the manual>

You'll then need to write a nullif(field, returnifnull,
returnifnotnull) procedure if you need it. If it takes a varchar you
should be able to pass everything into it.

Good luck.

PS
Of course it might be easier to upgrade to 7.31. I've never used 7.3
so I don't know if it had the "case" statement or not. Since someone
said that it wasn't added until 7.31, This might entirely be true.
Since you aren't sending your entire sql there is no way to know if
you didn't fat finger the sql somewhere else and the case clause if
fine in it.

PPS
Historical aside, I have been told that Informix added the decode
function for the company that I used to work for. And several other
Oracle compatibility features to help with the implementation of a
very large application that was written for Oracle that we deployed
using Informix. At least that is what our CIO told me at the time.
Think very large telecom company.