---
title: "SQL-010: clsXDeferredPrintCollection.RetrieveSQL"
summary: Dynamically assembled SELECT from XDEFERRED_PRINT with two retrieval modes.
status: draft
owner: <owner>
last_reviewed: 2026-07-24
library: SAIF.Services.Printing.Remote
reference_type: package
---

# SQL-010: clsXDeferredPrintCollection.RetrieveSQL

Retrieves deferred print items from `XDEFERRED_PRINT` using one of two `WHERE` clause variants, assembled dynamically at runtime in `BeforeRetrieve`. Variant A (`byDate`) filters by printed date or 30-day staleness; Variant B (`byDocDescr`) filters by document description for unprinted items. Results are ordered by the ten sort-value columns. Eligibility is governed by [BR-002](../business-rules.md#br-002-deferred-print-retentioneligibility-rule).

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-010 |
| **Operation type** | Read (Retrieve) |
| **Execution method** | Inline, dynamically assembled (WHERE clause and ORDER BY appended in `BeforeRetrieve`) |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsXDeferredPrintCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` |

## Parameters

Parameters vary by `RetrieveType`:

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PrintedDate` | Input | Date | — | Used when `RetrieveType = byDate` — cutoff date for "printed before this date" |
| `:ThirtyDaysAgoDate` | Input | Date | — | Used when `RetrieveType = byDate` — `Today.AddDays(-30)`, catches stale unprocessed items |
| `:DocumentDescr` | Input | String | 60 | Used when `RetrieveType = byDocDescr` — exact-match document description filter |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [XDEFERRED_PRINT](../database-tables.md#xdeferred_print) | SELECT | `COPIES_COUNT`, `CREATE_DATE`, `CREATE_USER`, `DOCUMENT_DESCR`, `DUPLEX_CODE`, `FILE_NAME`, `PRINTED_DATE`, `SORT0_VALUE`–`SORT9_VALUE`, `XML_VALUE_TEXT`, `XDEFERRED_SEQ_NUM` |

## Sample SQL

```sql
-- Base SELECT (Criteria.RetrieveSQL):
Select
    XDEFERRED_PRINT.COPIES_COUNT         as CopiesCount,
    XDEFERRED_PRINT.CREATE_DATE          as CreateDate,
    XDEFERRED_PRINT.CREATE_USER          as CreateUser,
    XDEFERRED_PRINT.DOCUMENT_DESCR       as DocumentDescr,
    XDEFERRED_PRINT.DUPLEX_CODE          as DuplexCode,
    XDEFERRED_PRINT.FILE_NAME            as FileName,
    XDEFERRED_PRINT.PRINTED_DATE         as PrintedDate,
    XDEFERRED_PRINT.SORT0_VALUE          as Sort0Value,
    XDEFERRED_PRINT.SORT1_VALUE          as Sort1Value,
    XDEFERRED_PRINT.SORT2_VALUE          as Sort2Value,
    XDEFERRED_PRINT.SORT3_VALUE          as Sort3Value,
    XDEFERRED_PRINT.SORT4_VALUE          as Sort4Value,
    XDEFERRED_PRINT.SORT5_VALUE          as Sort5Value,
    XDEFERRED_PRINT.SORT6_VALUE          as Sort6Value,
    XDEFERRED_PRINT.SORT7_VALUE          as Sort7Value,
    XDEFERRED_PRINT.SORT8_VALUE          as Sort8Value,
    XDEFERRED_PRINT.SORT9_VALUE          as Sort9Value,
    XDEFERRED_PRINT.XML_VALUE_TEXT       as XMLValueText,
    XDEFERRED_PRINT.XDEFERRED_SEQ_NUM    as XdeferredSeqNum
From
    XDEFERRED_PRINT
Where
-- WhereClause appended at runtime in BeforeRetrieve, depending on Criteria.RetrieveType:

-- Variant A — RetrieveType = byDate:
    XDEFERRED_PRINT.PRINTED_DATE < :PrintedDate
    OR XDEFERRED_PRINT.CREATE_DATE < :ThirtyDaysAgoDate

-- Variant B — RetrieveType = byDocDescr:
    XDEFERRED_PRINT.DOCUMENT_DESCR = :DocumentDescr
    AND XDEFERRED_PRINT.PRINTED_DATE IS NULL

-- SortOrder appended in all cases:
ORDER BY
    XDEFERRED_PRINT.SORT0_VALUE,
    XDEFERRED_PRINT.SORT1_VALUE,
    XDEFERRED_PRINT.SORT2_VALUE,
    XDEFERRED_PRINT.SORT3_VALUE,
    XDEFERRED_PRINT.SORT4_VALUE,
    XDEFERRED_PRINT.SORT5_VALUE,
    XDEFERRED_PRINT.SORT6_VALUE,
    XDEFERRED_PRINT.SORT7_VALUE,
    XDEFERRED_PRINT.SORT8_VALUE,
    XDEFERRED_PRINT.SORT9_VALUE
```

## Operations and processes
- [Deferred print queue lifecycle](../operations/deferred-print-queue-lifecycle.md)

## Business rules
- [BR-002: Deferred print retention/eligibility rule](../business-rules.md#br-002-deferred-print-retentioneligibility-rule)

> **Note:** Statements marked with `**` could not be confirmed; treat as unknown until verified.

## Sources
- `Services\SAIF.Services.Printing.Remote\clsXDeferredPrintCollection.vb`