Conditional criteria in PS Query

Sometimes you want to create a PS Query that gives you a result, based on certain criteria. Though if you keep the prompt on that criterium empty you just want to get all rows.

For example you want to select all departments in a certain company. In that case you create a PS Query with a prompt on the company field. This would look something like this.

SELECT A.SETID, A.DEPTID, A.COMPANY
FROM PS_DEPT_TBL A
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_DEPT_TBL A_ED
WHERE A.SETID = A_ED.SETID
AND A.DEPTID = A_ED.DEPTID
AND A_ED.EFFDT <= SYSDATE)
AND A.COMPANY = :1)

Now if you run the query it will prompt you to fill in the company. You fill in a company and you get the departments in that company.

But what if you keep the prompt empty? In this case you will get the departments that are not linked to a company. This is not what we want, instead we want to basically ignore the “AND A.COMPANY = :1” criterium if the prompt is left empty. This can be achieved easily this way:

SELECT A.SETID, A.DEPTID, A.COMPANY
FROM PS_DEPT_TBL A
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_DEPT_TBL A_ED
WHERE A.SETID = A_ED.SETID
AND A.DEPTID = A_ED.DEPTID
AND A_ED.EFFDT <= SYSDATE)
AND (A.COMPANY = :1
 OR :1 IS NULL))

Now run the query and when it asks you to fill in a company just click OK. You will see that it now returns all rows and basically ignores the criterium.

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.