How do i write qualify rank() over (partition.....question

Database
Fan

How do i write qualify rank() over (partition.....question

Hi all..

i have the query

qualify rank() over (partition.....question

 

in teradata form.  now i need to convert it to infomatica. can anyone help me provide the conversion for the "rank()" in informatica.???

 

thanks in advance..!!

 

this query is almost similar to previously asked but need answer in different form.

Tags (2)
7 REPLIES
Junior Contributor

Re: How do i write qualify rank() over (partition.....question

What do you mean by "convert it to informatica"?

Can't you simply use it as is against Teradata?

Rank is available in most DBMSes, the proprietary QUALIFY has to replaced by a Derived Table/Inline View and a Where condition:

select ...

from (select rank() ... as RNK from ...) dt

where RNK ...

Dieter

Fan

Re: How do i write qualify rank() over (partition.....question

Hi Dieter, thanks a lot for your response..

Actually i need a function for  qualify rank() over (partition.....question in INFORMIX, a function which is now RANK in TERADATA, i need a similar function what we can use in INFORMIX.

Actually i thought infomix and infomatica both are same and posted the query, apologies for that. can you please help me back, as to how to use rank function in INFORMIX.

Thanks in advance..

Junior Contributor

Re: How do i write qualify rank() over (partition.....question

Seems like Infomix desn't support OLAP functions, yet.

You can rewrite it using old style SQL, but performance will be horrible:

select
rank() over (partition by a order by b) as rnk
from tab
qualify rnk < 5

select
(select count(*) + 1 from tab as t2
where t2.a = t.a and t2.b < t.b) as rnk
from tab as t
where rnk < 5

Dieter

Fan

Re: How do i write qualify rank() over (partition.....question

Thanks very much dieter...:) :)

Enthusiast

Re: How do i write qualify rank() over (partition.....question

how to get dense rank in teradata?

is there any dense_rank() function available in teradata

Junior Contributor

Re: How do i write qualify rank() over (partition.....question

It's available since TD14.10, before you need to rewrite it:

Missing Functions: DENSE_RANK

Enthusiast

Re: How do i write qualify rank() over (partition.....question

Hi,

subscriber1  ||   pkg1  ||  23

Subscriber2 ||    pkg2  || 42 

.

.

.

SubscriberN || pkg1  || 50

-------------------------------------------------------------------------------------------

Now, I need to create SLABS of last col ( it there are 100 rows, i Need to create 10 partitions  and count subscribers who fall under this category).

Rather than using 10 group by queries , how i could do it by partition by function?

---------------------------------------------------------------------------------------------

Answer set should be :

Slab   ||   SubscribersCount

<20  ||  3 

>20  <= 40 ||  4