4 Replies Latest reply on Dec 23, 2010 11:02 PM by 523861

    If statement within query

      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
          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.

          • 2. Re: If statement within query
            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;
                  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
              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
                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