Re: IF EXISTS SYNTAX

SVenkatapathy@ual.com wrote: > > Hi Gurus, > > I'm having a problem with the if exists condition. I'm trying to do a > selection into a variable if the record exists in the table. -- SNIP -- > CASE II: > > DEFINE a_col_b char(); > IF EXISTS (select col_b into a_col_b from test where col_a ='1') THEN > ..... > END PROCEDURE; > Informix does not like this and returns a syntax error. > > The workaround that I have now is > CASE III: > > if exists(select * from test where col_a = '1') then > select col_b into col_b where col_a = '1'; > else > ... > > end procedure; Sathya, First: The reason syntax-II fails to parse is that, historically, the INTO <variable> clause was never part of SQL; it was a special syntax for embedded languages like ESQL and 4GL. The subquery is probably being parsed this way and I suspect will probably stay that way because no allowance has been made in the syntax for using variables in a subquery. This is not Informix's doing; I suspect the ASNI commitee would have coniptions if someone implemented variable usage in the subquery and it would be sloppy. However, what you want to do is essestially: If it exists, read it into variables and set state to success. If not, set state to failure Try this: let state = 'failure' foreach select * into <variables..> from test where col_a = '1' let state = 'success' exit foreach end foreach Drop us a line if it does what you need. -- -- Jake (In persuit of undomesticated aquatic avians) +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+