Giter Site home page Giter Site logo

Comments (2)

lizouqodex avatar lizouqodex commented on July 24, 2024 2

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')

from x12parser.

TerriblyVexed avatar TerriblyVexed commented on July 24, 2024

@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.

from x12parser.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.