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

  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.

Leave a Reply

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

*
*

*

Recommended for you:
XML / BI Publisher Tutorial
You Don’t Have to Write PeopleCode to Create an XML / BI Publisher Report using PS Query!

Close