PS Query: Execute only the criteria populated and ignore those that are blank/selective bind executed

Here's a simple tip for PS Query users. Have you ever had a problem where you need to show several prompts as criteria in your PS Query but user has an option to only populate few of them and ignore others?

For example:

I have set three fields as criteria (via prompt) in my PS Query namely EMPLID, HR_STATUS and DEPTID. If the user only populate any of these and retain other as blank, it should still pull the correct data. For instance, if the user only populated the EMPLID prompt, the query should only show all rows with this EMPLID. If user populated DEPTID, it will pull all data with this DEPTID regardless of the EMPLID and HR_STATUS. It's like the PS Query is ignoring the criteria without value entered by the user in the prompt field.

Here's an example of query on how to ignore those criteria that were not populated.

SELECT * 

FROM   PS_JOB 

WHERE  ( EMPLID = :1 

          OR TRIM(:1) IS NULL ) 

       AND ( HR_STATUS = :2 

              OR TRIM(:2) IS NULL ) 

       AND ( DEPTID = :3 

              OR TRIM(:3) IS NULL )

...MAX effdt

...MAX effseq 

Did it work? Please provide feedback if it worked or if you encounter problem.