Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    32

    Unanswered: Db2 select query with group by clause thorws error?

    I want to get Distinct values of Serial_num and EMP_NAME but the below statement throws an error ?


    Code:
        SELECT SERIAL_NUM,EMP_NAME,NOTESID FROM DB2INST1.EMP_HC GROUP BY SERIAL_NUM,EMP_NAME,NOTESID HAVING   FUNC_VP ='Bob '  AND VICE_PRES ='Bob'  AND DIRECTOR ='Michael'  AND THIRD_LINE ='Linda '

    error

    DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=FUNC_VP, DRIVER=3.63.123


    I tried the below too but it would not work

    Code:
    SELECT DISTINCT SERIAL_NUM,EMP_NAME,NOTESID FROM DB2INST1.EMP_HC  WHERE FUNC_VP ='Bob '  AND VICE_PRES ='Bob'  AND DIRECTOR ='Michael'  AND THIRD_LINE ='Linda '

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by marshell08 View Post
    I want to get Distinct values of Serial_num and EMP_NAME but the below statement throws an error ?


    Code:
        SELECT SERIAL_NUM,EMP_NAME,NOTESID FROM DB2INST1.EMP_HC GROUP BY SERIAL_NUM,EMP_NAME,NOTESID HAVING   FUNC_VP ='Bob '  AND VICE_PRES ='Bob'  AND DIRECTOR ='Michael'  AND THIRD_LINE ='Linda '

    error

    DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=FUNC_VP, DRIVER=3.63.123


    I tried the below too but it would not work

    Code:
    SELECT DISTINCT SERIAL_NUM,EMP_NAME,NOTESID FROM DB2INST1.EMP_HC  WHERE FUNC_VP ='Bob '  AND VICE_PRES ='Bob'  AND DIRECTOR ='Michael'  AND THIRD_LINE ='Linda '

    Your first example (you might want to format your code in the future so that it becomes easier to read) is wrong because having is evaluated after group by. That is, there is no VICE_PRES column that you can use in the having clause. You can however put that in the where clause instead:

    Code:
    SELECT SERIAL_NUM,EMP_NAME,NOTESID
    FROM DB2INST1.EMP_HC
    WHERE FUNC_VP ='Bob '
       AND VICE_PRES ='Bob'
       AND DIRECTOR ='Michael'
       AND THIRD_LINE ='Linda '
    GROUP BY SERIAL_NUM,EMP_NAME,NOTESID
    What error message did you get from your second example?
    --
    Lennart

Posting Permissions

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