Need help filling in Parameter Syntax

[LadyDi02]

Hello Experts,

Using oracle 9 i and was wondering if someone could help fill in my syntax. I have a stored procedure that accepts 3 parameters. One of the parameters in question is GSelection. What I am trying to code is if the user types ALL for the parameter selection then the query will return all the data, if the user selects GGG then only values where the database field ftype is GGG gets returned, lastly this field also has blank values. So how do I code this to say if the GSelection also equals blank then show me only the blank values. I'm confused on this. Can you help? This is what I have to accomodate the ALL value(which returns all ftypes correctly) and the GGG parameter value which returns all GGG values correctly.


I am just writing the section of the code in question

WHERE
    (nVL(GroupSelection, 'ALL') = 'ALL' or a.Flagtype = GroupSelection or ............(this is where I am lost)

Thanks again experts.

 

[Thargy]

Ladydi02,

As you stand, I don't believe that the logic can be accommodated in a single where statement without it getting involved.

I presume from your code snipped that the stored procedure is performing a select based on your WHERE condition logic.

Is there any reason why you can't use a CASE select statement instead?

http://www.databasejournal.com/features/oracle/article.php/3344871

gives some samples which might be useful.

Regards

Tharg

Grinding away at things Oracular

 

[LadyDi02]

Hello thargtheslayer,

Thanks so much for your help. I apologize but I don't really see how the case statement would be of use in this situation. I think maybe I should provide some more code and a sample resultset. I will only list the parameter in question to shorten the code.
For my parameter please disregard the syntax, I am only showing this for understanding purposes.

GroupSelection IN VARCHAR2,
results_cursor IN OUT CURSOR_TYPE

----this is the bulk of the code. Rather simple.

Select a.EID,
            (NVL(a.DName, 'Unassigned')) AS DName,
          b.FlagType
From
            Table a
JOIN
            Table b
ON
            a.EID = b.EID
WHERE
           
 -----this is the part I cannot figure out, I tried
         ((NVL(GroupSelection, 'ALL') = 'ALL') or GroupSelection = b.Flagtype or .......

Here is the result set without excluding the WHERE statement so that you can see what is happening and what I am after.

EID        DName FlagType
 
12324    John      MFG
12435    Jeff        MFG
12789    Don       MFG
18999    Dino      
21345    Tina       
67890    Lee

So looking at the resultset I need for the user to select ALL and it will return the complete resultset(this has been achieved with the NVL function). Then if the user selects MFG only return the results where flagtype = MFG(i.e John, Jeff, Don have MFG)..this has been achieved with the GroupSelection = b.FlagType. Lastly return the results where FlagType is Blank. In this case the blanks results are for Dino, Tina and Lee(This has not been achieved). This is where I am confused. Is this possible? I'm not seeing where a case statement would come into play as I have used case statements many times but do not see how to apply it to this situation.

Thanks again.

 

[gunjan14]

Hi,
If this code needs to go inside stored procedure then you can use dynamic sql and then execute it.
Here is the code snippet

lstr := 'select a.eid, (nvl(a.dname, ''Unassigned'')), b.flagtype ';
lstr := lstr || 'from table a, table b ';
lstr := lstr || 'where a.eid = b.eid ';
[i]
if trim(groupselection) = '' then
-- taking care for blank value
    lstr := lstr || 'and instr(b.flagtype, '' '', 1) > 0';
elsif upper(trim(groupselection)) != 'ALL' then
    lstr := lstr || 'and b.flagtype = '''||groupselection||'''';
end if;
[/i]
execute immediate lstr;

I hope that this is what you want.

Regards,
Gunjan

 

[LadyDi02]

Gunjan14,

Thanks for the reply. I'm not sure if this is what I want only because I do not know how to incorpporate your code into my Stored Procedure. Could you show me where to place this code inside my code that I provided above. Thanks so much for your help.

 

[gunjan14]

Di,

I need certain clarification from you
a. Are you opening a cursor defined by the select stament and returning that cursor?
b. Are you planning to use the value returned by the select stement inside the same procedure?

Reagrds,
Gunjan

 

[Beilstwh]

where ((GroupSelection = 'ALL') or
(GroupSelection is null and b.flagtype is null) or
(GroupSelection is not null
and GroupSelection <> 'ALL'
and GroupSelection = b.flagtype))

Bill
Oracle DBA/Developer
New York State, USA

 

[LadyDi02]

Gunjan14 I really appreciate your help. I have tried Beilstwh suggestion and it works great. I'm not sure on the benefits of running one over the other but if there is could someone please let me know. My query seems to run fine. Thanks Beilstwh.