Solved

oracle update statement

Posted on 2011-03-15
3
662 Views
Last Modified: 2013-12-19
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!!!!
0
Comment
Question by:chickanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 35140402
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
 

Author Comment

by:chickanna
ID: 35140820
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
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 200 total points
ID: 35141580
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question