I am trying to implement this with a more complicated query.
Thoughts on a where clause like this, or one using between and dates?
( VW_Person.Person_Number = :APP_PERSON_PERSON
Or :APP_IS_ADMIN = 'Y')
And ( :P21_CRITERIA_STATUS = 'A' AND STATUS_TRAN_PERSON = 'ACTIVE'
OR :P21_CRITERIA_STATUS = 'I' AND STATUS_TRAN_PERSON = 'INACTIVE'
OR :P21_CRITERIA_STATUS NOT IN ('A', 'I'))
And (:P21_CRITERIA_EPRGENRL_ID IS NULL OR Employer_ID = :P21_CRITERIA_Employer_ID);
Note: Criteria_Status is a group letting the user select (A)ctive, (I)nactive, or Al(L) people.
Thanks
Joe
Delivering real value with innovative strategies and solutions
Very helpful both for the code, and the perspective. Are there security concerns here from having a function that creates a query from inputs vs. a "hard coded" query?
No extra security concerns at all. In fact, that's one reason I decided to write the function. This function uses bind variable and, although it does concatenate on unescaped column names, those columns names are defined by the developer at design time, not run time, and they can't be changed at run time. The only security concern is with the developer, which is the same as it is for a hard coded query.
Joe Kerr
I am trying to implement this with a more complicated query. Thoughts on a where clause like this, or one using between and dates? ( VW_Person.Person_Number = :APP_PERSON_PERSON Or :APP_IS_ADMIN = 'Y') And ( :P21_CRITERIA_STATUS = 'A' AND STATUS_TRAN_PERSON = 'ACTIVE' OR :P21_CRITERIA_STATUS = 'I' AND STATUS_TRAN_PERSON = 'INACTIVE' OR :P21_CRITERIA_STATUS NOT IN ('A', 'I')) And (:P21_CRITERIA_EPRGENRL_ID IS NULL OR Employer_ID = :P21_CRITERIA_Employer_ID); Note: Criteria_Status is a group letting the user select (A)ctive, (I)nactive, or Al(L) people. Thanks Joe