Weird Behaviour - Can someone explain?!

chesl73 · #1 (**permalink**) 10-12-08, 23:53

Hi,

I have a stored proc which simplified looks like:

CREATE PROCEDURE MYPROC(MYVAR VARCHAR10))
....
BEGIN
DECLARE C1 CURSOR FOR
SELECT WO_DATE,
(CASE WHEN LCASE(MYVAR)='a' THEN TABLE.COL1
WHEN LCASE(MYVAR)='b' THEN TABLE.COL2 END) AS FIELD1
FROM TABLE
GROUP BY
WO_DATE,
CASE WHEN LCASE(MYVAR)='a' THEN TABLE.COL1
WHEN LCASE(MYVAR)='b' THEN TABLE.COL2 END
OPEN C1;
END @

When i compiled this I would get the error: SQL0119N: An expression starting with TABLE.COL1 specified in a SELECT, HAVING, ORDER BY is not specified in the GROUP BY....

However, the same CASE statement is in the SELECT and the GROUP BY as shown above so the error baffled me.

It took my hours of playing around but I finally managed to get it working. This is the weird bit, if I simply removed the 'LCASE' function it would work!
Can someone explain this to me as it makes no sense!
ie, the stored proc works fine but now looks like:

SELECT WO_DATE,
(CASE WHEN MYVAR='a' THEN TABLE.COL1
WHEN MYVAR='b' THEN TABLE.COL2 END) AS FIELD1
FROM TABLE
GROUP BY
WO_DATE,
CASE WHEN MYVAR='a' THEN TABLE.COL1
WHEN MYVAR='b' THEN TABLE.COL2 END

Peter.Vanroose · #2 (**permalink**) 10-13-08, 01:46

Quote:

Originally Posted by chesl73

DECLARE C1 CURSOR FOR
SELECT WO_DATE,
(CASE WHEN LCASE(MYVAR)='a' THEN TABLE.COL1
WHEN LCASE(MYVAR)='b' THEN TABLE.COL2 END) AS FIELD1
FROM TABLE
GROUP BY
WO_DATE,
CASE WHEN LCASE(MYVAR)='a' THEN TABLE.COL1
WHEN LCASE(MYVAR)='b' THEN TABLE.COL2 END

I'm assuming you also have an aggregate function (COUNT(*) or so) in the SELECT, otherwise you could just use SELECT DISTINCT of course.

What about one of these two ways of writing the query?
Has to additional advantage of avoiding "code duplication", hence has better maintainability:

Code:

SELECT wo_date, field1
FROM (SELECT wo_date,
             CASE WHEN LOWER(myvar)='a' THEN col1
                  WHEN LOWER(myvar)='b' THEN col2 END AS field1
      FROM   table) t
GROUP BY wo_date, field1

or the "modern" way:

Code:

WITH t(wo_date, field1) AS
(SELECT wo_date,
        CASE WHEN LOWER(myvar)='a' THEN col1
             WHEN LOWER(myvar)='b' THEN col2 END
 FROM   table)
SELECT wo_date, field1  FROM t
GROUP BY wo_date, field1

przytula_guy · #3 (**permalink**) 10-13-08, 01:46

if you specify select col1,col2.. group by col1
this is not possible if col2 is not an aggregate like sum or other function
it has to be repeated in the group by

Peter.Vanroose · #4 (**permalink**) 10-13-08, 01:53

That's not his problem, I believe.
It's rather the following:

Code:

SELECT complex_function(col1)
FROM   table
GROUP BY complex_function(col1)

Apparently, DB2 does not "see" that both complex functions are identical (even if they are literally!) so this must be rewritten the "old" (v7) way, with a nested table expression, before the time "group by expression" was supported, or better of course: use a common table expression (CTE).

chesl73 · #5 (**permalink**) 10-13-08, 05:16

Thanks Peter. The last reply explains the reason which is nice to know.
I'm new to DB2 so I found my answer to remove the lcase function odd.

chesl73 · #6 (**permalink**) 10-13-08, 19:36

I've had a look at your alternate query suggestions and I have a question. There's something I can't figure out, excuse my sql skills!

