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

# SQL-210: Retrieve InjuryClaim

This page covers three related `InjuryClaim` reads in `SecurityOverrideService`: `GetClaimData(string)` looks up a claim by claim number, `GetClaimData(long)` looks up a claim by identifier, and `IsValidClaim` probes for the existence of a claim by claim number (only run when the claim number is non-null). All three are issued through NHibernate LINQ against the `InjuryClaim` entity from `Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:175, 179, 183`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-210 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `SecurityOverrideService` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GetClaimData, IsValidClaim` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:claimNumber` | Input | string | N/A | (1) `GetClaimData(string)` |
| `:claimId` | Input | long | N/A | (2) `GetClaimData(long)` |
| `:claimNumUpper` | Input | string | N/A | (3) `claimNum.ToUpper()`, evaluated client-side because it does not reference `x` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CL_INJURY_CLAIM](../database-tables.md#cl_injury_claim) | SELECT | all 100 mapped columns (entity load) for (1)-(2); (3) probes existence only; filter on CLAIM_ID / SAIF_CLAIM_NUM |

## LINQ statement

Source: `Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:175`, `Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:179`, `Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:183`

```csharp
// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:175
return unitOfWork.GetRepository<InjuryClaim>().Query().FirstOrDefault(x => x.ClaimNumber == claimNumber);

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:179
return unitOfWork.GetRepository<InjuryClaim>().Query().FirstOrDefault(x => x.Id == claimId);

// Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs:183
return (claimNum != null
        && unitOfWork.GetRepository<InjuryClaim>().Query()
       .Any(x => x.ClaimNumber == claimNum.ToUpper()));
```

## Sample SQL

> **Inferred SQL:** This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

```sql
-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
-- (1) L175 — GetClaimData(string)
SELECT * FROM (
  SELECT IC.CLAIM_ID,
       IC.UPDATE_NUM,
       IC.SAIF_CLAIM_NUM,
       IC.SAIF_CLAIM_CHK_VALUE,
       IC.WCD_CLAIM_NUM,
       IC.OWCP_CLAIM_NUM,
       IC.UPDATE_DATE,
       IC.UPDATE_USER,
       IC.RECEIVED_DATE,
       IC.RECEIVED_801_DATE,
       IC.TIN_AUTHORIZED_USE_FLAG,
       IC.AGE_DOI_YEARS,
       IC.GED_FLAG_CODE,
       IC.EDUCATION_YEARS,
       IC.EMPR_RELATIVE_FLAG,
       IC.INTERESTED_PARTY_FLAG_CODE,
       IC.WORKER_DEPARTMENT_DESCR,
       IC.EMPR_PROVIDED_WORKER_AREA_TXT,
       IC.EMPR_PROV_WORKER_DEPT_DESCR,
       IC.EMPR_PROV_PAYROLL_CLASS_CODE,
       IC.EMPR_PROV_PAYROLL_SUB_CODE,
       IC.EMPR_PROVIDED_SHIFT_CODE,
       IC.SHIFT_START_TIME,
       IC.SHIFT_END_TIME,
       IC.SAIF_PROV_PAYROLL_CLASS_CODE,
       IC.SAIF_PROV_PAYROLL_SUB_CODE,
       IC.WORKER_TENURE_DAY,
       IC.WORKER_TENURE_MONTH,
       IC.WORKER_TENURE_YEAR,
       IC.INPATIENT_FLAG_CODE,
       IC.INITIAL_TREATMENT_DATE,
       IC.EMPLOYMENT_INJURY_CODE,
       IC.HANDICAP_WORKER_RESERVE_FLAG,
       IC.MULTIPLE_WORKER_FLAG_CODE,
       IC.NCE_DATA_FLAG,
       IC.KNOWLEDGE_DATE,
       IC.INMATE_NUM,
       IC.INMATE_SUPERVISOR_KNOW_DATE,
       IC.DISABILITY_DATE,
       IC.WORKER_HIRE_DATE,
       IC.LEAVE_WORK_TIME,
       IC.POLICY_SUB_CODE,
       IC.CDA_FLAG,
       IC.LAST_RESERVE_REVIEW_DATE,
       IC.LAST_RESERVE_REVIEW_USER,
       IC.LAST_VOC_RESERVE_REVIEW_DATE,
       IC.LAST_VOC_RESERVE_REVIEW_USER,
       IC.AWARD_AUTO_RECALCULATION_FL_CD,
       IC.WORKER_OCCUPATION_DESCR,
       IC.ACTIVE_CLAIM_FLAG,
       IC.INJURY_DATE,
       IC.INJURY_TIME,
       IC.HARDWARE_CAUSED_FLAG_CODE,
       IC.PERSON_CAUSED_FLAG_CODE,
       IC.CITY_NAME,
       IC.STATE_CODE,
       IC.ZIP_CODE,
       IC.ZIP_CODE_EXT,
       IC.HIGH_SEAS_FLAG,
       IC.SAIF_EVENT_DESCR,
       IC.HEALTH_INSURANCE_PROV_NAME,
       IC.HEALTH_INSURANCE_ID,
       IC.HEALTH_INSURANCE_GROUP_NUM,
       IC.MULTIPLE_EMPR_FLAG,
       IC.MULTIPLE_EMPR_WAGE_AMT,
       IC.LEAVE_WORK_DATE,
       IC.COUNTY_CODE,
       IC.FEDERAL_8F_FLAG,
       IC.CDA_ISSUE_DATE,
       IC.DCS_ISSUE_DATE,
       IC.MEDICARE_ORM_TERM_DATE,
       IC.PREFERRED_WORKER_ID,
       IC.OUTSTANDING_RESERVE_FLAG,
       IC.EXTERNAL_VIEW_FLAG,
       IC.FULLY_FRAUDULENT_FLAG,
       IC.DENIED_FINAL_FLAG,
       IC.POLICY_NUM,
       IC.CLAIM_RESP_CODE,
       IC.COMPENSABILITY_RESP_CODE,
       IC.FORM_CODE,
       IC.CLAIM_STATUS_CODE,
       IC.CLAIM_TYPE_CODE,
       IC.JURISDICTION_CODE,
       IC.PRIMARY_BODY_PART_CODE,
       IC.PRIMARY_BODY_SIDE_CODE,
       IC.PRIMARY_INJURY_NATURE_CODE,
       IC.COVERAGE_CODE,
       IC.DCS_FULL_PART_CODE,
       IC.FEDERAL_INJURY_LOCATION_CODE,
       IC.OWCP_SERVICE_OFFICE_CODE,
       IC.INJURED_ON_PREMISES_CODE,
       IC.INJURY_EVENT_CODE,
       IC.INJURY_SOURCE_CODE,
       IC.INJ_OCCUPATIONAL_DISEASE_CODE,
       IC.REQUEST_TYPE_CODE,
       IC.MCO_ELIGIBILITY_REASON_CODE,
       IC.MCO_ELIGIBILITY_STATUS_CODE,
       IC.VOCATIONAL_RESP_CODE,
       IC.WORKER_ID,
       IC.MCO_ID
  FROM   CL_INJURY_CLAIM IC
  WHERE  IC.SAIF_CLAIM_NUM = :claimNumber
) WHERE ROWNUM <= 1;

-- (2) L179 — GetClaimData(long)
SELECT * FROM (
  SELECT IC.<all 100 mapped CL_INJURY_CLAIM columns, as in (1)>
  FROM   CL_INJURY_CLAIM IC
  WHERE  IC.CLAIM_ID = :claimId
) WHERE ROWNUM <= 1;

-- (3) L184 — IsValidClaim (only when claimNum != null; short-circuit in C#)
SELECT * FROM (
  SELECT 1
  FROM   CL_INJURY_CLAIM IC
  WHERE  IC.SAIF_CLAIM_NUM = :claimNumUpper
) WHERE ROWNUM <= 1;
```

Translation notes:
- `FirstOrDefault` → Oracle10 dialect wraps the query in `SELECT * FROM (...) WHERE ROWNUM <= 1`.
- `Any(...)` → existence probe (`SELECT 1 ... WHERE ROWNUM <= 1`); returns bool.
- In (3), `claimNum != null &&` is a C# short-circuit: when it is null no SQL is issued.

## Operations and processes
- SecurityOverrideService.GetClaimData — see [Operations and processes](../operations-processes.md)
- SecurityOverrideService.IsValidClaim — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Claims/SecurityOverride/SecurityOverrideService.cs (L175, L179, L184)`