The "Find Object References - Record Field" feature in Application Designer,
when used to search for queries utilizing a specific field (i.e. "EMPL_STATUS"),
is not accurate because it returns queries that do NOT use the field (this
behavior is caused by adding a field to a query and then removing it -- the
field remains in the PSQRYFIELD table, so it shows up on the object references
search).
Furthermore, the "find object references" feature is of limited use since it
is not clear if the field is merely in the select columns, or actually in the
"where" criteria.
Use this SQL code for more precise results ...
1. The COLUMNNUM column indicates the field is used in the Select if the value > 0
2. The USECOUNT column indicates the field is used in the Criteria if the value > 0
when used to search for queries utilizing a specific field (i.e. "EMPL_STATUS"),
is not accurate because it returns queries that do NOT use the field (this
behavior is caused by adding a field to a query and then removing it -- the
field remains in the PSQRYFIELD table, so it shows up on the object references
search).
Furthermore, the "find object references" feature is of limited use since it
is not clear if the field is merely in the select columns, or actually in the
"where" criteria.
Use this SQL code for more precise results ...
1. The COLUMNNUM column indicates the field is used in the Select if the value > 0
2. The USECOUNT column indicates the field is used in the Criteria if the value > 0
3. To exclude delivered queries, use the LIKE command (i.e. this codes searches only "ABC" queries)
- SELECT QRYNAME "QUERY NAME",
- DECODE(COLUMNNUM, 0, 'NO', 'YES') "SELECT",
- DECODE(USECOUNT, 0, 'NO', 'YES') "CRITERIA"
- FROM PSQRYFIELD
- WHERE FIELDNAME = 'EMPL_STATUS'
- AND QRYNAME LIKE 'ABC%'
- ORDER BY QRYNAME
SELECT QRYNAME "QUERY NAME",
DECODE(COLUMNNUM, 0, 'NO', 'YES') "SELECT",
DECODE(USECOUNT, 0, 'NO', 'YES') "CRITERIA"
FROM PSQRYFIELD
WHERE FIELDNAME = 'EMPL_STATUS'
AND QRYNAME LIKE 'ABC%'
ORDER BY QRYNAME