Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    25

    Unanswered: Problem with update statement

    I am trying to update some fields based on already available fields but I am facing some problem.

    Please see the below code.

    UPDATE SESSION.total_billing_final tbf
    SET (cur_ST_rating , cur_ST_rating_date)
    =
    (select rs.RATING_SYMBOL_CD , DATE(MAX(r.RATING_DTM))
    FROM
    RATINGS.RATABLE_ENTITY re,
    RATINGS.RATING_OBJECT ro,
    RATINGS.RATING r,
    RATINGS.RATING_SYMBOL rs,

    RATINGS.RATING_CLASS rc,
    RATINGS.RATING_CLASS_RATING_ATTRIBUTE rcra,
    RATINGS.RATING_ATTRIBUTE ra,
    REFERN.RATING_ATTRIBUTE_TYPE rat
    where
    tbf.sale_ID = re.ENTITY_ID
    AND re.RATABLE_ENTITY_ID = ro.RATABLE_ENTITY_ID
    AND ro.RATING_OBJECT_ID = r.RATING_OBJECT_ID
    AND r.RATING_SYMBOL_ID = rs.RATING_SYMBOL_ID

    AND ro.RATING_CLASS_ID = rc.RATING_CLASS_ID
    AND rc.RATING_CLASS_ID = rcra.RATING_CLASS_ID
    AND rcra.RATING_ATTRIBUTE_ID = ra.RATING_ATTRIBUTE_ID
    AND ra.RATING_ATTRIBUTE_TYPE_ID = rat.RATING_ATTRIBUTE_TYPE_ID
    AND rat.RATING_ATTRIBUTE_TYPE_CD = 'DURATION'
    AND ra.RATING_ATTRIBUTE_CD LIKE '%ST%'
    GROUP BY tbf.sale_ID);



    I am getting below error.

    [IBM][CLI Driver][DB2/LINUXX8664] SQL0119N An expression starting with "RATING_SYMBOL_CD" 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. LINE NUMBER=1029. SQLSTATE=42803


    Can anyone please help me out modifying the code??

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    (select rs.RATING_SYMBOL_CD , DATE(MAX(r.RATING_DTM))
    ...
    GROUP BY tbf.sale_ID);


    each column, that appears in the SELECT-clause and doesn't have a grouping function must reappear in the GROUP BY clause.

    So
    GROUP BY rs.RATING_SYMBOL_CD

    may solve the error.

    ( Whether the result of the query is correct, is an other question ... )

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I'm not sure,
    you might want to update like in this statement.

    If my assumtions were wrong or you already got right result,
    please ignore my guess.

    Assumptions:
    (1): There is no direct relationship between rs and ro.
    (2): You want to get rs.RATING_SYMBOL_CD which was related to a row of r which has MAX(r.RATING_DTM).

    (The outer most WHERE clause might be not necessary.
    If it was required, you might want to use MERGE statement to remove duplicated codes.)
    Code:
    UPDATE SESSION.total_billing_final tbf
       SET ( cur_ST_rating
           , cur_ST_rating_date )
           = 
           (SELECT rs.RATING_SYMBOL_CD
                 , r. rating_date
              FROM
                   LATERAL
                   (select DATE(r.RATING_DTM) AS rating_date
                         , r.RATING_SYMBOL_ID
                      FROM
                           RATINGS.RATABLE_ENTITY  re
                         , RATINGS.RATING_OBJECT   ro
                         , RATINGS.RATING          r
                     where
                           tbf.sale_ID           = re.ENTITY_ID
                       AND re. RATABLE_ENTITY_ID = ro.RATABLE_ENTITY_ID
                       AND ro. RATING_OBJECT_ID  = r. RATING_OBJECT_ID
                       AND EXISTS
                           (SELECT 0
                              FROM
                                   RATINGS.RATING_CLASS                  rc
                                 , RATINGS.RATING_CLASS_RATING_ATTRIBUTE rcra
                                 , RATINGS.RATING_ATTRIBUTE              ra
                                 , REFERN.RATING_ATTRIBUTE_TYPE          rat
                             WHERE
                                   ro.  RATING_CLASS_ID          = rc.  RATING_CLASS_ID
                               AND rc.  RATING_CLASS_ID          = rcra.RATING_CLASS_ID
                               AND rcra.RATING_ATTRIBUTE_ID      = ra.  RATING_ATTRIBUTE_ID
                               AND ra.  RATING_ATTRIBUTE_TYPE_ID = rat. RATING_ATTRIBUTE_TYPE_ID
                               AND rat. RATING_ATTRIBUTE_TYPE_CD = 'DURATION'
                               AND ra.  RATING_ATTRIBUTE_CD      LIKE '%ST%'
                           )
                     ORDER BY
                           r.RATING_DTM DESC
                     FETCH FIRST 1 ROW ONLY
                   ) r
                 , RATINGS.RATING_SYMBOL  rs
             WHERE r. RATING_SYMBOL_ID  = rs.RATING_SYMBOL_ID
           )
     WHERE EXISTS
           (SELECT 0
              FROM
                   LATERAL
                   (select DATE(r.RATING_DTM) AS rating_date
                         , r.RATING_SYMBOL_ID
                      FROM
                           RATINGS.RATABLE_ENTITY  re
                         , RATINGS.RATING_OBJECT   ro
                         , RATINGS.RATING          r
                     where
                           tbf.sale_ID           = re.ENTITY_ID
                       AND re. RATABLE_ENTITY_ID = ro.RATABLE_ENTITY_ID
                       AND ro. RATING_OBJECT_ID  = r. RATING_OBJECT_ID
                       AND EXISTS
                           (SELECT 0
                              FROM
                                   RATINGS.RATING_CLASS                  rc
                                 , RATINGS.RATING_CLASS_RATING_ATTRIBUTE rcra
                                 , RATINGS.RATING_ATTRIBUTE              ra
                                 , REFERN.RATING_ATTRIBUTE_TYPE          rat
                             WHERE
                                   ro.  RATING_CLASS_ID          = rc.  RATING_CLASS_ID
                               AND rc.  RATING_CLASS_ID          = rcra.RATING_CLASS_ID
                               AND rcra.RATING_ATTRIBUTE_ID      = ra.  RATING_ATTRIBUTE_ID
                               AND ra.  RATING_ATTRIBUTE_TYPE_ID = rat. RATING_ATTRIBUTE_TYPE_ID
                               AND rat. RATING_ATTRIBUTE_TYPE_CD = 'DURATION'
                               AND ra.  RATING_ATTRIBUTE_CD      LIKE '%ST%'
                           )
                     ORDER BY
                           r.RATING_DTM DESC
                     FETCH FIRST 1 ROW ONLY
                   ) r
                 , RATINGS.RATING_SYMBOL  rs
             WHERE r. RATING_SYMBOL_ID  = rs.RATING_SYMBOL_ID
           )
    ;

Posting Permissions

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