SQL-262: Retrieve Policy

This statement reads related POLICY rows for a given policy — either policies that share its relationship-policy chain (GetRelationshipPolicies) or policies that share its experience-rating account (GetExperienceRatingPolicies). Each variant includes a correlated scalar subquery that re-reads the source policy's own relationship/rate-account link. It is issued via NHibernate LINQ from Services/SAIF.Services/Policies/PolicyAssociation/PolicyAssociationService.cs.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:policyNumber	Input	long	N/A	Policy number, bound twice per statement (outer predicate and correlated subquery)

Tables

Table	Access	Columns used
POLICY	SELECT (outer) + SELECT (scalar subquery, alias P2)	Outer: POLICY_NUM, POLICY_NAME, LEGAL_NAME, SECURE_POLICY_CODE, OFFICE_CODE, ACCOUNT_NUM, POLICY_STATUS_CODE, MARKET_TYPE_CODE, POLICY_RELATIONSHIP_POLICY_NUM, POLICY_RELATIONSHIP_CODE, customer_service_team_code, ENTITY_CODE, TRADING_SEQ_NUM, RATE_ACCOUNT, AGENT_NUM; subquery: POLICY_NUM, POLICY_STATUS_CODE, POLICY_RELATIONSHIP_POLICY_NUM / RATE_ACCOUNT

LINQ statement

Source: Services/SAIF.Services/Policies/PolicyAssociation/PolicyAssociationService.cs:225

// Services/SAIF.Services/Policies/PolicyAssociation/PolicyAssociationService.cs:225-234
var relationshipPolicies = unitOfWork.GetRepository<Policy>()
    .Query()
    .Where(x => (x.RelationshipPolicy != null && x.RelationshipPolicy.PolicyNumber == policyNumber && x.Status.Code != "PC")
             || (x.RelationshipPolicy != null && x.Status.Code != "PC" && x.RelationshipPolicy.PolicyNumber ==
                 unitOfWork.GetRepository<Policy>()
                     .Query()
                     .Where(p => (p.PolicyNumber == policyNumber && p.Status.Code != "PC"))
                     .Select(p => p.RelationshipPolicy.PolicyNumber)
                     .FirstOrDefault())
    ).ToList();

// Services/SAIF.Services/Policies/PolicyAssociation/PolicyAssociationService.cs:242-251
var experienceRatingPolicies = unitOfWork.GetRepository<Policy>()
    .Query()
    .Where(x => (x.RateAccount != null && x.RateAccount.PolicyNumber == policyNumber && x.Status.Code != "PC") ||
                (x.RateAccount != null && x.Status.Code != "PC" && x.RateAccount ==
                 unitOfWork.GetRepository<Policy>()
                     .Query()
                     .Where(p => (p.PolicyNumber == policyNumber && p.Status.Code != "PC"))
                     .Select(p => p.RateAccount)
                     .FirstOrDefault())
    ).ToList();

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
-- GetRelationshipPolicies (L225; nested subquery L229)
SELECT
  P.POLICY_NUM,
  P.POLICY_NAME,
  P.LEGAL_NAME,
  P.SECURE_POLICY_CODE,
  P.OFFICE_CODE,
  P.ACCOUNT_NUM,
  P.POLICY_STATUS_CODE,
  P.MARKET_TYPE_CODE,
  P.POLICY_RELATIONSHIP_POLICY_NUM,
  P.POLICY_RELATIONSHIP_CODE,
  P.customer_service_team_code,
  P.ENTITY_CODE,
  P.TRADING_SEQ_NUM,
  P.RATE_ACCOUNT,
  P.AGENT_NUM
FROM POLICY P
WHERE (P.POLICY_RELATIONSHIP_POLICY_NUM IS NOT NULL
    AND P.POLICY_RELATIONSHIP_POLICY_NUM = :policyNumber
    AND P.POLICY_STATUS_CODE <> 'PC')
  OR (P.POLICY_RELATIONSHIP_POLICY_NUM IS NOT NULL
    AND P.POLICY_STATUS_CODE <> 'PC'
    AND P.POLICY_RELATIONSHIP_POLICY_NUM = (
      SELECT *
      FROM (
        SELECT P2.POLICY_RELATIONSHIP_POLICY_NUM
        FROM POLICY P2
        WHERE P2.POLICY_NUM = :policyNumber
          AND P2.POLICY_STATUS_CODE <> 'PC'
      )
      WHERE ROWNUM <= 1
    ))

-- GetExperienceRatingPolicies (L242; nested subquery L246)
SELECT
  P.POLICY_NUM,
  P.POLICY_NAME,
  P.LEGAL_NAME,
  P.SECURE_POLICY_CODE,
  P.OFFICE_CODE,
  P.ACCOUNT_NUM,
  P.POLICY_STATUS_CODE,
  P.MARKET_TYPE_CODE,
  P.POLICY_RELATIONSHIP_POLICY_NUM,
  P.POLICY_RELATIONSHIP_CODE,
  P.customer_service_team_code,
  P.ENTITY_CODE,
  P.TRADING_SEQ_NUM,
  P.RATE_ACCOUNT,
  P.AGENT_NUM
FROM POLICY P
WHERE (P.RATE_ACCOUNT IS NOT NULL
    AND P.RATE_ACCOUNT = :policyNumber
    AND P.POLICY_STATUS_CODE <> 'PC')
  OR (P.RATE_ACCOUNT IS NOT NULL
    AND P.POLICY_STATUS_CODE <> 'PC'
    AND P.RATE_ACCOUNT = (
      SELECT *
      FROM (
        SELECT P2.RATE_ACCOUNT
        FROM POLICY P2
        WHERE P2.POLICY_NUM = :policyNumber
          AND P2.POLICY_STATUS_CODE <> 'PC'
      )
      WHERE ROWNUM <= 1
    ))

Translation notes: - The nested ...Select(...).FirstOrDefault() inside the predicate references no outer lambda parameter, but it is a queryable expression, so NHibernate translates it as a scalar subquery (not client-evaluated). Rendered with the Oracle10 ROWNUM row limit; exact subquery shape is inferred. - .ToList() is the materialization point; Mapper.Map runs in memory. - OFFICE_CODE is mapped both as Map(OfficeCode) (PolicyMap.cs:18) and References(Office) (PolicyMap.cs:26); it is selected once. All other References contribute their FK column only; HasMany collections (Periods, DbaNames, OscPolicies, SubmissionPolicies, TeamFunctions, BusinessAffiliates) are lazy and not selected.

Operations and processes

• PolicyAssociationService.GetExperienceRatingPolicies — see Operations and processes
• PolicyAssociationService.GetRelationshipPolicies — see Operations and processes

Sources

• Services/SAIF.Services/Policies/PolicyAssociation/PolicyAssociationService.cs (L225, L229, L242, L246)