Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    3

    Question Unanswered: DB2 sqlcode: -119 sqlstate: 42803

    sqlcode: -119

    sqlstate: 42803
    Last edited by @swathi; 05-10-13 at 08:31.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so, what is the problem ?
    sum needs 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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The difficulty(or troublesome) might be which columns of which tables should be included in GROUP BY clause.

    I formatted your query by my way, to understand construction/structure of the query.
    Code:
    SELECT SUM(AVL.MEETING_MIN)
         , SRS.*
         , RS.NAME
     FROM  SCRUM_MASTER.RES_AVAIL_HRS          AVL
         , SCRUM_MASTER.SCRUM_PROJECT_RESOURCE SRS
         , SCRUM_MASTER.RESOURCE               RS
         , SCRUM_MASTER.SCRUM_SPRINT           SPT
     WHERE AVL.SPRINT_ID = 'S207'
       AND AVL.AVAIL_DATE BETWEEN '2013-05-01'
                              AND '2013-05-03'
       AND AVL.RESOURCE_ID = RS .RESOURCE_ID
       AND AVL.RESOURCE_ID = SRS.RESOURCE_ID
       AND AVL.SPRINT_ID   = SPT.SPRINT_ID
       AND SRS.PROJECT_ID  = SPT.PROJECT_ID
    /*
     GROUP BY
     ??? , SRS.*
     ??? , RS.NAME
    */
    ;
    Then, I sometimes draw a diagram like the following to understand complex joins.
    It's might be not rational, rather be intuitive.
    Code:
    SUM(AVL.MEETING_MIN)                  RS.NAME
     A                                     A
     |                                     |
    (output)                              (output)
     |                                     |
    AVL____ -------[RESOURCE_ID]---------- RS
     | | A
     | | |
     | | +----- AVL.AVAIL_DATE BETWEEN '2013-05-01'
     | |                           AND '2013-05-03'
     | |
     | +------------------------+
     |                          |
    [RESOURCE_ID]              [SPRINT_ID] <----- AVL.SPRINT_ID = 'S207'
     |                          |
     |                          |
    SRS -----[PROJECT_ID]----- SPT
     |
    (output)
     |
     V
    SRS.*
    Note: Bold: for join(s). Italic: predicates to select rows in a table.


    After looking into the diagram for a while,
    I guessed that RS and SRS could be added after SUMed AVL.MEETING_MIN.


    Although, I'm not sure my guess might be right( or not),
    if based on those my guess, one possible query might be...

    Example 1:
    Code:
    SELECT sum_meeting_min
         , SRS.*
         , RS.NAME
     FROM  (SELECT SPRINT_ID
                 , RESOURCE_ID
                 , SUM(MEETING_MIN) AS sum_meeting_min
             FROM  SCRUM_MASTER.RES_AVAIL_HRS  AVL
             WHERE SPRINT_ID  = 'S207'
               AND AVAIL_DATE BETWEEN '2013-05-01'
                                  AND '2013-05-03'
             GROUP BY
                   RESOURCE_ID
                 , SPRINT_ID
           ) AS AVL
     INNER JOIN
           SCRUM_MASTER.SCRUM_SPRINT           SPT
      ON   SPT.SPRINT_ID   = AVL.SPRINT_ID
     INNER JOIN
           SCRUM_MASTER.SCRUM_PROJECT_RESOURCE SRS
      ON   SRS.RESOURCE_ID = AVL.RESOURCE_ID
       AND SRS.PROJECT_ID  = SPT.PROJECT_ID
     INNER JOIN
           SCRUM_MASTER.RESOURCE               RS
      ON   RS .RESOURCE_ID = AVL.RESOURCE_ID
    ;

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down

    Quote Originally Posted by @swathi View Post
    ------------------------------ Commands Entered ------------------------------
    SELECT SUM(AVL.MEETING_MIN), SRS.*,RS.NAME FROM SCRUM_MASTER.RES_AVAIL_HRS AVL,SCRUM_MASTER.SCRUM_PROJECT_RESOURCE SRS, SCRUM_MASTER.RESOURCE RS,SCRUM_MASTER.SCRUM_SPRINT SPT WHERE AVL.SPRINT_ID='S207' AND AVL.AVAIL_DATE BETWEEN '2013-05-01' AND '2013-05-03' AND AVL.RESOURCE_ID=RS.RESOURCE_ID AND AVL.RESOURCE_ID=SRS.RESOURCE_ID AND AVL.SPRINT_ID=SPT.SPRINT_ID AND SRS.PROJECT_ID=SPT.PROJECT_ID;
    ------------------------------------------------------------------------------
    SELECT SUM(AVL.MEETING_MIN), SRS.*,RS.NAME FROM SCRUM_MASTER.RES_AVAIL_HRS AVL,SCRUM_MASTER.SCRUM_PROJECT_RESOURCE SRS, SCRUM_MASTER.RESOURCE RS,SCRUM_MASTER.SCRUM_SPRINT SPT WHERE AVL.SPRINT_ID='S207' AND AVL.AVAIL_DATE BETWEEN '2013-05-01' AND '2013-05-03' AND AVL.RESOURCE_ID=RS.RESOURCE_ID AND AVL.RESOURCE_ID=SRS.RESOURCE_ID AND AVL.SPRINT_ID=SPT.SPRINT_ID AND SRS.PROJECT_ID=SPT.PROJECT_ID
    SQL0119N An expression starting with "PROJECT_ID" specified in a SELECT
    clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
    clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
    column function and no GROUP BY clause is specified. SQLSTATE=42803

    SQL0119N An expression starting with "PROJECT_ID " specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.

    Explanation:

    The SELECT statement has one of the following errors:
    * The identified expression and a column function are contained in the
    SELECT clause, HAVING clause, or ORDER BY clause but there is no
    GROUP BY clause
    * The identified expression is contained in the SELECT clause, HAVING
    CLAUSE, or ORDER BY clause but is not in the GROUP BY clause.

    The identified expression is an expression that starts with
    "<expression-start>". The expression may be a single column name.

    If the NODENUMBER or PARTITION functions are specified in the HAVING
    clause, then all partitioning key columns of the underlying table are
    considered to be in the HAVING clause.

    The statement cannot be processed.

    User response:

    Correct the statement by including the expression in the GROUP BY clause
    that are in the SELECT clause, HAVING clause, or ORDER BY clause or by
    removing the column function from the SELECT statement.

    sqlcode: -119

    sqlstate: 42803
    Code:
    SELECT SUM(AVL.MEETING_MIN), SRS.* ,RS.NAME
    Now you can understand your mistake:
    If you are using SRS.* with aggregation function you have to make GROUP BY using all columns of SCRUM_MASTER.SCRUM_PROJECT_RESOURCE SRS, also you have to include in GROUP BY RS.NAME.
    But I don't see any GROUP BY in your query.

    Lenny

Tags for this Thread

Posting Permissions

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