Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » If else in Oracle SQL Query (ORACLE and PLSQL)
If else in Oracle SQL Query [message #545327] Tue, 28 February 2012 06:36 Go to next message
m3nhaq
Messages: 20
Registered: September 2011
Location: Pakistan
Junior Member
Hello Friends!

I need a solution of this query.
I want to write a single query in a procedure.
If procedure get rolNo = 'TR' , then it behave like this

Procedure Show_Records (rolNo = 'TR')
IS 
    Select c.date , c.rolNo
    From my_table  c
    where c.date = system_date
    AND   c.rolNo = 'RT';
end;


else it behave like this
Procedure Show_Records (rolNo = 'TR')
IS 
    Select c.date , c.rolNo
    From my_table  c
    where c.date = system_date
    AND   c.rolNo <> 'RT';
end;



Please, guide me . I am writing a complex query and cannot want to write 2 queries, just for <> and = operators.

Thanks.
Re: If else in Oracle SQL Query [message #545330 is a reply to message #545327] Tue, 28 February 2012 07:37 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hello,

firstly, you should rename the parameter differently than the column name (e.g. P_ROLNO), otherwise you will not be able to distinguish them in the query (Oracle will prefer column name).

Secondly, what about simply stating the conditions you wrote in SQL?
  AND (    ( p_rolno = 'TR' and c.rolno = 'RT' )
        OR ( p_rolno != 'TR' and c.rolno != 'RT' ) )

(you wanted to check the parameter for non-equality in the second snippet, did not you?)
Re: If else in Oracle SQL Query [message #545371 is a reply to message #545330] Tue, 28 February 2012 23:18 Go to previous message
m3nhaq
Messages: 20
Registered: September 2011
Location: Pakistan
Junior Member
Thank u sooooooo much....

U solve my problem of writing 1 procedure only for this query, instead of 2.

Thanks a lot.. Smile Smile
Previous Topic: send email from within apex application
Next Topic: Regions weird in firefox
Goto Forum:
  


Current Time: Mon Mar 18 23:13:17 CDT 2024