Home » SQL & PL/SQL » SQL & PL/SQL » user parameters (Oracle 10g)
user parameters [message #609442] Thu, 06 March 2014 20:42 Go to next message
m.abdulhaq
Messages: 125
Registered: April 2013
Location: Ajman
Senior Member
i have a table with job statuses as open or close , if i pass the value open , only those records with status as open will be filtered, similarly when i choose close only records of job_status with close will be filtered, i can do this by passing open or close as parameters but when i dont pass any values , then all the records of ot_job_stat needs to be displayed.



CREATE TABLE OT_JOB_STAT ( JOB_NO VARCHAR2(12),JOB_STATUS VARCHAR2(12),JOB_EQUIP VARCHAR2(12),JOB_TYPE VARCHAR2(12))

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE ) values ('0001','Open','CNC1','Prev')

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE ) values ('0002','Close','CNC2','Brk')

select * from ot_job_stat where nvl(job_status) in ('Open') or nvl(job_status)='All' --All should bring both the records.





Re: user parameters [message #609448 is a reply to message #609442] Fri, 07 March 2014 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

select * from ot_job_stat where job_status = :param or :param is null;

icon14.gif  Re: user parameters [message #609492 is a reply to message #609448] Fri, 07 March 2014 09:13 Go to previous messageGo to next message
m.abdulhaq
Messages: 125
Registered: April 2013
Location: Ajman
Senior Member
Thanks a lot Michael, it worked.
Re: user parameters [message #609517 is a reply to message #609448] Sat, 08 March 2014 03:11 Go to previous messageGo to next message
m.abdulhaq
Messages: 125
Registered: April 2013
Location: Ajman
Senior Member
sorry to disturb you , but when i am adding multiple conditions to the above query , its not giving me correct results.Like i added date column to the table and i want to filter data based on multiple ranges like within the time span what job_types i want , but the query is yielding wrong results.




CREATE TABLE OT_JOB_STAT ( JOB_NO VARCHAR2(12),JOB_STATUS VARCHAR2(12),JOB_EQUIP VARCHAR2(12),JOB_TYPE VARCHAR2(12),JOB_DATE DATE);

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE,JOB_DATE ) values ('0001','Open','CNC1','Prev',TO_DATE('01/01/2014'));

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE , JOB_DATE ) values ('0002','Close','CNC1','Brk',TO_DATE('01/01/2014'));

insert into ot_job_stat ( JOB_NO ,JOB_STATUS ,JOB_EQUIP ,JOB_TYPE , JOB_DATE ) values ('0003','Close','CNC2','Prev',TO_DATE('02/02/2014'));

select * from ot_job_stat
WHERE
   JOB_DATE between TO_DATE(:F_JOB_DT,'DD/MM/RRRR') AND  TO_DATE(:T_JOB_DT,'DD/MM/RRRR') 
  and job_type in :job_type or :job_type is null   


--I am passing job_type as null and :F_JOB_DT = '01/01/2014' AND :T_JOB_DT = '01/01/2014'

--its getting me all the three records , but actually i dont want the record of 02/02/2014.

--result is


JOB_NO	JOB_STATUS	JOB_EQUIP	JOB_TYPE	JOB_DATE
0002	Close	          CNC1	           Brk	        1/1/2014
0003	Close	          CNC2	          Prev	        2/2/2014
0001	Open	          CNC1	          Prev	        1/1/2014





Re: user parameters [message #609518 is a reply to message #609517] Sat, 08 March 2014 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59422
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Parentheses are missing:
and ( job_type in :job_type or :job_type is null )

Re: user parameters [message #609523 is a reply to message #609442] Sat, 08 March 2014 08:01 Go to previous messageGo to next message
EdStevens
Messages: 306
Registered: September 2013
Senior Member
https://community.oracle.com/thread/3527899
Re: user parameters [message #609526 is a reply to message #609518] Sat, 08 March 2014 11:35 Go to previous message
m.abdulhaq
Messages: 125
Registered: April 2013
Location: Ajman
Senior Member
thamks michael.
Previous Topic: Want to get first day of last month
Next Topic: clarification about selectivity
Goto Forum:
  


Current Time: Thu Oct 23 14:36:47 CDT 2014

Total time taken to generate the page: 0.08310 seconds