Please help with update conversion from oracle to Teradata

Database
Enthusiast

Please help with update conversion from oracle to Teradata

Hi ,

This is Oracle query .

update dbname.sharp_app_usage_rpt_details A

set display_format = Coalesce ((select C.propvalue from dbname.sharp_app_usage_rpt_details B, dbname.sharp_properties 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 = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ), display_format)

where request_id = 56546

Converted TD query

update table_a A FROM (select C.propvalue from

table_b B, table_c C ,table_a A

where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type

and C.systemname = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ) AS D(D1)

SET

display_format = D1

Coalesce (D1 , display_format )

where request_id = 56546

I am getting error as illegal usage of alias name

Thanks
6 REPLIES
Enthusiast

Re: Please help with update conversion from oracle to Teradata

Try:

update table_a A FROM (select C.propvalue from

table_b B, table_c C ,table_a A

where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type

and C.systemname = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ) AS D(D1)

SET

display_format = Coalesce (D1 , display_format )
----------------------------------------------

where request_id = 56546
;

Enthusiast

Re: Please help with update conversion from oracle to Teradata

I got the query compiled without any errors but when I update I get the folowing error :

: [Teradata Database] [TeraJDBC 13.00.00.07] [Error 7547] [SQLState HY000] Target row updated by multiple source rows.

any help on this Please.
Enthusiast

Re: Please help with update conversion from oracle to Teradata

Apologies - you need to take the key details from your subquery and join them back to the main table.
Try:

update table_a FROM
(select A.Request_Id,
A.Report_Name,
A.Report_Type,
C.propvalue (D1)
from table_b B, table_c C ,table_a A
where B.request_ID = A.request_ID
and B.report_Name = A.report_name
and B.report_type = A.report_type
and C.systemname = 'dfdf'
and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt'
and C.PROPVALUE in ('dfd', 'dfda')
and A.request_id = 56546 ) AS D

SET
display_format = Coalesce (D.D1 , Table_A.display_format )
where Table_A.request_id = D.Request_Id
and Table_A.Report_Name = D.Report_Name
And Table_A.Report_Type = D.Report_Type
;
Enthusiast

Re: Please help with update conversion from oracle to Teradata

thanks
Enthusiast

Re: Please help with update conversion from oracle to Teradata

I am getting the following error now .Can anybody help on this pelase

Message : [Teradata Database] [TeraJDBC 13.00.00.07] [Error 7547] [SQLState HY000] Target row updated by multiple source rows.

Thanks.
Enthusiast

Re: Please help with update conversion from oracle to Teradata

Hi,
The issue you are seing is that 2 or more rows are appearing from your result set to update one target row. Effectively means that data row duplicates exist in your tables. Try this:

update table_a FROM
(select A.Request_Id,
A.Report_Name,
A.Report_Type,
C.propvalue (D1)
from table_b B, table_c C ,table_a A
where B.request_ID = A.request_ID
and B.report_Name = A.report_name
and B.report_type = A.report_type
and C.systemname = 'dfdf'
and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt'
and C.PROPVALUE in ('dfd', 'dfda')
and A.request_id = 56546
) AS D

SET
display_format = Coalesce (D.D1 , Table_A.display_format )
where Table_A.request_id = D.Request_Id
and Table_A.Report_Name = D.Report_Name
And Table_A.Report_Type = D.Report_Type
;
The section I have marked in BOLD above should be run seperately and checked whether multiple rows are being returned. If so, then simply GROUP BY to reduce this if possible, otherwise solve the problem with appropriate steps. Then your UPDATE will work fine.