How to set up correlated subqueries

[moepower]

I've this simple SQL statement that I want to replicate in Brio but couldn't figure out how to work with Correlated Subquery. Any help is greatly appreciated...

select yy.customer_id, yy.sales_amount
from sale_dim xx, customer_tracker yy
where yy.sales_date >= to_date('04/16/02','mm/dd/yy')
and yy.sales_date < to_date('04/17/02','mm/dd/yy')
and yy.sales_id = xx.sales_id
and xx.sales_location = 'Colorado'
and exists
(select 1
from sales_dim a, customer_tracker b
where b.sales_date >= to_date('01/16/01','mm/dd/yy')
and b.sales_date < to_date('01/17/02','mm/dd/yy')
and b.sales_id = a.sales_id
and a.sales_location = 'Colorado'
and a.sales_zone = 'Central'
and b.customer_id = yy.customer_id
and b.sales_amount < yy.sales_amount)

 

[KBHere]

Try something like this:

begin-program
do cust
end-program
begin-procedure cust
begin-select
yy.customer_id ()
yy.sales_amount ()
do subquery4cust
from sale_dim xx, customer_tracker yy
where yy.sales_date >= to_date('04/16/02','mm/dd/yy')
and yy.sales_date < to_date('04/17/02','mm/dd/yy')
and yy.sales_id = xx.sales_id
and xx.sales_location = 'Colorado'
end-select
end-procedure

 

[KBHere]

Here is the rest:

begin-procedure subquery4cust
begin-select
!field to select that select 1 retrieves...
from sales_dim a, customer_tracker b
where b.sales_date >= to_date('01/16/01','mm/dd/yy')
and b.sales_date < to_date('01/17/02','mm/dd/yy')
and b.sales_id = a.sales_id
and a.sales_location = 'Colorado'
and a.sales_zone = 'Central'
and b.customer_id = yy.customer_id
and b.sales_amount < yy.sales_amount
end-select
end-procedure

One should be nested within the other query. I am not familiar with your database tables, but the solution should be something like above.

I hope this helps