vote up 1 vote down
star
1

Here is the oracle query .Can anybody give me teradata query .

update table_a A 
    	set display_format =  nvl((select prop.propvalue from table_a B,
    							      table_c C 
    							 where B.request_ID = A.request_ID 
    							 and   B.report_Name = A.report_name 
    							 and B.report_type = A.report_type 
    							 and C.systemname = 'system' 
    							 and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' 
    							 and C.PROPVALUE in ('yes','No') ), display_format)
    	where request_id = 123

Regards,

flag

2 Answers

vote up 1 vote down

Here is a template that you can use to modify your query to fit within:

UPDATE TGT
FROM TABLE_A TGT
   , (SELECT /*columns necessary for join between SRC and TGT */
           , /*any additional columns necessary*/
      FROM <SOURCE TABLE>
      /* Any additional joins necessary*/
     ) SRC
SET <COL_A> = SRC.<COL_X> 
WHERE TGT.<join_columns> = SRC.<join_columns>
;

Hope this helps.

link|flag
vote up 0 vote down

I try do the same thing, as below: 

update A 
 FROM (select BU_ID, ORD_NBR from TMP_WORK_DB.CYCLE_TM_ORD_TIE_APJ_STEP1 ) B,
  TMP_WORK_DB.ORD_CYCLE_TMP_APJ A
  set A.ORD_NBR =B.ORD_NBR
  where  A.BU_ID = B.BU_ID

However, I get the below error: 

ERROR [42S22] [NCR][ODBC Teradata Driver][Teradata Database]
 Column/Parameter 'TMP_WORK_DB.A.A'
 does not exist. 0 records returned.
 UPDATE Command Failed.

Is there something that I didn't qualify properly? :S

link|flag
You cannot qualify the left hand part of the SET. Change set A.ORD_NBR to simply SET ORD_NBR without the A. – Carlos A. Ibarra Jan 20 at 4:16

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.