Re: Complex logic query that I cannot figure out:(

MrHelpMe wrote:
> Hello again experts,
>
> I am working on a query that pulls from 3 tables.
>
> Table 1 - houses all user information
> Table 2 - houses all counts for the users
> Table 3 - houses all user info. for those users that are unique.
>
> I have three parameter ID, Status and Group
>
> I am trying to figure out how to write my query so that if a user
> selects a set of 3 parameters it pulls from Table 1 for example and if
> he/she chooses another set of 3 parameters it pulls from table 3 ect.
>
> Here is my query that hopefully someone could help me finish. I am
> using oracle 9.2
>
> Select a.ID,
> a.Name,
> b.ID
>
> FROM
> Table 1 a
> LEFT OUTER JOIN
> Table 2 b
> ON
> a.id = b.id
> RIGHT OUTER JOIN Table 3 c
> ON
> a.id = c.id
> WHERE
> a.id in ID
> AND
> a.status in STATUS
> AND
>
> ---- this is where I seem to be stuck---------------------
> if Group = 'ALL' then pull user info. from Table a
> if Group = 'ATT' then show users in table 3 where there status in
> table 1 = 'DONE'
> if Group = 'NULL' then look at all the users in table 1 and remove
> those that are the same in table 3(since table 3 will house users that
> are also found in table 1 but that are unique) and display the results
> of table 1
>
> If someone could figure this out this would be most appreciated.
> Thanks.

You have two choices:

1. Use Native Dynamic SQL
2. Write an IF statement that, based on the parameters supplied, chooses one path or the other

Try both ... choose one.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org