All Forums Database
Saarang84 16 posts Joined 08/14
08 Oct 2014
How to avoid using QUALIFY OVER PARTITION BY

I'm trying to rewrite a query in Teradata in which I'm trying to avoid using the QUALIFY statement for a report.

The query layout is as below :

 

SELECT A.field1, A.field2, A.field3, ... F.date_field, G.field1 
FROM TableA  A
LEFT JOIN TableB  B
ON A.key_field = B.key_field AND snap_dt = '?SNAP_DT'
LEFT JOIN TableB  C
ON A.key_field = C.key_field AND snap_dt = '?SNAP_DT'
LEFT JOIN TableB  D
ON A.key_field = D.key_field AND snap_dt = '?SNAP_DT'
LEFT JOIN TableB  E
ON A.key_field = E.key_field AND snap_dt = '?SNAP_DT'
LEFT JOIN TableB  F
ON A.key_field = F.key_field AND snap_dt = '?SNAP_DT'
LEFT JOIN TableB  G
ON A.key_field = G.key_field AND snap_dt = '?SNAP_DT'
WHERE <condition_1>
AND <condition_2>
AND <condition_3>
AND <condition_4>
QUALIFY OVER (PARTITION BY A.key_field ORDER BY F.date_field) = 1

 

 

Sarang

Raja_KT 1246 posts Joined 07/09
08 Oct 2014

You can think of group by   and having.
I see that  you do a left join on the same table many times on constant fields. I am not aware of your "WHERE" clauses. Just my thought that you may restructure the way you store/query the table.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Saarang84 16 posts Joined 08/14
09 Oct 2014

Apologies for posting my initial code snippet which is incorrect. I do not use the same table for multiple joins. 
The correct query structure is as below :

SELECT A.field1, A.field2, A.field3, ... F.date_field, G.field1 
FROM TableA  A  
LEFT JOIN TableB  B 
ON A.key_field = B.key_field AND B.Snap_dt = '?SNAP_DT'
LEFT JOIN TableC  C 
ON A.key_field = C.key_field AND C.snap_dt = '?SNAP_DT' 
LEFT JOIN TableD  D 
ON A.key_field = D.key_field AND D.snap_dt = '?SNAP_DT' 
LEFT JOIN TableE  E 
ON A.key_field = E.key_field AND E.snap_dt = '?SNAP_DT' 
LEFT JOIN TableF  F 
ON A.key_field = F.key_field AND F.snap_dt = '?SNAP_DT' 
LEFT JOIN TableG  G 
ON A.key_field = G.key_field AND G.snap_dt = '?SNAP_DT' 
WHERE <condition_1> 
AND <condition_2> 
AND <condition_3> 
AND <condition_4> 
QUALIFY OVER (PARTITION BY A.key_field ORDER BY F.date_field) = 1

 

Sarang

ulrich 816 posts Joined 09/09
09 Oct 2014

do you mean
 

QUALIFY row_number() OVER (PARTITION BY A.key_field ORDER BY F.date_field) = 1?

 

do you have max 1:1 relations or 1:N relations between A and B,C,D,E,F,G?

 

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Saarang84 16 posts Joined 08/14
09 Oct 2014

There is a 1:1 relation for all tables except the table F where there are multiple entries for a record from table A

Sarang

dnoeth 4628 posts Joined 11/04
09 Oct 2014

Hi Sarang,
depending on your actual data you might do the ROW_NUMBER in a Derived Table instead:

LEFT JOIN (SELECT * FROM TableF
           QUALIFY ROW_NUMBER() OVER (PARTITION BY key_field ORDER BY date_field) = 1)  F 
ON A.key_field = F.key_field AND F.snap_dt = '?SNAP_DT'

or

SELECT ...
FROM
 (
   SELECT ...FROM TableA
   LEFT JOIN TableF F
   ON A.key_field = F.key_field AND F.snap_dt = '?SNAP_DT'
   QUALIFY ROW_NUMBER() OVER (PARTITION BY A.key_field ORDER BY F.date_field) = 1
 ) A
LEFT JOIN TableB
...

 

Dieter

Saarang84 16 posts Joined 08/14
09 Oct 2014

This doesn't seem to work for me Dieter..

Sarang

ramjasmeenakshi 19 posts Joined 04/11
10 Oct 2014

Hi Dieter,
Can u pls ans below..its bit urgent..
Hi,
I have a table with PPI on date column Fill_DT defined as fill_dt BETWEEN '2001-01-01'(DATE) AND '2014-12-31'(DATE) EACH INTERVAL '1' MONTH
Now I want to know who all users accessed partitions of 2008 and below.
I am doing it with dbql object and log table join and then drilling each sql but that realy difficult as the sqls are more than 10000 is there any way around for this.

Meenakshi Swami

Saarang84 16 posts Joined 08/14
10 Oct 2014

Thanks a ton Dieter, I was able to work out a solution with your help ..

Sarang

divyagolla 22 posts Joined 02/14
12 Nov 2014

I have a person table with following data

 

Person_id  Address

10 Bangalore karnataka india

20 India karnataka Bangalore

30 Maharastra Mumbai

40 Asia bangalore

50 Bangalore

 

Column, Address is of variable length, Bangalore can come at any place those fields. I want to extract only bangalore from Address.

Can some one please help me how to extract only bangalore from address column

Raja_KT 1246 posts Joined 07/09
13 Nov 2014

You can use regexp_substr like example below:
regexp_substr(address,'bangalore',1,1,'i')
 
Next time, please issue a new topic, for ease of handling :) 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

CyrusArmanius 1 post Joined 07/15
08 Jul 2015

In order to avoid using QUALIFY OVER PARTITION BY try below line:
d QUALIFY OVER (PARTITION BY A.key_field ORDER BY F.date_field) = 1
 
 

You must sign in to leave a comment.