Tuesday, October 03, 2006

Define Optional Query Prompt/Criteria in PeopleSoft Query

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.

7 comments:

amitmast said...

Just to add....

The Above expression is used when :1 is Character type.
Use
:1 or :1 = 0
when it is Numeric.

amitmast said...

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????

The Maggio's said...

How would you perform this when :1 is a date?

Ketan Kothari said...

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.

Rakesh said...

We tried using this method, but didn't work with DB2 zOS platform.

Rakesh said...

We were using this in SQL Server but it doesn't work with DB2 zOS

Unknown said...

Hey
I want to pass JobCode (Prompt) from the page to the Query. How I can pass that value?