Informix equivalents for Decode, NVL, CASE-WHEN-THEN

Sorry if the question sound amateur, but I am looking for Informix implementation for conditional statements/functions/procedures that can do what I used Decode, and NVL in Oracle. I have limited Informix experience, and moderate experience with Oracle and Sybase. What I need to is construct a single SELECT statement that will return a single row and basically means a whole bunch of conditional SELECTs. I can post/send more detailed info upon request. Thanks in advance. Jay Lee Zungwon Engineering and Systems, Inc. Seoul, Korea email: XXyjleeXX@XXns.zungwon.co.krXX (remove X's)

informix ids has decode, case, nvl all that. -- jarrodr [at] province [dot] com blacklungs wrote in message <7eturu$cf9$1@news.kren.nm.kr>... >Sorry if the question sound amateur, but I am looking for Informix >implementation for conditional statements/functions/procedures that can do >what I used Decode, and NVL in Oracle. > >I have limited Informix experience, and moderate experience with Oracle and >Sybase. > >What I need to is construct a single SELECT statement that will return a >single row and basically means a whole bunch of conditional SELECTs. > >I can post/send more detailed info upon request. > >Thanks in advance. > >Jay Lee >Zungwon Engineering and Systems, Inc. >Seoul, Korea > >email: XXyjleeXX@XXns.zungwon.co.krXX (remove X's) > > > > > >

I thought it did, too. But I'm using Informix Universal Dataserver 9.14 and just throwing queries at it from a Win95 client app, yet CASE statements generate '-201 syntax errors' and NVL and Decode generate '-674 routine cannot be resolved errors'. Does the version of Informix we have not support the functions, or is it just not installed, or am I doing something wrong? Jay. Jarrod Roberson ''('') <7eu5i8$1vn$1@usenet49.supernews.com> ''''''''' 'ۼ'''''''''... >informix ids has decode, case, nvl all that. > >-- >jarrodr [at] province [dot] com >blacklungs wrote in message <7eturu$cf9$1@news.kren.nm.kr>... >>Sorry if the question sound amateur, but I am looking for Informix >>implementation for conditional statements/functions/procedures that can do >>what I used Decode, and NVL in Oracle. >> >>I have limited Informix experience, and moderate experience with Oracle and >>Sybase. >> >>What I need to is construct a single SELECT statement that will return a >>single row and basically means a whole bunch of conditional SELECTs. >> >>I can post/send more detailed info upon request. >> >>Thanks in advance. >> >>Jay Lee >>Zungwon Engineering and Systems, Inc. >>Seoul, Korea >> >>email: XXyjleeXX@XXns.zungwon.co.krXX (remove X's) >> >> >> >> >> >> > >

blacklungs (please@refertothep.ost) wrote: : I thought it did, too. : But I'm using Informix Universal Dataserver 9.14 and just throwing queries : at it from a Win95 client app, yet CASE statements generate '-201 syntax : errors' and NVL and Decode generate '-674 routine cannot be resolved : errors'. : : Does the version of Informix we have not support the functions, or is it : just not installed, or am I doing something wrong? 9.14 lacks NVL and CASE. But it does have an extensible query language. 1. The CASE keyword was thought to be necessary because the SQL-92 language fundamentally couldn't handle extensions to the query language. But with 9.14, the DBMS can. For example, supposing you want to categorize people into regions based on the US state they live in. ( Sorry for the US - centric nature of the example, but its code I happen to have lying around.) There are two ways to do this: CASE or a UDR. a. CASE SELECT Print(M.Name) AS Name, CASE WHEN M.Address.State IN ('TX','CA','AZ','NV','UT') THEN 'South West' WHEN M.Address.State IN ('OR','WA','ID') THEN 'North West' WHEN M.Address.State IN ('CO','WY','NM','UT','MT') THEN 'Mountain' WHEN M.Address.State IN ('ND','SD','NE','KS','OK','LO') THEN 'Central' WHEN M.Address.State IN ('IL','IN','IO','MI','MN','MS','OH','WI') THEN 'Mid West' WHEN M.Address.State IN ('AL','AR','FL','GA','KY','LO','NC','SC','TN') THEN 'South East' WHEN M.Address.State IN ('CT','ME','MA','NH','RI','VT','DE') THEN 'New England' WHEN M.Address.State IN ('VA','WV','PA','MD','NY','NJ','DC') THEN 'North East' ELSE 'Non-US' END CASE FROM MovieClubMembers M; b. UDR CREATE FUNCTION Region ( Arg1 State_Enum ) RETURNING lvarchar DEFINE lvRetVal lvarchar; IF Arg1 IN ('TX','CA','AZ','NV','UT') THEN LET lvRetVal = 'South West'; ELIF Arg1 IN ('OR','WA','ID') THEN LET lvRetVal = 'North West'; ELIF Arg1 IN ('CO','WY','NM','UT','MT') THEN LET lvRetVal = 'Mountain'; ELIF Arg1 IN ('ND','SD','NE','KS','OK') THEN LET lvRetVal = 'Central'; ELIF Arg1 IN ('IL','IN','IO','MI','MN','MS','OH','WI') THEN LET lvRetVal = 'Mid West'; ELIF Arg1 IN ('AL','AR','FL','GA','KY','LO','NC','SC','TN') THEN LET lvRetVal = 'South East'; ELIF Arg1 IN ('CT','ME','MA','NH','RI','VT','DE') THEN LET lvRetVal = 'New England'; ELIF Arg1 IN ('VA','WV','PA','MD','NY','NJ','DC') THEN LET lvRetVal = 'North East'; ELSE LET lvRetVal = 'Non-US'; END IF; RETURN lvRetVal; END FUNCTION; -- GRANT EXECUTE ON FUNCTION Region ( State_Enum ) TO PUBLIC;-- -- Combined with the query: -- SELECT Print(M.Name) AS Name, Region(M.Address.State) AS Region FROM MovieClubMembers M; Now. the UDR approach has a lot of advantages. In the first place, it it more modular. You can re-use this Region() function in lots of queries, in WHERE clauses and so on. Second, it makes the task of modifying the application so much easier when rules like this are encoded in just one place. For example, if it were decided to move Delaware into the North East (and out of New England) then with CASE you would need to alter every query in the application that implemented this mapping, whereas with the UDR approach you change it once. 2. NVL (Null Value) As other posters have pointed out, the 9.2 engine has an NVL function built-in and it's pretty useful. But you can use the UDR stuff in the 9.14 engine in a similar way. To avoid an upgrade problem, this is what the NVL thingie looks like in 9.2 (beta copy). -- -- Housecleaning. -- DROP TABLE Foo;-- -- Create a table to store data -- CREATE TABLE Foo ( A INTEGER NOT NULL, B INTEGER );-- -- Populate it with 100 rows of data with a NULL in the B column -- INSERT INTO Foo SELECT N1.Num * 10 + N2.Num, NULL::INTEGER FROM TABLE(SET{0,1,2,3,4,5,6,7,8,9}::SET(INTEGER NOT NULL)) N1 ( Num ), TABLE(SET{0,1,2,3,4,5,6,7,8,9}::SET(INTEGER NOT NULL)) N2 ( Num );-- -- Populate it with another 100 rows, only the B column contains -- actual values. -- INSERT INTO Foo SELECT N1.Num * 20 + N2.Num, N2.Num * 10 + N1.Num FROM TABLE(SET{0,1,2,3,4,5,6,7,8,9}::SET(INTEGER NOT NULL)) N1 ( Num ), TABLE(SET{0,1,2,3,4,5,6,7,8,9}::SET(INTEGER NOT NULL)) N2 ( Num );-- -- SELECT NVL(B, -10) fROM Foo WHERE B IS NULL; -- -- BAsically, the NVL() function takes two arguments: the first is the -- column to check, and the second is a value to supply iff. the -- first argument is NULL. Note that the second argument need not -- be a contant value. For example; -- SELECT NVL(B,A) FROM Foo WHERE B IS NULL; -- -- i.e. (in 9.14). When you upgrade to 9.2, simply remove these CREATE -- FUNCTION statements from the schema scripts. -- CREATE FUNCTION NVL( Arg1 INTEGER, Arg2 INTEGER) RETURNING INTEGER IF ( Arg1 IS NULL ) THEN RETURN Arg2; END IF; RETURN Arg1; END FUNCTION; -- In general, most vendor's SQL contains a lot of idiosyncratic extensions which can better be solved through a more general extensibility mechanism. This is the motivation for the 9.X product line. Hope this helps! KR Pb