To create a optional query criteria, in Peoplesoft Query create a prompt as expression as shown below
:1 OR :1 = ' '
Note their is a single space between two quotes. This will make query run with or without prompt. User can either provide the prompt value to return results for a specific value or leave it blank to return rows for all values.
Subscribe to:
Post Comments (Atom)
7 comments:
Just to add....
The Above expression is used when :1 is Character type.
Use
:1 or :1 = 0
when it is Numeric.
The where clause should look like,
A.EMPLID = DECODE(:1,' ',A.EMPLID,:1)
OR
A.SOME_NUMBER = DECODE(:1,0,A.SOME_NUMBER,:1)
Isnt it????
How would you perform this when :1 is a date?
You can not use this for date field. This was working in 7.5, but tools rel. 8.x and 8.4x changed the Query expression parser. It automtically adds to_date expression for oracle db, thus making the sql inavlid, when you do not pass the value.
We tried using this method, but didn't work with DB2 zOS platform.
We were using this in SQL Server but it doesn't work with DB2 zOS
Hey
I want to pass JobCode (Prompt) from the page to the Query. How I can pass that value?
Post a Comment