Passing values from outer query to a sub query

Analytics
New Member

Passing values from outer query to a sub query

Hi All,

I am new to TD as a database. Lots of experience in others.

I am helping out one of my team with a view at a teradata account.

What is desired is to count customers with certain types of transactions both this year and last year.

We have got it working for this month this year versus this month last year. But we are experimenting to see if we can get it to work for full year and to do that we need to pass in the month_id to the sub selects.

 

I have created primary query and then two sub queries with left joins.

The outline of the query is as follows.

Select customer# 

           month_id

           other data

          from customer table alias as A

 

left join 

( select customer # , month_id, other data from invoice lines alias as inv_line group by 1,2)  cy_inv_line

on a.customer# = cy_inv_line.customer#

and a.month_id = cy_inv_line.month_id

 

left join 

( select customer # , month_id, other data from invoice lines alias as inv_line group by 1,2)  ly_inv_line

on a.customer# = cy_inv_line.customer#

and a.month_id - 100 = cy_inv_line.month_id (we are using yyyymm as month_id)

 

What we want to try doing is selecting invoice lines for the prior year to determine conditions on the invoice lines and to count customers who have certain conditions on those invoice lines. But I can not figure out how to pass the outer variables in to the sub selects. I get a message "object a. does not exist".

I know this must be able to be done but with my lack of experience on TD I am having trouble finding an example of how to do this.

Any tips or pointers to pages that describe this would be most welcome.

Thank You

Peter  

 

3 REPLIES
Teradata Employee

Re: Passing values from outer query to a sub query

check the "Correlated Subquery" section of the SQL Data Manipulation Language section of the Teradata documentation. Good luck!

Re: Passing values from outer query to a sub query

I understand you want to have a list of all customers who have done certain purchases between current_month and current_month - 100?

If so, I believe this covers your question:

 

select

    a.customer#

    ,a.month_id

    ,a.other_data

from

    customer_table a

where exists (

    select 1

    from invoice_lines_table b

    where a.customer# = b.customer#

    and a.month_id = b.month_id

    and conditions -- define transaction types

).

 

This way you use a correlated subquery and the exists will have Teradata use the fast inclusion join.

 

Highlighted
Junior Contributor

Re: Passing values from outer query to a sub query

What you're trying to do ("pass in the month_id to the sub selects") is Standard SQL's Lateral Join, which is not supported in Teradata.

 

It's hard to tell exactly how to write this without more details...

Can you add some example data & expected result?