---
title: "SQL-214: Retrieve Investigation"
summary: Read operation against Investigation via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-214: Retrieve Investigation

This statement runs hand-written native SQL — not LINQ — through `Session.CreateSqlQuery` in `InvestigationRepositoryExtensions`, which the `Investigation` repository exposes as `GetSubjectName` and `GetClaimId` extension methods. `FraudInvestigationService.GetSubjectNameAsync` calls `GetSubjectName(caseNumber)` to resolve the worker/company name, worker ID, and policy name for an investigation subject, and `FraudInvestigationService.GetClaimId` calls `GetClaimId(caseNumber)` to fetch the claim ID for an investigation. The SQL text is defined at `Data/SAIF.Data.NHibernate/Repositories/Investigations/InvestigationRepositoryExtensions.cs:10-25, 49-54`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-214 |
| **Operation type** | Read |
| **Execution method** | Inline SQL (NHibernate CreateSqlQuery) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `FraudInvestigationService` |
| **Criteria property** | N/A |
| **Lifecycle method** | `GetClaimId, GetSubjectNameAsync` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:investigationId` | Input | long | N/A | `caseNumber`, bound via `SetParameter<long>` (L26 / L55) |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [INVESTIGATION](../database-tables.md#investigation) | SELECT | SUBJECT_SEQ_NUM, PROVIDER_LOCATION_SEQ_NUM, POLICY_NUM, INVESTIGATION_SEQ_NUM (1); CLAIM_ID, INVESTIGATION_SEQ_NUM (2) |
| [SUBJECT](../database-tables.md#subject) | JOIN | SUBJECT_SEQ_NUM, PERSON_SEQ_NUM, COMPANY_SEQ_NUM, WORKER_ID |
| [PERSON](../database-tables.md#person) | JOIN | PERSON_SEQ_NUM, NAME |
| [COMPANY](../database-tables.md#company) | JOIN | COMPANY_SEQ_NUM, COMPANY_NAME |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | JOIN | PROVIDER_LOCATION_SEQ_NUM, BUSINESS_NAME |
| [POLICY](../database-tables.md#policy) | JOIN | POLICY_NUM, POLICY_NAME |

## Sample SQL

```sql
-- (1) InvestigationRepositoryExtensions.GetSubjectName — L10-25
SELECT
  nvl(per.NAME,
    nvl(com.company_name,
      nvl(loc.business_name, ''))) as worker_name,
  sub.worker_id,
  pol.policy_name
FROM investigation inv
JOIN subject sub ON inv.subject_seq_num = sub.subject_seq_num
LEFT OUTER JOIN person per ON per.person_seq_num = sub.person_seq_num
LEFT OUTER JOIN company com ON com.company_seq_num = sub.company_seq_num
LEFT OUTER JOIN provider_location loc ON loc.provider_location_seq_num = inv.provider_location_seq_num
LEFT OUTER JOIN policy pol ON pol.policy_num = inv.policy_num
WHERE investigation_seq_num = :investigationId

-- (2) InvestigationRepositoryExtensions.GetClaimId — L49-54
SELECT inv.claim_id
FROM investigation inv
WHERE investigation_seq_num = :investigationId
```

## Operations and processes
- FraudInvestigationService.GetClaimId — see [Operations and processes](../operations-processes.md)
- FraudInvestigationService.GetSubjectNameAsync — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Investigations/FraudInvestigationService.cs (L23, L45)`
- `Data/SAIF.Data.NHibernate/Repositories/Investigations/InvestigationRepositoryExtensions.cs (L10-25, L49-54)`