×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to set up correlated subqueries

How to set up correlated subqueries

How to set up correlated subqueries

(OP)
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)

RE: How to set up correlated subqueries

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

RE: How to set up correlated subqueries

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 :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close