SQL-158: LegalQueryProvider.GetAppealAssignedAttorney

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

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
caseId	Input	long	N/A	Method argument of LegalQueryProvider.GetAppealAssignedAttorney bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables

Table	Access	Columns used
APPEAL	SELECT	APPEAL_SEQ_NUM, ATTY_EMPLID, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, LEGAL_CASE_SEQ_NUM

LINQ statement

Source: Data/SAIF.Data/Queries/Legal/LegalQueryProvider.cs:82-86.

public String GetAppealAssignedAttorney(long caseId)
{
    var appeal = Factory.GetRepository<Appeal>()
        .Query()
        .Where(x => x.LegalCase.Id == caseId)
        .OrderByDescending(x => x.Id)
        .FirstOrDefault();
    return appeal.IsNull() || appeal.AssignedAttorneyEmplid.IsNullOrWhiteSpace() ? string.Empty : appeal.AssignedAttorneyEmplid;
}

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 * FROM (
  SELECT
    A.APPEAL_SEQ_NUM,
    A.ATTY_EMPLID,
    A.CREATE_USER,
    A.CREATE_DATE,
    A.UPDATE_USER,
    A.UPDATE_DATE,
    A.LEGAL_CASE_SEQ_NUM
  FROM APPEAL A
  WHERE A.LEGAL_CASE_SEQ_NUM = :caseId
  ORDER BY A.APPEAL_SEQ_NUM DESC
) WHERE ROWNUM <= 1

Translation notes: - FirstOrDefault() after OrderByDescending → Oracle10 ROWNUM <= 1 wrapper around the ordered inner query. - The whole Appeal entity is loaded. AssignedAttorneyEmplid (ATTY_EMPLID) is read in memory (line 87).

Operations and processes

• Legal case queries

Business rules

• SD-BR-060 Case-to-claim resolution precedence
• SD-BR-061 Preferred employee name takes precedence

Sources

• Data/SAIF.Data/Queries/Legal/LegalQueryProvider.cs:82