Optional Prompting in PS Query

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.

Business Unit Prompt in Query

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.

No results in Query

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 expressions in Query

Add Expression, Enter ' ' [space], click ok.

Edit Expressions in Query

Add criteria to the above expression.

Edit Criteria in Query

Change the logical operator to OR for the above added expression.

And Condition

Or Condition

Group the last two Criteria as shown in the below image.

Group Criteria

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?

Tags: 
Subscribe to Comments RSS Feed in this post
18 Responses
  1. 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?

  2. 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.

  3. You can also use NVL — A.FIELDNAME = NVL(LTRIM(:1,”),A.FIELDNAME)

  4. Super…

  5. Thanks Alex for sharing the Alternative way.

  6. Thanks Manikandan and Srinivas for your comments

  7. Really It’s very useful Alex…..

  8. I use this in SQL Server:

    (
    =(CASE WHEN :1 = ‘ ‘ THEN ELSE :1 END)
    OR
    Like :1
    )

  9. (
    FIELD =(CASE WHEN :1 = ‘ ‘ THEN FIELD
    ELSE :1 END)

    OR

    FIELD Like :1
    )

  10. 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

  11. Helpful. Thankyou 🙂

  12. 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.

  13. On the prompt definition, click the optional check box.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*