Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2008
    Posts
    19

    Unanswered: Weird Behaviour - Can someone explain?!

    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

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Oct 2008
    Posts
    19
    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.

  6. #6
    Join Date
    Oct 2008
    Posts
    19
    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?

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Oct 2008
    Posts
    19
    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.

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Oct 2008
    Posts
    19
    Looking at your example it all seems so simple!
    Thanks a lot for your time Peter.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •