ROW_NUMBER() Problem in SUb Query

Database

ROW_NUMBER() Problem in SUb Query

 
Tags (1)
5 REPLIES

Re: ROW_NUMBER() Problem in SUb Query

select * from operations.tll_gnr_master tgm where tgm.rowid in (
select a.rowid from operations.tll_gnr_master a, operations.site b
where a.site_key=b.site_key
and b.brand_id='WD' and a.calendar_key between 20100101 and 20100131)
and tgm.calendar_key between 20100101 and 20100131;

Need the above query on Teradata. Kindly help me please.. ABove query runs successfuly on Oracle but not on Teradata

Re: ROW_NUMBER() Problem in SUb Query

select * from operations.tll_gnr_master tgm 
where ROW_NUMBER() OVER(PARTITION BY  a.site_key ORDER BY a.site_key) in (
select ROW_NUMBER() OVER(PARTITION BY  a.site_key ORDER BY a.site_key)
from operations.tll_gnr_master a, operations.site b
where a.site_key = b.site_key
and b.brand_id = 'WD' and a.calendar_key between 20100101 and 20100131)

Hi have written query on Teradata as:

N/A

Re: ROW_NUMBER() Problem in SUb Query

This query is overly complicated (in Oracle, too), should be exactly the same as 

select a.* 
from operations.tll_gnr_master a, operations.site b
where a.site_key=b.site_key
and b.brand_id='WD' and a.calendar_key between 20100101 and 20100131

Re: ROW_NUMBER() Problem in SUb Query

delete from operations.tll_gnr_master tgm where tgm.rowid in (
select a.rowid from operations.tll_gnr_master a, operations.site b where a.site_key=b.site_key
and b.brand_id='WD' and a.calendar_key between 20100101 and 20100131)
and tgm.calendar_key between 20100101 and 20100131

Hi,

Thanks for your help, but i need query as above for teardata.

please help

N/A

Re: ROW_NUMBER() Problem in SUb Query

If you need a DELETE why don't you talk about it instead of SELECT?

ROW_NUMBER is only supported for SELECTs, but you don't need it (or ROWID):

delete from operations.tll_gnr_master as a
where exists
(select * from operations.site b
where a.site_key = b.site_key
and b.brand_id = 'WD' and a.calendar_key between 20100101 and 20100131)