If you don’t fill in a prompt when running a PS Query, the query usually returns no row. If you want the query to still fetch data (ignoring the condition), here’s something for you.
Let’s first see what I’m talking about in a simple PS Query that fetches some fields from VOUCHER and VOUCHER_LINE records. The query would prompt us to enter the BUSINESS_UNIT. Below is the SQL underlying the Query.
SELECT A.BUSINESS_UNIT, A.VOUCHER_ID, A.VOUCHER_STYLE, A.INVOICE_ID, TO_CHAR(A.INVOICE_DT,'YYYY-MM-DD'), A.VENDOR_SETID, A.VENDOR_ID, A.VNDR_LOC FROM PS_VOUCHER A, PS_VOUCHER_LINE B WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.BUSINESS_UNIT = :1 ) |
Now when I run this query, the Business unit prompt pops up.
Here, if I just hit enter (or click ok without entering any value), it passes on a blank feed into the query and it returns no rows.
By the way, how does the SQL look like in this case?
With blank (one space) or no values (Null) entered in the prompt, the sql formed will have criteria that would be equated to blank.
SELECT A.BUSINESS_UNIT, A.VOUCHER_ID, A.VOUCHER_STYLE, A.INVOICE_ID, TO_CHAR(A.INVOICE_DT,'YYYY-MM-DD'), A.VENDOR_SETID, A.VENDOR_ID, A.VNDR_LOC FROM PS_VOUCHER A, PS_VOUCHER_LINE B WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.BUSINESS_UNIT = ' ') |
Now, this will not fetch any output unless there are matching rows with blank values.
Generally, prompts involving key fields wouldn’t be null or blank, so this scenario is valid in most cases.
However, if I want to use the same query to fetch data for all Business Units, there isn’t much I can do with the present design. One thing that I could do is to change the query to use the prompt in a LIKE criteria and let the user enter a % when prompted for the Business Unit.
Optional Prompt
But if you want to spare the users from entering anything in the prompt and still get the query to fetch results for all Business Units, here’s what you can do.
Go to the expressions tab
Add Expression, Enter ' ' [space], click ok.
Add criteria to the above expression.
Change the logical operator to OR for the above added expression.
Group the last two Criteria as shown in the below image.
This will ensure that even if the Business unit is not entered (or a space is entered), the query runs for all the business units. This is how the SQL would look.
SELECT A.BUSINESS_UNIT, A.VOUCHER_ID, A.VOUCHER_STYLE, A.INVOICE_ID, TO_CHAR(A.INVOICE_DT,'YYYY-MM-DD'), A.VENDOR_SETID, A.VENDOR_ID, A.VNDR_LOC FROM PS_VOUCHER A, PS_VOUCHER_LINE B WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND ( A.BUSINESS_UNIT = :1 OR ' ' = :1) ) |
Over to you… How else can you achieve this?
Use a DECODE (:1, ‘ ‘, A.BUSINESS_UNIT, :1) for Oracle
or
CASE statement in SQL Server
Unfortunately this doesn’t work for dates, but for Business Units it works like a charm.
Thanks Jeffry , Decode is short and sweet !!
(:1, ‘ ‘, A.country, :1) is not working for me
Sneha, Can you paste the entire Query here
I have added DECODE (:2, ` `, C.GU_NODE_LVL3, :2) as an expression and when I run the query I am prompted for a value, but the output ignores the value I enter and returns all rows.
The expression does not appear in the actual sql – how to I add/write this so it appears in the criteria?
To make an optional prompt, change the “Edit Type” on the “Prompt” page to “No Table Edit” and add the relevant “Prompt Table”. This will ensure that you can pick a valid entry but still leave it blank.
Thank You
You can also use NVL — A.FIELDNAME = NVL(LTRIM(:1,”),A.FIELDNAME)
Super…
Thanks Alex for sharing the Alternative way.
Thanks Manikandan and Srinivas for your comments
Really It’s very useful Alex…..
I use this in SQL Server:
(
=(CASE WHEN :1 = ‘ ‘ THEN ELSE :1 END)
OR
Like :1
)
(
FIELD =(CASE WHEN :1 = ‘ ‘ THEN FIELD
ELSE :1 END)
OR
FIELD Like :1
)
Hi,
Could you please help me to clear it out.
We added a drop down in a page. We have a prompt table with No Edit and I have mapped the required record and filed to the table. The drop down displays the values that were mapped to the Prompt table
My requirement is to display only a selected values in the drop down. Say, to display Business Unit Values in the Drop down only if the Active status of the Business Unit is ‘A'(Active)
Thanks much for the help
Helpful. Thankyou 🙂
How can I do this to make Date prompts optional? When I use the above I receive a SQL error while running (An unsupported parameter datatype was specified).
As an alternative I tried adding dynamic dates to the Prompt using the Default Value on the prompt (%Date + 30) however it does not like the addition on the date variable.
On the prompt definition, click the optional check box.