SQL - Informix error leads to another error - hallway of mirrors P.I.T.A

I'm using Informix version 11.50.FC6 via iSql

I'm giving the result of a CASE block a virtual name, att_hrs

SELECT      c.id,
            CASE    WHEN    (   c.prog = 'UNDG'
                                AND (c.grd  IN (SELECT DISTINCT grd FROM grd_table WHERE att_fctr = 1) OR (c.grd IN ('TR','W','LAB','WC')))
                                AND c.grd   NOT IN ('WM')
                                AND c.stat  NOT IN ('X','D'))
                    THEN    CAST(SUM(c.hrs) AS CHAR(4))
                    ELSE    'ELSED (att)'
            END     att_hrs 
FROM        cw_rec c
WHERE       c.id IN (SELECT DISTINCT id FROM stu_ids)
GROUP BY    c.id
I开发者_如何学运维NTO TEMP   cheese
WITH NO LOG;

This gives me an error:

294: The column (att_hrs) must be in the GROUP BY list.

Trying to fix the error as suggested:

SELECT      c.id,
            CASE    WHEN    (   c.prog = 'UNDG'
                                AND (c.grd  IN (SELECT DISTINCT grd FROM grd_table WHERE att_fctr = 1) OR (c.grd IN ('TR','W','LAB','WC')))
                                AND c.grd   NOT IN ('WM')
                                AND c.stat  NOT IN ('X','D'))
                    THEN    CAST(SUM(c.hrs) AS CHAR(4))
                    ELSE    'ELSED (att)'
            END     att_hrs 
FROM        cw_rec c
WHERE       c.id IN (SELECT DISTINCT id FROM stu_ids)
GROUP BY    c.id,
            att_hrs
INTO TEMP   cheese
WITH NO LOG;

Then gives me this error:

217: Column (att_hrs) not found in any table in the query (or SLV is undefined).

They kind of found att_hrs pretty easily when it wasn't in the GROUP BY party, but now all of a sudden, att_hrs is lost in the sauce...

Can you get around this?

What are the real errors &| solutions to what is going on here and what I need to do to fix it?

I tried RET's solution to GROUP BY 1,2,3... and got the following error:

321: Cannot group by aggregate column.

You can't use your labels for derived columns in your group by list. You need to list the grouping columns by their ordinal position under these circumstances.

Change your SQL to read GROUP BY 1, 2

Here's the relevant entry from the manual. Figure 269, specifically.

UPDATE: I didn't examine the CASE statement closely enough - I don't think what you're attempting is possible, because each row can differ in whether it's treated as a grouping or aggregate column.

Perhaps you need to try something like this:

SELECT      c.id,
            NVL(SUM(CASE
                WHEN ( c.prog = 'UNDG'
                       AND (c.grd  IN (SELECT DISTINCT grd FROM grd_table WHERE att_fctr = 1) OR (c.grd IN ('TR','W','LAB','WC')))
                       AND c.grd   NOT IN ('WM')
                       AND c.stat  NOT IN ('X','D'))
                THEN    c.hrs
                ELSE    NULL)::CHAR(4)
                END), 'ELSED (att)') AS att_hrs 
FROM        cw_rec c
WHERE       c.id IN (SELECT DISTINCT id FROM stu_ids)
GROUP BY    c.id
INTO TEMP   cheese
WITH NO LOG;

That's untested, but hopefully gives you the idea - there's always aggregation going on, and the result is cast into text.

Although the following link is a different kind of problem, it may shed some light into solving your problem with the GROUP BY clause. Error compiling ACE report with multiple SELECT INTO statements

The problem is with the columns c.prog, c.grd, c.stat, which appear in the SELECT list without being either included in GROUP BY or aggregated.

You seem to be calculating att_hrs based on the values of some columns, but the question is: what if the columns' values match the condition in the CASE expression in some rows and do not match in some others for the same id? Is that possible? If yes, what should become the value of att_hrs for that id?

There are two answers I can expect (I'm not pretending they are the only two possible answers, maybe I've missed something):

1. It should be 'ELSED (att)' if all the rows for that id do not match the CASE condition, otherwise it should be the sum for the rows that match the condition.

2. It should be 'ELSED (att)' if some (one or more) rows for that id do not match the CASE condition. The sum should only be calculated if all the rows match the condition.

So, to summarise, presently I've only answered the question about the actual problem with your query. I'll be happy to extend my answer with a solution after you elaborate on my questions.

I took everyone's advice that it isn't possible and rewrote it using UNION blocks.

Table & field name(s) may have varied, but here's the idea:

SELECT          s.id,
                SUM(c.hrs) hrs,
                'ATT' type
FROM            expected_contacts s,
                OUTER stu_crs c
WHERE           s.id = c.id
                AND c.prog = 'UNDG'
                AND c.grd   NOT IN ('WM')
                AND c.stat  NOT IN ('X','D')
                AND (c.grd  IN (SELECT DISTINCT grd 
                                FROM            grd_table 
                                WHERE           att_fctr = 1) 
                            OR (c.grd IN ('TR','W','LAB','WC')))
                AND c.crs_no <> 'PARM101'
GROUP BY        s.id
UNION
SELECT          s.id,
                SUM(c.hrs) hrs,
                'EARN' type
FROM            expected_contacts s,
                OUTER stu_crs c
WHERE           s.id = c.id
                AND c.prog = 'UNDG'
                AND (c.grd <= 'DI' or c.grd like 'S%' or c.grd IN ('P','LAB','TR'))
                AND c.stat NOT IN ('D','W','X')
GROUP BY        s.id
UNION
SELECT          s.id,
                SUM(c.hrs) hrs,
                'DEV' type
FROM            expected_contacts s,
                OUTER stu_crs c
WHERE           s.id = c.id
                AND c.prog = 'UNDG'
                AND (   c.crs_no    LIKE 'ENGL0%' 
                        OR c.crs_no LIKE 'MATH0%' 
                        OR c.crs_no LIKE 'ENGSL0%' 
                        OR c.crs_no LIKE 'ESOL0%')
                AND c.stat IN ('C','R','W')
                AND c.grd <> 'IP'   
GROUP BY        s.id
INTO TEMP       stu_acad
WITH NO LOG;