Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Complex logic query that I cannot figure out:(

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

From: MrHelpMe <clinttoris_at_hotmail.com>
Date: Wed, 12 Dec 2007 08:40:01 -0800 (PST)
Message-ID: <75ac9d64-c814-48b8-bf15-654dfb10ae77@i29g2000prf.googlegroups.com>


On Dec 12, 11:30 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel,

Thanks for replying not sure on what you mean by Dynamic native sql ( I will investigate) but I think I will opt for option 2. Now I think I will need some help please.

So in the WHERE clause I guess you are saying that I can put an if Statement (didn't know I could). So here goes but I am still finding difficulty as I know what I want to say but have no idea how to code it:(

if Group = 'ALL' then (Select FullName from Table 1) ELSE IF
Group = 'ATT' then (Select DisplayName from Table 3 where Status from Table 1 = 'Done')------ I know this is wrong Else if
Group = 'NULL' then (I am totally lost here).--- not sure how to subtract the employees from the 2 tables. End IF

If you think Dynamic Sql (I will research this) is easier then please let me know. Thanks again Daniel. Received on Wed Dec 12 2007 - 10:40:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US