Does Informix support CASE statement inside a SELECT query

Question

I am using the SELECT query below, and it isn't working if I give CASE statements. Can anyone please advise me if Informix allows CASE, or if statements in a select query or if there is any other alternative to what I am trying below?

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

I have tried replacing '0' with 0 and null, no luck.

Solution

All supported versions of Informix support CASE in SELECT statements. However, it does not use END CASE because the SQL standard only requires END. The sample query also has a stray comma that could be a minimization problem.

SELECT a.post_date, a.count_date,
       CASE WHEN (a.tover > b.sun_num) THEN a.tover ELSE '0' END {CASE} AS t_over,
       CASE WHEN (a.tshort > b.sun_num) THEN a.tover ELSE '0' END {CASE} AS t_short   -- , -- this comma in the original query is an error
  FROM a JOIN b ON a.cust_ix = b.cust_ix

---

As discussed in a comment, the syntax above is correct — any residual problems are not demonstrable from the information available in the question.

Let's start with the basics.

• Which version of Informix are you using and on which platform?
• When you say "it won't work for me", in what way doesn't it work?
• What is the error number/message?
• If you create two minimal tables A and B with just the columns referenced in the example query (5 columns in A, 2 in B), and run exactly the answer I suggested, does that compile at all?

Here's a complete query sequence that does what I suggested in the last bullet point, and it worked (in a logged database — running Informix 11.70.FC6 on Mac OS X 10.10.4 via my SQLCMD program, but DB-Access would be OK too):

BEGIN WORK;
CREATE TEMP TABLE a
(
    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) THEN a.tover ELSE '0' END AS t_over,
       CASE WHEN (a.tshort > b.sun_num) THEN a.tover ELSE '0' END AS t_short
  FROM a JOIN b ON a.cust_ix = b.cust_ix
  ;
ROLLBACK;

I wonder if the a.tover in the second CASE should be t.short, but that's a semantic issue in the query, not a syntactic problem that can be resolved by an outsider like me.

I first created the three CHAR(3) columns as INTEGER columns, but I got error -800: Corresponding data types must be compatible in CASE expression or DECODE because of the quotes around the zeros in the query. So, I changed the columns to CHAR(3) instead. Alternatively, I could have changed '0' to 0 and kept with the INTEGER type.

So, the syntax shown is correct. You now need to apply it accurately to your larger query on more complex tables and fix any other residual problems. May I recommend a step-wise refinement approach. Comment out all the CASE expressions in your query, and get the remainder of the query syntactically correct. Then add the CASE expressions one (or a few) at a time, and debug each (set) as you go.