All Forums Database
prawen 4 posts Joined 09/12
20 Sep 2012
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.

dnoeth 4628 posts Joined 11/04
20 Sep 2012

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

Dieter

prawen 4 posts Joined 09/12
20 Sep 2012

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

dnoeth 4628 posts Joined 11/04
20 Sep 2012

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

Dieter

prawen 4 posts Joined 09/12
20 Sep 2012

Thanks very much dieter...:) :)

raj786 23 posts Joined 04/14
04 Mar 2016

how to get dense rank in teradata?
is there any dense_rank() function available in teradata

dnoeth 4628 posts Joined 11/04
05 Mar 2016

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

Dieter

frozenshine 7 posts Joined 03/15
05 Aug 2016

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 
 

ank

You must sign in to leave a comment.