sql - Does Informix support CASE statement inside a SELECT query -

i using select query below, , isn't working if give case statements. can please advise me if informix allows case, or if statements in select query or if there other alternative trying below?

select a.post_date, a.count_date,     case          when (a.tover>b.sun_num) a.tover          else '0'      end case t_over,     case          when (a.tshort>b.sun_num) a.tover          else '0'      end case t_short, join b on a.cust_ix = b.cust_ix 

i have tried replacing '0' 0 , null, no luck.

all supported versions of informix support case in select statements. however, not use end case because sql standard requires end. sample query has stray comma minimization problem.

select a.post_date, a.count_date,        case when (a.tover > b.sun_num) a.tover else '0' end {case} t_over,        case when (a.tshort > b.sun_num) a.tover else '0' end {case} t_short   -- , -- comma in original query error   join b on a.cust_ix = b.cust_ix 

---

as discussed in comment, syntax above correct — residual problems not demonstrable information available in question.

let's start basics.

• which version of informix using , on platform?
• when "it won't work me", in way doesn't work?
• what error number/message?
• if create 2 minimal tables , b columns referenced in example query (5 columns in a, 2 in b), , run answer suggested, compile @ all?

here's complete query sequence suggested in last bullet point, , worked (in logged database — running informix 11.70.fc6 on mac os x 10.10.4 via sqlcmd program, db-access ok too):

begin work; create temp table (     post_date   date not null,     count_date  date not null,     tover       char(3) not null,     tshort      char(3) not null,     cust_ix     integer not null ); create temp table b (     cust_ix     integer not null,     sun_num     char(3) not null ); select a.post_date, a.count_date,        case when (a.tover  > b.sun_num) a.tover else '0' end t_over,        case when (a.tshort > b.sun_num) a.tover else '0' end t_short   join b on a.cust_ix = b.cust_ix   ; rollback; 

i wonder if a.tover in second case should t.short, that's semantic issue in query, not syntactic problem can resolved outsider me.

i first created 3 char(3) columns integer columns, got error -800: corresponding data types must compatible in case expression or decode because of quotes around zeros in query. so, changed columns char(3) instead. alternatively, have changed '0' 0 , kept integer type.

so, syntax shown correct. need apply accurately larger query on more complex tables , fix other residual problems. may recommend step-wise refinement approach. comment out case expressions in query, , remainder of query syntactically correct. add case expressions 1 (or few) @ time, , debug each (set) go.