This discussion is archived
4 Replies Latest reply: Dec 23, 2010 3:02 PM by WhiteHat RSS

If statement within query

788195 Newbie
Currently Being Moderated
Is it possible to imbed an if statement within an oracle query?
This is a package function that I pass variables into.
I am passing an ID/IDs from a dropdown list. The user can pick one or many or none, if they pick none I am passing a zero and and to search for all of the IDs withing the database.

Function GetStuff(myID varchar)

Select * from myTable
if myID != 0 Then
where UsersID in (myID)
End If;

End function.
  • 1. Re: If statement within query
    Solomon Yakobson Guru
    Currently Being Moderated
    select  *
      from  myTable
      where myID = 0
         or UsersID in (myID)
    /
    However, you need to explain more on ID/ IDs_ from a dropdown list. If it is a list, the above code will not work.

    SY.
  • 2. Re: If statement within query
    bluefrog Expert
    Currently Being Moderated
    You'll have to make the function return something,
    so maybe something like this:
    function GetStuff (myID in varchar2) return number as
      l_Cnt number;
      begin
          select count(*)
        into   l_Cnt
        from   myTable
        where  0      != myID
        and    UserID = myID;
    
    
        return l_Cnt;
        
    end GetStuff;
  • 3. Re: If statement within query
    Syed Ullah Journeyer
    Currently Being Moderated
    Give some example values of myID. Since you are passing it as a string, how are you passing multiple values? Are the values separated by blank or comma?
  • 4. Re: If statement within query
    WhiteHat Expert
    Currently Being Moderated
    Select * from myTable
    if myID != 0 Then
    where UsersID in (myID)
    End If;

    End function.
    I think you just need to put it in your where clause:
    select * from myTable
    where myid != 0  -- myid doesn't have to be a column of the table to be included in the where clause
      and usersID in (myID);
    but as others said - if myid contains multiple values, you need to process it differently depending on whether it's one string or a collection etc.


    edit: I just realised that other people had answered with the exact same statement as I just did..... that's what I get for not reading replies thouroughly.

    Edited by: WhiteHat on Dec 24, 2010 9:01 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points