How to avoid using QUALIFY OVER PARTITION BY

Database
Enthusiast

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
11 REPLIES
Enthusiast

Re: How to avoid using QUALIFY OVER PARTITION BY

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.

Enthusiast

Re: How to avoid using QUALIFY OVER PARTITION BY

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
Supporter

Re: How to avoid using QUALIFY OVER PARTITION BY

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?

 

 


Enthusiast

Re: How to avoid using QUALIFY OVER PARTITION BY

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

Senior Apprentice

Re: How to avoid using QUALIFY OVER PARTITION BY

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

Re: How to avoid using QUALIFY OVER PARTITION BY

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

Enthusiast

Re: How to avoid using QUALIFY OVER PARTITION BY

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.

Enthusiast

Re: How to avoid using QUALIFY OVER PARTITION BY

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

Enthusiast

Re: How to avoid using QUALIFY OVER PARTITION BY

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