---
title: "SQL-008: Task filtered collection select"
summary: Builds the collection-retrieval SELECT for task searches.
status: draft
owner: SAIF
last_reviewed: 2026-07-24
library: SAIF.Business.Tasks
reference_type: package
---

# SQL-008: Task filtered collection select

Builds the collection-retrieval SELECT for task searches.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-008 |
| **Operation type** | Read |
| **Execution method** | StringBuilder-assembled |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsTaskCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:TaskSource` | Input | String | 5 | Single source filter. |
| `:SOURCE1..:SOURCEN` | Input | String | 5 | Source-list filters. |
| `:CAT1..:CATN` | Input | String | 5 | Category filters. |
| `:PriorityCode` | Input | String | 5 | Priority filter. |
| `:BeginDate` | Input | Date | N/A | Inclusive lower date. |
| `:EndDate` | Input | Date | N/A | Inclusive upper date. |
| `:MailID` | Input | String | 30 | Assignee filter. |
| `:ClaimID` | Input | Decimal | N/A | Single claim filter. |
| `:CLM1..:CLMN` | Input | Decimal | N/A | Claim-list filters. |
| `:PolicyNum` | Input | Decimal | N/A | Single policy filter. |
| `:POL1..:POLN` | Input | Decimal | N/A | Policy-list filters. |
| `:ACT1..:ACTN` | Input | String | 5 | Action-code filters. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [TASK](../database-tables.md#task) | SELECT | CLAIM_ID, CONTEXT_*, CREATE_DATE, CREATE_USER, DELETE_FLAG, IMAGE_SEQ_NUM, NOTEPAD_FLAG, POLICY_NUM, TASK_USER, TASK_ACTION_CODE, TASK_CATEGORY_CODE, TASK_DATE, TASK_SECURITY, TASK_PRIORITY_CODE, TASK_SEQ_NUM, TASK_SOURCE_CODE, TASK_TEXT, UPDATE_DATE, UPDATE_SEQ_NUM, UPDATE_USER |

## Sample SQL

```sql
SELECT TASK.CLAIM_ID AS ClaimID
     , TASK.CONTEXT_CATEGORY AS ContextCategory
     , TASK.CONTEXT_CODE AS ContextCode
     , TASK.CONTEXT_DATE AS ContextDate
     , TASK.CONTEXT_KEY AS ContextKey
     , TASK.CONTEXT_NUM AS ContextNum
     , TASK.CONTEXT_TEXT AS ContextText
     , TASK.CREATE_DATE AS CreateDate
     , TASK.CREATE_USER AS CreateUser
     , TASK.DELETE_FLAG AS DeleteFlag
     , TASK.IMAGE_SEQ_NUM AS ImageSeqNum
     , TASK.NOTEPAD_FLAG AS NotepadFlag
     , TASK.POLICY_NUM AS PolicyNum
     , TASK.TASK_USER AS MailID
     , TASK.TASK_ACTION_CODE AS TaskActionCode
     , TASK.TASK_CATEGORY_CODE AS TaskCategoryCode
     , TASK.TASK_DATE AS TaskDate
     , TASK.TASK_SECURITY AS TaskSecurity
     , TASK.TASK_PRIORITY_CODE AS TaskPriorityCode
     , TASK.TASK_SEQ_NUM AS TaskSeqNum
     , TASK.TASK_SOURCE_CODE AS TaskSourceCode
     , TASK.TASK_TEXT AS TaskText
     , TASK.UPDATE_DATE AS UpdateDate
     , TASK.UPDATE_SEQ_NUM AS UpdateSeqNum
     , TASK.UPDATE_USER AS UpdateUser
FROM TASK
```

### Conditional append logic

```vbnet
m_SQL.Append(" AND DELETE_FLAG = ").Append(clsConstants.N).Append(" ")
m_SQL.Append(" AND TASK_TEXT LIKE %").Append(TaskText).Append("% ")
m_SQL.Append(" AND TASK_SOURCE_CODE = :TaskSource")
m_SQL.Append(" AND TASK_SOURCE_CODE IN (")
m_SQL.Append(" AND TASK_CATEGORY_CODE = :CAT1")
m_SQL.Append(" AND TASK_CATEGORY_CODE IN (")
m_SQL.Append(" AND TASK_PRIORITY_CODE = :PriorityCode")
m_SQL.Append(" AND TASK_PRIORITY_CODE IN (")
m_SQL.Append(" AND trunc(TASK_DATE) >= :BeginDate")
m_SQL.Append(" AND trunc(TASK_DATE) <= :EndDate")
m_SQL.Append(" AND TASK_USER = :MailID")
m_SQL.Append(" AND CLAIM_ID = :ClaimID")
m_SQL.Append(" AND CLAIM_ID IN (")
m_SQL.Append(" AND POLICY_NUM = :PolicyNum")
m_SQL.Append(" AND POLICY_NUM IN (")
m_SQL.Append(" AND TASK_ACTION_CODE = :ACT1 ")
m_SQL.Append(" AND TASK_ACTION_CODE IN (")
m_SQL.Append(" AND CONTEXT_CODE = ").Append(ContextCode).Append(" ")
m_SQL.Append(" AND CONTEXT_KEY = ").Append(ContextKey).Append(" ")
m_SQL.Append(" AND CONTEXT_CATEGORY = ").Append(ContextCategory).Append(" ")
m_SQL.Append(" AND CONTEXT_TEXT = ").Append(ContextTextValue).Append(" ")
m_SQL.Append(" AND CONTEXT_DATE = ").Append(ContextDate).Append(" ")
m_SQL.Append(" AND CONTEXT_NUM = ").Append(ContextNumericValue).Append(" ")
```

## Operations and processes
- [Retrieve task collection](../operations/task-collection-retrieval.md)

> **Note:** Statements marked with `*` are inferred from code behavior rather than explicit documentation or comments.

## Sources
- `Business\SAIF.Business.Tasks\clsTaskCollection.vb`