Search code examples
sqloracle-databasepeoplesoft

How to add conditions for blank prompts in PSQuery?

I need help with myPS Query. This is how it goes below:

 SELECT A.EMPLID, B.STUDENT_ID, C.ADMIT_TERM, D.CHECKLIST_CD, 
 D.CHECKLIST_STATUS...
 FROM TABLE1 A, TABLE2 B, TABLE3C, TABLE4 D...
 WHERE……
 AND A.EMPLID = :1
 AND C.ADMIT_TERM = :2
 AND D.CHECKLIST_CD = :3
 AND D.CHECKLIST_STATUS = :4
 ….

Now there are certain conditions wherein prompts 1,2,3 and 4 are not provided that conditions below should happen:

  • All students with incomplete / in progress checklists will be shown regardless of when they were created.
  • All students whose checklists were completed within the current Term will be shown.

At this point, I don't know how to add a condition if all those prompts are blank. Can you advise what I need to do next? Sub-queries? Expression?

Help please.

Solution

  • This is basically the same as Littlefoot's answer, but I'll try to add some more detail about how to accomplish that solution. This is an example of a query I did which is similar to what you're trying to do, with a little tweak added on the ORDER_NO field to demonstrate what you're trying to do. I don't know how to make it return certain rows only if all prompts are not provided, but this will let you return certain rows for each specific prompt which isn't provided. For example, if you don't provide a checklist status, then you can return all students that are in incomplete and in progress status.

    Note that this is a very old version of PeopleSoft, so your interface is probably different.

    First, add your prompts

    enter image description here

    Now, the trick is to make the WHERE clause for each optional field work like WHERE (D.CHECKLIST_STATUS = :4 OR (:4 = '' AND D.CHECKLIST_STATUS IN ('I', 'P')) (or whatever your status codes are). Here's how to do that (again, on my old version). The logic on the ORDER_NO field is similar to what you'll need.

    enter image description here

    Make sure to set your AND/OR operators and your parenthesis appropriately.

    enter image description here

    Clicking on the Edit button for the '' equal to :2 criteria, this is what it looks like

    enter image description here

    And the SQL looks like this

    enter image description here

    Now when you run the query, any of the prompt fields that you leave empty will either not be used to filter the results, or the default values for that field will be used.