Query Integrity

The following table describes the audit queries and resolutions for this area:

Query Description Resolution

QUERY-01

Query Definition Select count does not match the record count that is in the Query Select table. The query definition is corrupt.

Run the following SQL:

DELETE FROM PSQRYDEFN
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYSELECT
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYRECORD
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYFIELD
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYCRITERIA
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYEXPR
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYBIND
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

QUERY-02

Query Definition Expression count does not match the record count in the Query Expression table.

Run the following SQL:

UPDATE PSQRYDEFN
SET EXPCOUNT =
 (SELECT COUNT(*)
  FROM PSQRYEXPR C
  WHERE OPRID = ‘X’ AND
   QRYNAME = ‘Y’)
WHERE OPRID = ‘X’ AND
 QRYNAME = ‘Y’

QUERY-03

Query Definition Bind count does not match the record count in the Query Bind table.

Run the following SQL:

UPDATE PSQRYDEFN
SET BNDCOUNT =
 (SELECT COUNT(*)
  FROM PSQRYBIND
  WHERE OPRID = ‘X’ AND
   QRYNAME = ‘Y’)
WHERE OPRID = ‘X’ AND
 QRYNAME = ‘Y’

QUERY-04

Query Definition Record name does not exist in the Record Definition table.

See resolution for QUERY-07.

QUERY-05

Query Definition Record JoinRecord name does not exist in the Query Record table.

See resolution for QUERY-01.

QUERY-06

Query Definition Record JoinField name does not exist in the Query Field table.

See resolution for QUERY-01.

QUERY-07

Query Field Record Name does not exist in Record Definition Table.

To salvage the query, you must use Application Designer to re-create the record definition.

Having re-created the record, run Query and open the offending query. Remove or repair the affected areas and save the query.

Or, if the query is not important, you can delete the entire query definition by using the resolution for QRY-01.

QUERY-08

Query Definition Field name does not exist in the Field Definition table.

If the record on which this field appears is deleted, you have seen errors for every referenced field that belongs to the deleted record. If this is the case, see the resolution for QUERY-1.

If this is not the case, some fields that the query depends on are either deleted or renamed. Run Query and open the offending query. Query automatically repairs itself and updates the query definition in the database.

QUERY-09

Query Selection Record count does not match the record count in Query Record table.

See resolution for QUERY-01.

QUERY-10

Query Selection Field count does not match the record count in Query Field table.

See resolution for QUERY-01.

QUERY-11

Query Selection Criteria count does not match the record count in Query Criteria table.

See resolution for QUERY-01.

QUERY-11A

Query Selection Criteria having count does not match the record count in Query Criteria table.

See resolution for QUERY-01.

QUERY-12

Query Selection Parent select number does not exist in Query Select table.

See resolution for QUERY-01.

QUERY-13

Query Criteria Selection-Left does not exist in the Query Selection table.

Run Query and delete the corrupted criteria entry. If you can't open the query, run the following SQL to delete the corrupted criteria entry:

DELETE FROM PSQRYCRITERIA
WHERE OPRID = 'X'AND
 QRYNAME ='Y' AND
 CRTNUM = count

QUERY-14

Query Criteria Selection-Right1 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-15

Query Criteria Selection-Right2 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-16

Query Criteria Field-Left does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-17

Query Criteria Field-Right1 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-18

Query Criteria Field-Right2 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-19

Query Criteria Expression-Right1 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-20

Query Criteria Expression-Right2 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-22

This audit identifies queries that were created without PUBLIC access.

This is normal; the audit insures that PeopleSoft does not deliver non-public queries as part of its standard delivered products.

QUERY-23

This audit identifies queries that reference non-existent database records. The query definitions are corrupt.

This is an internal PeopleSoft audit. Contact My Oracle Support for resolution.

Run the following SQL:

DELETE FROM PSQRYDEFN
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYSELECT
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYRECORD
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYFIELD
WHERE OPRID = 'X'
 AND QRYNAME = 'Y'

DELETE FROM PSQRYCRITERIA
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYEXPR
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

DELETE FROM PSQRYBIND
WHERE OPRID = 'X' AND
 QRYNAME = 'Y'

QUERY-24

This audit identifies queries that were created with the name UNTITLED.

Queries should not be saved as UNTITLED. Either rename or delete these queries.

QUERY-25

This audit identifies queries that were created with blank query names.

You must either rename or delete these queries.

QUERY-26

This audit identifies queries that contain unions but select an unequal number of fields.

Ensure that every select statement in the query has an equal number of fields selected. These fields must also match in display type and length.

QUERY-27

This audit identifies the queries that reference query translate fields that have been incorrectly modified to a non-translate type. When run in Microsoft Windows Query Manager, queries that reference these fields might return an inaccurate zero result set because the query metadata is corrupt.

Run the following SQL: 

UPDATE PSQRYFIELD
SET XLATTYPE = 1
WHERE FIELDNAME IN
 (<list of fieldnames
  from the audit>)