Re: Case structure et.al.

This will compile:

select 'N' as TransCode,
decode(shiploc.state, 'AB', 'CAN', 'USA') as country,
shiploc.store
from shiploc
into temp orders

You could of course prepare these statements from strings.

--
Regards,
Doug Lawry
www.douglawry.dyndns.org


----- Original Message -----
From: <[email protected]>
To: <[email protected]>
Cc: <[email protected]>
Sent: Friday, February 23, 2007 11:20 PM
Subject: [fourjs-users] Case structure et.al.


I have been having to jump between sql server and informix databases
and have gotten used to Case and Join syntax on the sql server side.  I
see the commands are valid under Informix 9.x that we have, but I can't
get any program to compile with Join or Case.
e.g.
select i.*, d.unitprice, d.prodname
     from invoice i join detail d on d.invoicenum = i.invoicenum

Left and right outer joins don't work either.

In the case of case:

select 'N' as TransCode,
case shiploc.state
when ('AB' ) then 'CAN'
else 'USA'
end as country,
shiploc.store
from shiploc
into temp orders

This doesn't work either.
I tried enclosing that all in SQL ENDSQL and it didn't compile either.

Fri 02/23/2007-15:18:57.48 O:\GWN\Source>fglwrt -a info
Development license 'FABXXXXXX' with 1 runtime user(s).
License server usage enabled.
Extension(s):
          - Open Database Interface
Warning! This is a temporary license, installation number is 'YYYYYYY'.
This temporary installation will expire in 12 day(s).

Got any ideas?