Home » Developer & Programmer » Forms » Multiple choice or all (Forms 6i)
Multiple choice or all [message #600208] |
Sun, 03 November 2013 05:00 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c70e51380f6e8af6765f6e8fede51587?s=64&d=mm&r=g) |
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
i have a table where there are two or more field which can be passed as parameters to filter rows or i can select all of the rows, how can i achieve this using where clause.
REATE 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.
situation becomes more critical when there are more parameters.
select * from ot_job_stat where nvl(job_status,'X') in (:p_status) or nvl(job_status,'X')='All' and
nvl(Job_type,'X') in (:p_type) or Nvl(Job_status,'X')='All'
|
|
|
|
Re: Multiple choice or all [message #600213 is a reply to message #600209] |
Sun, 03 November 2013 05:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c70e51380f6e8af6765f6e8fede51587?s=64&d=mm&r=g) |
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
thanks littlefoot for the prompt response, but the problem is user will be selecting the value from list box , he may select open or close , if he doesnt select anything then all the records should appear.
i mean something like 1=1 logic which should bring all the records if there is no selection.
Somewhat similar to dynamic where clause.
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.
[Updated on: Sun, 03 November 2013 06:04] Report message to a moderator
|
|
|
Re: Multiple choice or all [message #600214 is a reply to message #600213] |
Sun, 03 November 2013 07:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Yes, "all" would evaluate to "where 1 = 1". You just need to program it; default clause might always be "where 1 = 1", and then you'd just - if needed - add additional condition(s).
|
|
|
Goto Forum:
Current Time: Wed Jun 26 13:45:05 CDT 2024
|