Re: IF EXISTS SYNTAX

On Thu, 10 Apr 1997 12:37:53 GMT, Jacob Salomon <jake@apparel.net> wrote: > >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 | >+-----------------------------------------------------------+ The EXIST functionality is exactly that. A TEST! Jake's answer of testing with the foreach (or even a sigleton select) is the ONLY way to test and retrieve at the same time. Chris cburton@gte.net