The original query does have a count(id) in it. So, if I wanted to translate the original query to the first example with the nested table, I can't see how it would work as follows:

SELECT t.wo_date, t.field1, count(id) as volume
FROM (SELECT wo_date,
CASE WHEN LOWER(myvar)='a' THEN col1
WHEN LOWER(myvar)='b' THEN col2 END AS field1
FROM table) t, table t2
WHERE t.wo_date = t2.wo_date AND t.field1 = ?????
GROUP BY t.wo_date, t.field1

If I add the count, I'll need to join the two tables together but how can I when the field1 only exists in the nested table? If I don't do the WHERE clause then I get duplicates.

How would I do this?

Peter.Vanroose · #7 (**permalink**) 10-14-08, 02:02

Quote:

Originally Posted by chesl73

The original query does have a count(id) in it.
If I add the count, I'll need to join the two tables together but how can I when the field1 only exists in the nested table?

I don't fully see what you want to do.
Just do as if the nested expression t is a view. Add columns to that view if necessary.
If you want to join table t with table t2 on wo_date *and* on a similar col1/col2 construct, do a similar thing for t2:

Code:

SELECT t.wo_date, t.field1, count(*) as volume
FROM (SELECT wo_date,
             CASE WHEN LOWER(myvar)='a' THEN col1
                  WHEN LOWER(myvar)='b' THEN col2 END AS field1
      FROM   table1) t,
     (SELECT wo_date,
             CASE WHEN LOWER(myvar)='a' THEN col1
                  WHEN LOWER(myvar)='b' THEN col2 END AS field1
      FROM   table2) t2
WHERE t.wo_date = t2.wo_date AND t.field1 = t2.field1
GROUP BY t.wo_date, t.field1

chesl73 · #8 (**permalink**) 10-14-08, 02:28

Hi,

Sorry Peter, maybe it isn't clear from my last post.

From my original post, I had an issue with the following sql:
SELECT count(*) as volume, wo_date,
CASE WHEN LOWER(myvar)='a' THEN col1
WHEN LOWER(myvar)='b' THEN col2 END AS field1
FROM table
GROUP BY wo_date,
CASE WHEN LOWER(myvar)='a' THEN col1
WHEN LOWER(myvar)='b' THEN col2 END

This does not work because of the LOWER function not being recognised as the same function in the SELECT (as you explained earlier).

So, you suggested I use a nested table (or a CTE).

But I cannot see how the above would be converted into a nested table because if you add the count(*) into the nested table then you'll have to add the CASE statement into a GROUP BY which will give me the same issue I had originally. Does that make sense?
Maybe if you could please convert above into a nested table expression with the count(*) it will become clear for me.

Thanks a lot for your help, apologies for the confusion.

Peter.Vanroose · #9 (**permalink**) 10-14-08, 05:39

Quote:

Originally Posted by chesl73

SELECT count(*) as volume, wo_date,
CASE WHEN LOWER(myvar)='a' THEN col1
WHEN LOWER(myvar)='b' THEN col2 END AS field1
FROM table
GROUP BY wo_date,
CASE WHEN LOWER(myvar)='a' THEN col1
WHEN LOWER(myvar)='b' THEN col2 END

This is functionally equivalent to:

Code:

WITH t(wo_date,field1) AS
(SELECT wo_date,
        CASE WHEN LOWER(myvar)='a' THEN col1
             WHEN LOWER(myvar)='b' THEN col2 END
 FROM   table)
SELECT count(*) as volume, wo_date, field1
FROM   t
GROUP BY wo_date, field1

chesl73 · #10 (**permalink**) 10-14-08, 06:43

Looking at your example it all seems so simple!
Thanks a lot for your time Peter.

stolze · #11 (**permalink**) 10-14-08, 07:02

Using the same function in a GROUP BY as in the SELECT list will definitively not work if the function is non-deterministic. The reason is that the grouping may be done on different values than what is produced in the SELECT list.

I am not sure if LOWER counts as deterministic or not (LCASE does).

Internet Services

Web Development

Digital Marketing

Consumer Tech