RowID issue with teradata

Database
Enthusiast

RowID issue with teradata

Hi,

Can any one help me toconvert below query  into teradata.

update operations.entity_group set status = 'I'

where rowid in (

select rowid from operations.entity_group a

where (ENTITY_KEY, OR_UPDATE_DATE) = (

select ENTITY_KEY, min(OR_UPDATE_DATE) from operations.entity_group b

where b.ENTITY_KEY = a.ENTITY_KEY

and b.status = 'A'

group by b.ENTITY_KEY

having count(*) > 1

)

)
6 REPLIES
Enthusiast

Re: RowID issue with teradata

From which DBMS you want this query to be migrated to Teradata?

We will need more information like what error you are getting, and on which particular clause you are getting the error and needs a TD replacement etc to suggest a solution.

Enthusiast

Re: RowID issue with teradata

Hi,

The above query is migrating from ORACLE to Teradata.

When above query is run, its showing error ROWID doesnot exist.

Enthusiast

Re: RowID issue with teradata

In Teradata, there is ROW_NUMBER function which you can use. Following is the syntax for ROW_NUMBER

ROW_NUMBER() OVER(PARTITION BY <<col_list>> ORDER BY <<col_list>>

Becase you are selecting the rowid from the same tables, so you can generate the unique row_ids using row_number by defining the PARTITION BY & ORDER BY columns list in ROW_NUMBER, and should get you close where you want to be.

Enthusiast

Re: RowID issue with teradata

Hi,

I know the Row_NUMBER(), but I am not able toupdatethe table. Need some help in

I have written the query but its not working. Please help

update TEMP FROM operations.entity_group

SET TEMP.status = 'I'

WHERE ASP.SEQ IN(

SELECT ASP.SEQ from ASP WHERE ASP.SEQ in (

select row_number() over(partition by a.ENTITY_KEY order by a.ENTITY_KEY ) AS seq from operations.entity_group a

where (a.ENTITY_KEY) = (

select ENTITY_KEY from operations.entity_group b

where b.ENTITY_KEY = a.ENTITY_KEY

and b.status = 'A'

group by b.ENTITY_KEY

having count(*) > 1

) AS ASP

)

Junior Contributor

Re: RowID issue with teradata

IMHO you don't need ROWID at all, this query seems to be just a overly complex way to write (assuming the combination ENTITY_KEY, OR_UPDATE_DATE is unique):

UPDATE operations.entity_group SET status = 'I'
WHERE (ENTITY_KEY, OR_UPDATE_DATE) IN
(
SELECT ENTITY_KEY, MIN(OR_UPDATE_DATE)
FROM operations.entity_group b
WHERE b.status = 'A'
GROUP BY b.ENTITY_KEY
HAVING COUNT(*) > 1
)

Of course this is untested...


Enthusiast

Re: RowID issue with teradata

Hi doneth,

If the combination is not unique then what shouldbe the query.

kindly help.