Retrieve data by priority

General
Highlighted
Fan

Retrieve data by priority

Background: While iam not new to ANSI SQL I am almost completely new to Teradata SQL.  I have used T-SQL a lot.

 

Say I have a table, TableX.  And in it I have three columns: ID, Type and detail.

 

What I want to end up with is results for ID such that if there are results for type = '01' I get those and if the count is 0 I get results for type '02' and lastly if the count is still 0 I get any results there may be.  

 

I am more than a little vague as to what is available as far as conditional processing in Teradata SQL.  I think I could get there with a Volatile table (the one extension to ANSI I have seen) and a series of "in" and "not in" Insert Into statements. Is this my best course?


Accepted Solutions
Junior Contributor

Re: Retrieve data by priority

Teradata is based on Standard SQL, too, like T-SQL, maybe a bit closer :-)

 

Are those types actually '01', '02', '03', etc., i.e. increasing values?

SELECT *
FROM tableX
QUALIFY -- Teradata extension to filter the result of a Windowed Aggregate
   Min(type ) Over (PARTITION BY ID) = type              -- prefer '01' over '02'
OR Min(type ) Over (PARTITION BY ID) NOT IN ('01','02')  -- or return all rows

 

Otherwise you must apply more complex logic:

SELECT *
FROM tableX
QUALIFY
   type = Coalesce(Min(CASE WHEN type_ = '01' THEN type end) Over (PARTITION BY ID)  -- preferred type #1
                  ,Min(CASE WHEN type = '02' THEN type end) Over (PARTITION BY ID)   -- preferred type #2
                  ,type)                                                             -- all other types
1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: Retrieve data by priority

Teradata is based on Standard SQL, too, like T-SQL, maybe a bit closer :-)

 

Are those types actually '01', '02', '03', etc., i.e. increasing values?

SELECT *
FROM tableX
QUALIFY -- Teradata extension to filter the result of a Windowed Aggregate
   Min(type ) Over (PARTITION BY ID) = type              -- prefer '01' over '02'
OR Min(type ) Over (PARTITION BY ID) NOT IN ('01','02')  -- or return all rows

 

Otherwise you must apply more complex logic:

SELECT *
FROM tableX
QUALIFY
   type = Coalesce(Min(CASE WHEN type_ = '01' THEN type end) Over (PARTITION BY ID)  -- preferred type #1
                  ,Min(CASE WHEN type = '02' THEN type end) Over (PARTITION BY ID)   -- preferred type #2
                  ,type)                                                             -- all other types
Fan

Re: Retrieve data by priority

A whole new SQL clause in Teradata SQL, wrapping my mind around that.  A stack overflow question helped more than the documentaiton "Difference between Qualify and Having".

 

 

I will have to use the Coalesce method.  The Values do seem to be numbers stored as text but no an aggregate will not work as in my case they are not consecutive values nor necessarily at the end of the range like 01 implies.   

 

I'll post more once I have worked it out... or fail to work it out.

 

 

Fan

Re: Retrieve data by priority

I did get it to run and in my small test case it seems to be working as expected.

 

After a long think, I finally understand or deluded myself to think so Smiley Happy

 

 

Over operates on the logical criteria before it, likely wrapped in parenthesis, and executes it for / over the sub criteria.  By not including it, you get the value for that record.

 

Case When is used to test the values and Min is an arbitrary aggregate function to make Over work in that context (although the only alternative that comes to mind is Max).  

 

Coalesce is used as a wrapper to order the mess of tested values in the user defined order because the false conditions are returning null from Min.

 

This leads me to believe that Qualify does not need Over but if there were no over at all it might as well be a Having Clause?  - Maybe a Where depending on context .  Do I have it right?

 

Junior Contributor

Re: Retrieve data by priority

Over operates on the logical criteria before it, likely wrapped in parenthesis, and executes it for / over the sub criteria.  By not including it, you get the value for that record.

 

OVER works exactly the same as in SQL Server, calculating a Windowed Aggregate aka OLAP-function, the new part is the QUALIFY keyword.

 

 


Case When is used to test the values and Min is an arbitrary aggregate function to make Over work in that context (although the only alternative that comes to mind is Max).   


 

Yep, check if this value exists.

 


Coalesce is used as a wrapper to order the mess of tested values in the user defined order because the false conditions are returning null from Min. 


 Yep.

 


This leads me to believe that Qualify does not need Over but if there were no over at all it might as well be a Having Clause?  - Maybe a Where depending on context .  Do I have it right? 


A WHERE would be quite complicated, multiple (NOT) EXISTS.

Without OLAP-function QUALIFY is not allowed, similar to no HAVING without aggregate.

But with HAVING you don't get the detail rows,

That's the nice thing about Windowed Aggregates, they allow you to access detail and agggregated data at the same time.

 

When you copy the OVERs into the Select list you can see what they return.

If you want to emulate QUALIFY in Standard or T-SQL you must do it anyway:

SELECT -- all columns but min01, min02
FROM
 (
   SELECT t.*,
      Min(CASE WHEN type = '01' THEN type end) Over (PARTITION BY ID) AS min01,
      Min(CASE WHEN type = '02' THEN type end) Over (PARTITION BY ID) AS min02
   FROM tableX AS t
 ) AS dt
WHERE
   type = Coalesce(min01, min02, type)