SQL-PRV-02 Provider collection with a caller-supplied WHERE clause¶

Retrieves the set of PROVIDER records matching an arbitrary caller-supplied WHERE clause (m_WhereClause) appended verbatim to the base select, with no bind parameters or validation.

Classification¶

Field	Value
SQL ID	SQL-PRV-02
Operation type	Read
Execution method	Inline SQL (BOF criteria constant, verbatim caller-supplied WHERE)
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsProviderCollection`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve` / `AfterRetrieve`

Tables¶

Table	Access	Columns used
PROVIDER	SELECT	`PRIMARY_TAX_ID`, `PROVIDER_SEQ_NUM`, `UPDATE_DATE`, `UPDATE_NUM`, `UPDATE_USER`
PROVIDER_TAX_ID	JOIN (LEFT OUTER)	`TAX_ID`, `TAX_ID_DISPLAY_DESCR`, `REGISTERED_NAME`

Sample SQL¶

Security note: m_WhereClause is appended verbatim from the caller (BR-PRV-003). The example below shows a representative caller-supplied predicate.

-- Provider collection with a caller-supplied WHERE clause
SELECT
  PRV.PRIMARY_TAX_ID        AS PrimaryTaxID,
  PRV.PROVIDER_SEQ_NUM      AS ProviderID,
  PRV.UPDATE_DATE           AS UpdateDate,
  PRV.UPDATE_NUM            AS UpdateNum,
  PRV.UPDATE_USER           AS UpdateUser,
  TAX.TAX_ID_DISPLAY_DESCR  AS FormattedPrimaryTaxID,
  TAX.REGISTERED_NAME       AS RegisteredName
FROM PROVIDER PRV
  LEFT OUTER JOIN PROVIDER_TAX_ID TAX
    ON TAX.TAX_ID = PRV.PRIMARY_TAX_ID
WHERE TAX.REGISTERED_NAME_UPPER_CASE LIKE 'ACME%'

Operations and processes¶

Retrieve provider set

Business rules¶

BR-PRV-003: Unparameterized provider collection SQL

Sources¶

SAIF.Business.View.Provider/clsProviderCollection.vb