Problem with update statement

smartcooldevil · #1 (**permalink**) 08-17-10, 23:54

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??

umayer · #2 (**permalink**) 08-18-10, 01:22

(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 ... )

tonkuma · #3 (**permalink**) 08-18-10, 11:25

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
       )
;

Internet Services

Web Development

Digital Marketing

Consumer Tech