Re: Informix SQL ver.2.10 Report Question (HELP)
>From: charli@clark.net (Charlene C.)
>Subject: Informix SQL ver.2.10 Report Question (HELP)
>Date: 14 Sep 1994 03:40:27 GMT
>X-Informix-List-Id: <news.8662>
>
>I would like to write a report and order the information by the outcome
>of an if statement.
>
>example:
>
>There are 4 hearing date fields.
>In the body of the report program I would say:
>
>if ba_hear4 is not null then
>let u_hearing = ba_hear4
>else
>if ba_hear4 is null and ba_hear3 is not null then
>let u_hearing = ba_hear3
>else
>if ba_hear3 is null and ba_hear2 is not null then
>let u_hearing = ba_hear2
>else
>if ba_hear2 is null and ba_hear1 is not null then
>let u_hearing = ba_hear1
>
>NOW: I want to order the report by u_hearing
You aren't going to like it. I don't like it. But:
SELECT *, ba_hear4 u_hearing
FROM Wherever
WHERE Whatever
AND ba_hear4 IS NOT NULL
UNION
SELECT *, ba_hear3 u_hearing
FROM Wherever
WHERE Whatever
AND ba_hear4 IS NULL
AND ba_hear3 IS NOT NULL
UNION
SELECT *, ba_hear2 u_hearing
FROM Wherever
WHERE Whatever
AND ba_hear4 IS NULL
AND ba_hear3 IS NULL
AND ba_hear2 IS NOT NULL
UNION
SELECT *, ba_hear1 u_hearing
FROM Wherever
WHERE Whatever
AND ba_hear4 IS NULL
AND ba_hear3 IS NULL
AND ba_hear2 IS NULL
ORDER BY u_hearing;
You may end up having to use a temp table for the output to evade
problems with column naming in ORDER BY with UNION... I haven't checked
it out.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>