All Forums Database
kishlay_TD 14 posts Joined 04/14
09 Apr 2014
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
)
)

 

Qaisar Kiani 337 posts Joined 11/05
09 Apr 2014

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.

kishlay_TD 14 posts Joined 04/14
09 Apr 2014

Hi,
The above query is migrating from ORACLE to Teradata.
When above query is run, its showing error ROWID doesnot exist.

Qaisar Kiani 337 posts Joined 11/05
09 Apr 2014

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.

kishlay_TD 14 posts Joined 04/14
09 Apr 2014

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
)

dnoeth 4628 posts Joined 11/04
09 Apr 2014

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...

 

 
 

Dieter

kishlay_TD 14 posts Joined 04/14
10 Apr 2014

Hi doneth,
If the combination is not unique then what shouldbe the query.
kindly help.

You must sign in to leave a comment.