All Forums Database
kishlay_TD 14 posts Joined 04/14
11 Jul 2014
ROW_NUMBER() Problem in SUb Query
Tags:
kishlay_TD 14 posts Joined 04/14
11 Jul 2014
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

kishlay_TD 14 posts Joined 04/14
11 Jul 2014
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:
 

dnoeth 4628 posts Joined 11/04
11 Jul 2014

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

 

Dieter

kishlay_TD 14 posts Joined 04/14
11 Jul 2014
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
 
 
 

dnoeth 4628 posts Joined 11/04
11 Jul 2014

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)

 

Dieter

You must sign in to leave a comment.