CODEWITHSUNDEEP
sqloracle-database
osfar
osfar

Reputation: 413

how to make conditional where in sql?

i have table that i can't alter which have the follwoing structure

requests_table

ID   PROCESS_ID   STATUS      DATE
1        1           -1       2002
2        2            1       2003
3        2            4       2004
4        3            5       2005
5        3            14      2005
6        1            15      2006

and the process_id is foriegn key to proccess_table which is 

process_id    process_name
   1               a
   2               b        
   3               c

and i have input parameter :processID

what i want to 

if the paramter  = 5 

then return records that have STATUS 14 or 15 only

else
  if 
the paramter is null 
then return all records

else if the paramter has any value except 5 , apply this condition

where :processID = requests_table.PROCESS_ID

how to achieve that ? I cant insert any new records to the tables

Upvotes: 0

Views: 60

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this in a single statement as:

SELECT *
FROM requests_table
WHERE (:processID = 5 AND STATUS IN (14, 15)) OR
      (:processID = processId) OR
      (:processID IS NULL);

Upvotes: 1

M.Minbashi
M.Minbashi

Reputation: 352

you can use EXECUTE IMMEDIATE Query define a varchar variable and set your query into it after that check your parameters and set your query's parameters look at this example :

declare   
  myQuery   varchar(500) ;   
  parameter number := 5;   
begin   
  myQuery := ' select * from requests_table ';   

  if parameter = 5 then   
    myQuery := myQuery || ' where (status=14 OR status=15) ' ;   
  end if;    

  EXECUTE IMMEDIATE myQuery;   
end;    
end;

parameter is your value that you want to pass to it

Upvotes: 0

dbac
dbac

Reputation: 341

I would suggest using whatever is calling to SQL (eg PHP) to switch between different SQL statements, like 

if(processID==5){
    query="SELECT * FROM requests_table WHERE STATUS=14 OR STATUS=15"
} else if (processID==null){
    query="SELECT * FROM requests_table"
} else {
    query="SELECT * FROM requests_table WHERE PROCESS_ID=processID"
}

and then run the chosen query.

if you want to have it all in a single SQL statement you can do 

SELECT * FROM requests_table WHERE (processID=5 AND (STATUS=14 OR STATUS=15)) OR processID NULL OR PROCESS_ID=processID

Upvotes: 1

Related Questions