General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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?

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

3 weeks ago

3 weeks ago

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)