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

Question

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
INTO 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?

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

321: Cannot group by aggregate column.

A cheese WITH NO LOG is like a donut with no jelly. – AJ. Jun 04 '11 at 01:12 — AJ., Jun 04 '11 at 01:12

RET · Answer 1 · 2011-06-05T13:05:09.340

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.

Thanks for the info, but this solution takes me to a new error: `321: Cannot group by aggregate column.` — CheeseConQueso, Jun 04 '11 at 17:47
@Cheese: I think that's because you have `SUM(c.hrs)` inside the `CASE` — ypercubeᵀᴹ, Jun 04 '11 at 23:33
I must be honest I didn't look closely at the CASE statement yesterday. The rules suggest an aggregation only under some circumstances. That's highly irregular. I've updated my answer accordingly. — RET, Jun 05 '11 at 12:56

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

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

edited May 23 '17 at 10:28

Community

1
1

answered Jun 07 '11 at 04:30

Frank R.

2,206
4
29
69

score 0 · Answer 3 · answered Jun 04 '11 at 22:51

0

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):

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.
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.

answered Jun 04 '11 at 22:51

Andriy M

71,352
17
87
142

i thought the same about grouping the prog, grd, stat, etc... but that didn't work for me. with regards to the 'else' scenarios, i only have them in there for debugging - they should never (in theory) obtain that value – CheeseConQueso Jun 05 '11 at 11:01
also, there are 3 cases in the actual problem i am having - i only wrote one out for demonstration purposes – CheeseConQueso Jun 05 '11 at 11:02
@CheeseConQueso: I thought just as well that those columns were not meant to be grouped by, but the issue is, you are referencing them incorrectly. If they aren't supposed to be included in GROUP BY, then *you can only mention them within aggregate functions*. What do the other cases look like? I mean, what is calculated in the THEN parts? Other SUMs or something else? – Andriy M Jun 05 '11 at 11:16
there are 3 cases and they all look the same except for the `WHEN` clause... all of them have `hrs` in the `THEN` clause – CheeseConQueso Jun 13 '11 at 12:23

score 0 · Accepted Answer · answered Jun 09 '11 at 02:08

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;

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

4 Answers4