Link segment by loop and parent loop ID in SQL Server ?

[inteligenz]

Link segment by loop and parent loop ID in SQL Server ?

[lizouqodex]

I think I answered my own questions, and please correct me if I am wrong.

I found out there is a example view in DBCreation.cs.
The loop left join to other tables looks correct based on X12 file structure.

    public void CreateEntityView(string commonSchema)
    {
        ExecuteCmd(string.Format(@"

CREATE VIEW [{0}].[Entity]
AS
select l.Id as EntityId, l.EntityIdentifierCode, eic.Definition as EntityIdentifier
, l.InterchangeId, l.TransactionSetId, l.TransactionSetCode, l.ParentLoopId, l.SpecLoopId, l.StartingSegmentId
, Name = isnull(n1.[02], case nm1.[02] when '2' then nm1.[03] when '1' then nm1.[03] + ', ' + nm1.[04] + isnull(' ' + nm1.[05],'') end)
, IsPerson = case nm1.[02] when '1' then 1 else 0 end
, LastName = nm1.[03]
, FirstName = nm1.[04]
, MiddleName = nm1.[05]
, NamePrefix = nm1.[06]
, NameSuffix = nm1.[07]
, IdQualifier = isnull(n1.[03],nm1.[08])
, Identification = isnull(n1.[04],nm1.[09])
, Ssn = case when n1.[03] = '34' then n1.[04]
when nm1.[08] = '34' then nm1.[09]
else (select top 1 [02] from [{0}].REF where l.Id = ref.ParentLoopId and [01] = 'SY') end
, Npi = case when n1.[03] = 'XX' then n1.[04]
when nm1.[08] = 'XX' then nm1.[09]
else (select top 1 [02] from [{0}].REF where l.Id = ref.ParentLoopId and [01] = 'HPI') end
, TelephoneNumber = coalesce((select top 1 [04] from [{0}].PER where per.ParentLoopId = l.Id and per.[03]='TE')
,(select top 1 [06] from [{0}].PER where per.ParentLoopId = l.Id and per.[05]='TE')
,(select top 1 [08] from [{0}].PER where per.ParentLoopId = l.Id and per.[07]='TE'))
, AddressLine1 = n3.[01]
, AddressLine2 = n3.[02]
, City = n4.[01]
, StateCode = n4.[02]
, PostalCode = n4.[03]
, County = case n4.[05] when 'CY' then n4.[06] else null end
, CountryCode = n4.[07]
, DateOfBirth = dmg.[02]
, Gender = dmg.[03]
from [{0}].[Loop] l
left join [{1}].X12CodeList eic on l.EntityIdentifierCode = eic.Code and eic.ElementId = '98'
left join [{0}].[N1] on l.Id = n1.LoopId
left join [{0}].[NM1] on l.Id = nm1.LoopId
left join [{0}].N3 on l.Id = n3.ParentLoopId
left join [{0}].N4 on l.Id = n4.ParentLoopId
left join [{0}].[DMG] on l.Id = dmg.ParentLoopId
where l.StartingSegmentId in ('N1','NM1','ENT','NX1','PT','IN1','NX1')

[TerriblyVexed]

@lizouqodex I know this is a closed issue, but did you prove out that your approach works? It looks like it works and, if so, I'd like to use your queries in my implementation of the parser. Specifically I'm interested in the recursive CTE.