Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: Grouping by Timestamp_iso error

    Dear all,

    I creating a stored pro at DB2 for crystal report.
    I need to use TIMESTAMP_ISO at tbl2 to format the date.
    ----------------------------------------------------------------


    CR : BEGIN
    DECLARE C_RST CURSOR WITH RETURN FOR

    SELECT
    tbl1. RO_NO ,
    tbl1. CHKIN_TIME ,
    tbl1. BIBRCD ,
    tbl1. STATUS ,
    MIN(tbl1 . ON_TIME) AS MIN_ONTIME ,
    MAX(tbl1 . OFF_TIME) AS MAX_OFFTIME

    FROM tbl1
    GROUP BY tbl1. RO_NO,
    tbl1. CHKIN_TIME ,
    tbl1 . BIBRCD ,
    tbl1 . STATUS
    HAVING tbl1. BIBRCD = BRDID AND
    tbl1. STATUS = '10' AND
    DATE ( tbl1 . CHKIN_TIME ) = EDATE

    UNION ALL

    SELECT
    tbl2 . AORONO ,
    TIMESTAMP_ISO ( SUBSTR ( CHAR ( tbl2. AOCKDT ) , 1 , 8 ) || RIGHT ( '0' || TRIM ( CHAR ( tbl2. AOCKT ) ) , 6 ) ) CHKIN_TIME ,
    tbl2. AOBRCD ,
    tbl2 . AOSTAT ,
    MIN(tbl2 . ON_TIME) AS MIN_ONTIME ,
    MAX(tbl2 . OFF_TIME) AS MAX_OFFTIME

    FROM tbl2
    GROUP BY tbl2. AORONO,
    TIMESTAMP_ISO ( SUBSTR ( CHAR ( tbl2. AOCKDT ) , 1 , 8 ) || RIGHT ( '0' || TRIM ( CHAR ( tbl2. AOCKT ) ) , 6 ) ),
    tbl2. AOBRCD , tbl2. AOSTAT
    HAVING tbl2. AOBRCD = BRDID AND
    tbl2. AOSTAT = '10' AND
    tbl2 . AOCKDT = DECIMAL ( REPLACE ( CHAR ( EDATE ) , '-' , '' ) );

    OPEN C_RST ;
    END CR


    ---------------------------------------------------------------

    But there is error prompt

    Column AOCKDT or expression in HAVING clause not valid.

    Message ID: SQL0119

    Cause . . . . . : One of the following has occurred: -- Column AOCKDT specified in a HAVING clause is not within an aggregate function and is not in the GROUP BY clause. -- An expression specified in a HAVING clause is not within an aggregate function and is not in the GROUP BY clause.

    Recovery . . . : Remove the column or expression from the HAVING clause or add the column or expression to the GROUP BY clause. Try the request again.

    ---------------------------------------------------------------------

    Experts here have any idea? Thanks.

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    On what platform and db2 version does the error occur? The HAVING CLAUSE looks a bit wierd, should'nt those predicates be part of the WHERE clause?
    Last edited by lelle12; 09-13-12 at 02:58.
    --
    Lennart

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    where : executed before group by
    having : executed after 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

Posting Permissions

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