1

How to convert rownum in following query(oracle) to teradata equivalent:

and not exists(select 1
   from CSE, SPD 
   WHERE cse.id=spd.id
   AND ROWNUM = 1
   AND CSE.STATUSID IN(6,7,8,13)

thanks.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
user3438498
  • 211
  • 3
  • 11
  • 21

5 Answers5

2

There's no ROWNUM in Teradata, but you can usually rewrite it using ROW_NUMBER plus QUALIFY.

In your case there's no need for ROWNUM at all (at least logically, maybe Oracle prefers it to do a better plan), this is exactly the same:

and not exists(select *
   from CSE, SPD 
   WHERE cse.id=spd.id
   AND CSE.STATUSID IN(6,7,8,13)
dnoeth
  • 59,503
  • 4
  • 39
  • 56
2

Teradata specifically does not have any rownumber attached to the rows in a table as in Oracle.

But it has two analytical functions such as ROW_NUMBER() and RANK() which will give a number to your row and then you can accordingly pick you data.

You may use something like below:

QUALIFY ROW_NUMBER()(Partition by Id order by date)=1

Here, Partition by with a column or few columns can be used to group your data, like suppose some id column in your table and order by will sort the data for that id in your table according to the order by column you provide and =1 means it then chooses the row for that id which is given row number as 1.

Sudhanshu Agrawal
  • 412
  • 3
  • 9
2

Use somethin like below:

    row_number() over(partition by '' order by statusid asc) as rownum_1
qualify rownum_1 = 1

The above statement imitates the rownum functionality of oracle.

0

you can use row_number. keep in mind the columns you want to take into consideration while calculating the row number. You can use qualify for the same. other options are dense_rank, rank etc. In your case you can use rank in the sub query and put the condition rank = 1. if you want the syntax do let me know.

Aritra Bhattacharya
  • 720
  • 1
  • 7
  • 18
  • the syntax wud be something like this : select A.colA,AcolB from (sel colA,colB,qualify rank() over ( partition by colA,colB order by ColC ) as rank from table A group by colA,colB) A where A.rank=1 group by A.colA,b.colB ; use this sql in your inner query that you have given. but the relevant coly\umn names in place of the cols mentioned. if you are having problems do let me know. – Aritra Bhattacharya Apr 21 '15 at 18:24
0

As you using this tweak for efficiency, I suppose, QUALIFY ROW_NUMBER() and other window functions will not suite you during their heavy use of CPU.

You can simply remove this part, Teradata should be fine.

Alex
  • 731
  • 1
  • 6
  • 21