Skip to content

SQL-058: ClaimQueryProvider.GetClaimStatusDate

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by ClaimQueryProvider.GetClaimStatusDate.

Classification

Field Value
SQL ID SQL-058
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle*
Schema [unqualified]**
Command type N/A (ORM-generated)
Business object ClaimQueryProvider
Criteria property N/A — ORM
Lifecycle method GetClaimStatusDate

Tables

Table Access Columns used
CL_CLAIM_STATUS_ACTIVITY SELECT (outer query and scalar subquery) CLAIM_ID, CLAIM_STATUS_CODE, CLAIM_STATUS_ACTIVITY_SEQ_NUM, UPDATE_NUM, EFF_DATE, UPDATE_USER, UPDATE_DATE

LINQ statement

Source: Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:182-186.

var table = Factory.GetRepository<ClaimStatusActivity>().Query();
return table.Single(x =>
        x.ClaimId == claimId &&
        x.SubId == (table.Where(y => y.ClaimId == claimId)
                                        .Max(z => z.SubId))).EffectiveDate.GetValueOrDefault();

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.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT
  CCSA.CLAIM_ID,
  CCSA.CLAIM_STATUS_CODE,
  CCSA.CLAIM_STATUS_ACTIVITY_SEQ_NUM,
  CCSA.UPDATE_NUM,
  CCSA.EFF_DATE,
  CCSA.UPDATE_USER,
  CCSA.UPDATE_DATE
FROM CL_CLAIM_STATUS_ACTIVITY CCSA
WHERE CCSA.CLAIM_ID = :claimId
  AND CCSA.CLAIM_STATUS_ACTIVITY_SEQ_NUM = (
    SELECT MAX(CCSA2.CLAIM_STATUS_ACTIVITY_SEQ_NUM)
    FROM CL_CLAIM_STATUS_ACTIVITY CCSA2
    WHERE CCSA2.CLAIM_ID = :claimId
  )
-- NHibernate expects at most one row

Translation notes: - The subquery on the same IQueryable (table) becomes a correlated-by-value scalar subquery bound to the same :claimId parameter. - Single throws when no row matches. It also throws if two rows share the max sequence number under different CLAIM_STATUS_CODE values, because the composite key is (CLAIM_ID, CLAIM_STATUS_CODE, CLAIM_STATUS_ACTIVITY_SEQ_NUM). - Only EFF_DATE is consumed by the caller; .GetValueOrDefault() runs in memory after materialization.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/Claims/ClaimQueryProvider.cs:182