Re: IF Statement
"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,operation1,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.