Re: IF Statement
A newcomer asked for the Informix equivalent of MySQL's IF(condition, value, 0) inside a SELECT. Replies pointed to NVL() for substituting a value when a column is NULL, plus DECODE() and CASE expressions for general conditional logic. A side debate arose over which version introduced these: one poster recalled they only appeared in 7.31, but checking the Product Lifecycle dates and the February 1998 Guide to SQL: Syntax showed NVL, DECODE and CASE were already present in 7.30. The rest of the thread is banter.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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 > > TIA, > Nicky. > > If you want to use a value when column is null, then you can use the NVL function: SELECT NVL(column, value) FROM table WHERE ... fx if column is character SELECT NVL(column, 'unknown') FROM table WHERE ... or if column is integer SELECT NVL(column, 0) FROM table WHERE ...
Claus Samuelsen <csa@eye-bee-em.com> wrote in message news:<41270877$0$203$14726298@news.sunsite.dk>... > 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 > > > > TIA, > > Nicky. > > > > > > If you want to use a value when column is null, then you can use the NVL > function: > > SELECT NVL(column, value) FROM table WHERE ... > > fx if column is character > > SELECT NVL(column, 'unknown') FROM table WHERE ... > > or if column is integer > > SELECT NVL(column, 0) FROM table WHERE ... My feelings exactly until Mark Stock said that "NVL" and "CASE" and ... weren't included in 7.3 but were added in 7.31. So you have to roll your own in 7.3 which I outlined in my overly long and rambling answer which probably no one read. Oh Well I tried.
Curtis Crowson wrote: > Claus Samuelsen wrote: > > 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 > > > > > > TIA, > > > Nicky. > > > > > > > > > > If you want to use a value when column is null, then you can use the NVL > > function: > > > > SELECT NVL(column, value) FROM table WHERE ... > > > > fx if column is character > > > > SELECT NVL(column, 'unknown') FROM table WHERE ... > > > > or if column is integer > > > > SELECT NVL(column, 0) FROM table WHERE ... > > > My feelings exactly until Mark Stock said that "NVL" and "CASE" and > ... weren't included in 7.3 but were added in 7.31. So you have to > roll your own in 7.3 which I outlined in my overly long and rambling > answer which probably no one read. > > Oh Well I tried. First - I apologize for the bizarre formatting of this. I seem to have no control over the appearance, but it *should* go out in plain text format. See both http://www-306.ibm.com/software/data/informix/support/plc/ and http://publib.boulder.ibm.com/epubs/pdf/4367.pdf These are links to the Product Lifecycle and to the Guide to SQL: Syntax (Feb 1998). According to the Product Lifecycle, the general availability date of 7.30 was 1 March 98 and the general availability date of 7.31 was 31 May 02; the Syntax manual is dated February 1998. It appears that the NVL and DECODE functions, plus the CASE expression were introduced with 7.30. In Mark's defense, he did add "IIRC" to his comment. I don't know if this helps or hurts. As to your original response, I wouldn't call it rambling, and I did read it - so there is at least one who has done so. And one last thing. A week or so ago I suggest that Captain Pedantic needed to get out more. After taking the time to go back through old documentation to compare dates, maybe I should follow my own advice? -- June Hunt
June C. Hunt wrote: > And one last thing. A week or so ago I suggest that Captain Pedantic > needed > to get out more. After taking the time to go back through old > documentation to compare dates, maybe I should follow my own advice? "Suggested", Shirley, Sheila? -- Strewth! Stick a sock in it, Sheila!
My Name Is Bruce and I'm A Sock Puppet wrote: > June C. Hunt wrote: > > > And one last thing. A week or so ago I suggest that Captain Pedantic > > needed > > to get out more. After taking the time to go back through old > > documentation to compare dates, maybe I should follow my own advice? > > "Suggested", Shirley, Sheila? I know! I know! And don't call me Shirley. :-) -- June Hunt
June C. Hunt wrote: > My Name Is Bruce and I'm A Sock Puppet wrote: >> June C. Hunt wrote: >> >> > And one last thing. A week or so ago I suggest that Captain Pedantic >> > needed >> > to get out more. After taking the time to go back through old >> > documentation to compare dates, maybe I should follow my own advice? >> >> "Suggested", Shirley, Sheila? > > I know! I know! > > And don't call me Shirley. :-) No worries, Sheila! -- Strewth! Stick a sock in it, Sheila!