SQL-032: Retrieve all active non-indexed batches¶
This statement aggregates all IMGSYS rows where IDXFL = 'N' (not indexed) and DELCD = ' ' (not deleted), grouping by batch ID and scan date, and excluding batches named AUTOIMPORT or FAXIMPORT. It uses the Oracle hint /*+ INDEX(imgsys IMGSYS_IDXFL_IDX1) */ to encourage index usage. Executed by clsIndexBatchCollection.BeforeRetrieve.
Classification¶
| Field | Value |
|---|---|
| SQL ID | SQL-032 |
| Operation type | Read |
| Execution method | Inline SQL |
| Database | Oracle |
| Schema | [unqualified]** |
| Command type | Text** |
| Business object | clsIndexBatchCollection |
| Criteria property | RetrieveSQL |
| Lifecycle method | BeforeRetrieve |
Parameters¶
No parameters.
Tables¶
| Table | Access | Columns used |
|---|---|---|
| IMGSYS | SELECT (aggregated) | BATID, SCANDT, IDXFL, DELCD |
Sample SQL¶
select /*+ INDEX(imgsys IMGSYS_IDXFL_IDX1) */
nvl(batid,' ') BatchId,
scandt ScanDate,
count(*) RemainingDocs
from imgsys
where nvl(idxfl,'N') = 'N'
and nvl(delcd,' ') = ' '
and upper(nvl(batid,' ')) not in ('AUTOIMPORT','FAXIMPORT')
group by nvl(batid,' '), scandt
Operations and processes¶
Note: Statements marked with
**could not be confirmed; treat as unknown until verified.
Sources¶
Business\SAIF.Business.Imaging\clsIndexBatchCollection.vb