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: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Wed, 12 Dec 2007 10:48:11 -0800 (PST)
Message-ID: <eb746cda-53d1-4c0b-afc8-9263bde31b65@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.

A better approach might be to use plsql ( that can see which parameters are used ) and return a ref cursor Received on Wed Dec 12 2007 - 12:48:11 CST

Original text of this message

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