OraFAQ Forum: Forms » Multiple choice or all

Home » Developer & Programmer » Forms » Multiple choice or all (Forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multiple choice or all [message #600208]

Sun, 03 November 2013 05:00

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'

Report message to a moderator

Re: Multiple choice or all [message #600209 is a reply to message #600208]

Sun, 03 November 2013 05:06

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

When selecting all rows, you don't need a condition at all, this evaluates to pure "select * from your_table".

For any other condition, compose it to a valid WHERE clause and use it in SET_BLOCK_PROPERTY built-in while setting its ONETIME_WHERE (or DEFAULT_WHERE) property within the PRE-QUERY trigger.

Report message to a moderator

Re: Multiple choice or all [message #600213 is a reply to message #600209]

Sun, 03 November 2013 05:51

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

Littlefoot
Messages: 21808
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).

Report message to a moderator

Previous Topic:	compare two tables with rowid?
Next Topic:	set block property (group by)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 12:08:28 CDT 2024