oracle update statement

experts... I have this update statement.

update
(select REP.a,REP.SCOR_NUM_A,REP.SCOR_NUM_B,
REP.SCOR_NUM_C,REP.TYPE_CODE,REP.RQST_TMS,
REP.SBMT_USER_ID,
 CDMAI.MGR_OID, cdmai.SCOR_NUM_A,
 cdmai.SCOR_NUM_C,
 cdmai.SCOR_NUM_C,
 CASE WHEN
 GREATEST (NVL (RQST_TMS_A, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (
      RQST_TMS_B, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (RQST_TMS_C,
      TO_DATE
      ('01/01/1900', 'MM/DD/YYYY'))) = to_date('1/1/1900','MM-DD-YYYY')
      THEN NULL
      ELSE
      GREATEST (NVL (RQST_TMS_A, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (
      RQST_TMS_B, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (RQST_TMS_C,
      TO_DATE
      ('01/01/1900', 'MM/DD/YYYY')))
      END as greatest_date,
 cdmai.SBMT_USER_ID,
 CDM.TYPE_CODE
from c_d_m_a_i cdmai,T_C_C_M_R_D rep,
c_d_m cdm
where  REP.APLCN_OID = CDMAI.APLCN_OID
and  CDMAI.D_M_OID = CDM.D_M_OID
and CDM.ACTVTY_TMS >= sysdate - 60)
set rep.SCOR_NUM_A = cdmai.SCOR_NUM_A,
rep.SCOR_NUM_B = cdmai.SCOR_NUM_B,
rep.SCOR_NUM_C = cdmai.SCOR_NUM_C,
rep.RQST_TMS=  greatest_date,
rep.SBMT_USER_ID= cdmai.SBMT_USER_ID,
rep.TYPE_CODE= cdm.TYPE_CODE;


It gives me an error saying that cdm.type_code is an invalid identifier. Is the update statement wrongly written? I am not that familiar with oracle update statements. Please Help!!!!
chickannaAsked:
Who is Participating?
 
sdstuberCommented:
when you put your select in ()  the entities lose their identity

the enclosed query becomes its own entity

you must give it a name and each column a distinct name then you can reference them in the SET clause
0
 
chickannaAuthor Commented:
thanks for the reply. This is what I want to do in Oracle (I am new to Oracle)


update T_C_C_M_R_D
set SCOR_NUM_A = CDMAI.SCOR_NUM_A ,
SCOR_NUM_B = CDMAI.SCOR_NUM_B,
SCOR_NUM_C  = CDMAI.SCOR_NUM_C ,
RQST_TMS =  CASE WHEN
 GREATEST (NVL (RQST_TMS_A, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (
      RQST_TMS_B, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (RQST_TMS_C,
      TO_DATE
      ('01/01/1900', 'MM/DD/YYYY'))) = to_date('1/1/1900','MM-DD-YYYY')
      THEN NULL
      ELSE
      GREATEST (NVL (RQST_TMS_A, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (
      RQST_TMS_B, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (RQST_TMS_C,
      TO_DATE
      ('01/01/1900', 'MM/DD/YYYY')))
      END,
USER_ID = CDMAI.USER_ID,
TYPE_CODE = CDM.TYPE_CODE
from c_d_m_a_i cdmai, T_C_C_M_R_D rep,
c_d_m cdm
where  REP.APLCN_OID = CDMAI.APLCN_OID
and  CDMAI.APLCN_OID = CDM.APLCN_OID
and CDM.ACTVTY_TMS >= sysdate - 60)

obivously this will not work in Oracle. How do I re-formulate this query to run in Oracle 9i.
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
try this,

UPDATE T_C_C_M_R_D rep
SET
(SCOR_NUM_A, SCOR_NUM_B, SCOR_NUM_C, RQST_TMS, USER_ID, TYPE_CODE )
= (SELECT SCOR_NUM_A, SCOR_NUM_B, SCOR_NUM_C,
    CASE WHEN
      GREATEST (NVL (RQST_TMS_A, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (
      RQST_TMS_B, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (RQST_TMS_C,
      TO_DATE
      ('01/01/1900', 'MM/DD/YYYY'))) = to_date('1/1/1900','MM-DD-YYYY')
      THEN NULL
      ELSE
      GREATEST (NVL (RQST_TMS_A, TO_DATE ('01/01/1900', 'MM/DD/YYYY')),NVL (
      RQST_TMS_B, TO_DATE ('01/01/1900', 'MM/DD/YYYY')), NVL (RQST_TMS_C,
      TO_DATE
      ('01/01/1900', 'MM/DD/YYYY')))
    END, USER_ID, TYPE_CODE
   FROM c_d_m_a_i cdmai, c_d_m cdm
   where  REP.APLCN_OID = CDMAI.APLCN_OID
   and  CDMAI.APLCN_OID = CDM.APLCN_OID
   and CDM.ACTVTY_TMS >= sysdate - 60)

I have not tested above query, but it should look like this  
   
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.