April 3, 2017 at 11:04 am
Folks
I got this off of a SSRS report. This works fine. However I can't seem to make sense from this.
I have never ever seen a JOIN where one "ON" follows another "ON" ( like below taken from the main query )
Anyhow, can one of you rearrange the SQL so it makes sense to the reader. ( Hey!--But it works as it is )
ON CLAIM.DClaim.ClaimTID = CLAIM.DClaimServiceLine.ClaimTID
ON DClaim_1.Membernbr = CLAIM.DClaim.Membernbr AND
SELECTCLAIM.DClaim.FormNbr, CLAIM.DClaim.AdmitDate, CLAIM.DClaim.Membernbr,
CLAIM.DClaim.TotalNetAmt, DClaim_1.TotalNetAmt AS TotalNetAmt2,
CLAIM.DClaimServiceLine.NetAmt, CLAIM.DClaimServiceLine.ProcedureCode,
CLAIM.DClaimServiceLine.ProviderNbr, CLAIM.DClaim.PlaceofService,
CLAIM.DClaim.ClaimType, DClaim_1.FormNbr AS FormNbr2, DClaimServiceLine_1.ProcedureCode AS ProcedureCode2,
DClaimServiceLine_1.ProviderNbr AS ProviderNbr2,
CLAIM.DClaim.PlanCode
FROM
CLAIM.DClaim AS DClaim_1 WITH (NOLOCK)
INNER JOIN
CLAIM.DClaimServiceLine AS DClaimServiceLine_1 WITH (NOLOCK) ON DClaimServiceLine_1.ClaimTID = DClaim_1.ClaimTID
INNER JOIN
CLAIM.DClaim WITH (NOLOCK)
INNER JOIN
CLAIM.DClaimServiceLine WITH (NOLOCK)
ON CLAIM.DClaim.ClaimTID = CLAIM.DClaimServiceLine.ClaimTID
ON DClaim_1.Membernbr = CLAIM.DClaim.Membernbr AND
DClaim_1.AdmitDate = CLAIM.DClaim.AdmitDate AND DClaim_1.FormNbr > CLAIM.DClaim.FormNbr AND
DClaimServiceLine_1.NetAmt = CLAIM.DClaimServiceLine.NetAmt AND DClaim_1.ClaimType = CLAIM.DClaim.ClaimType
WHERE
(CLAIM.DClaim.AdmitDate between @StartAdmitdate and @ENDAdmitDate )
AND (CLAIM.DClaim.TotalNetAmt > 0)
AND (DClaim_1.TotalNetAmt > 0) AND (CLAIM.DClaimServiceLine.NetAmt > 500)
AND
(CLAIM.DClaimServiceLine.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514'))
AND (DClaimServiceLine_1.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514'))
April 3, 2017 at 11:13 am
I'm not helping unless you format your code properly.
April 3, 2017 at 11:28 am
mw112009 - Monday, April 3, 2017 11:04 AMFolks
I got this off of a SSRS report. This works fine. However I can't seem to make sense from this.
I have never ever seen a JOIN where one "ON" follows another "ON" ( like below taken from the main query )
Anyhow, can one of you rearrange the SQL so it makes sense to the reader. ( Hey!--But it works as it is )
...
...
I believe your above SQL code sample got mangled in the process of capturing it. I tried to auto-format it using RedGate SQL Prompt, but it failed with complaints about syntax.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 3, 2017 at 11:36 am
This is the best I could do ... I used online http://www.dpriver.com/pp/sqlformat.htm and it gave the following code...
But.. the code works...
SELECTCLAIM.dclaim.formnbr, claim.dclaim.admitdate, claim.dclaim.membernbr, claim.dclaim.totalnetamt, dclaim_1.totalnetamt AS totalnetamt2, claim.dclaimserviceline.netamt, claim.dclaimserviceline.procedurecode, claim.dclaimserviceline.providernbr, claim.dclaim.placeofservice, claim.dclaim.claimtype, dclaim_1.formnbr AS formnbr2, dclaimserviceline_1.procedurecode AS procedurecode2, dclaimserviceline_1.providernbr AS providernbr2, claim.dclaim.plancode FROM claim.dclaim AS dclaim_1 WITH (nolock) INNER JOIN claim.dclaimserviceline AS dclaimserviceline_1 WITH (nolock) ON dclaimserviceline_1.claimtid = dclaim_1.claimtid INNER JOIN claim.dclaim WITH (nolock) INNER JOIN claim.dclaimserviceline WITH (nolock) ON claim.dclaim.claimtid = claim.dclaimserviceline.claimtid ON dclaim_1.membernbr = claim.dclaim.membernbr
AND
dclaim_1.admitdate = claim.dclaim.admitdate
AND
dclaim_1.formnbr > claim.dclaim.formnbr
AND
dclaimserviceline_1.netamt = claim.dclaimserviceline.netamt
AND
dclaim_1.claimtype = claim.dclaim.claimtype WHERE (
claim.dclaim.admitdate BETWEEN @StartAdmitdate
AND
@ENDAdmitDate
)
AND
(
claim.dclaim.totalnetamt > 0
)
AND
(
dclaim_1.totalnetamt > 0
)
AND
(
claim.dclaimserviceline.netamt > 500
)
AND
(
claim.dclaimserviceline.procedurecode IN ('59409',
'59514',
'59612',
'59620',
'57514')
)
AND
(
dclaimserviceline_1.procedurecode IN ('59409',
'59514',
'59612',
'59620',
'57514')
)
April 3, 2017 at 11:41 am
Or let me make everyones life easier a bit...
I ve never seen a SQl statement before that has a INNER JOIN a table/view name and then again an "INNER JOIN" followed by .....
if you can write an example ( I am not sure even whether that syntax is valid )
Hey! .. but it works ( That is the big surprise here )
I am trying my best to understand what kind of a join this is ....
FROM
CLAIM.DClaim AS DClaim_1 WITH (NOLOCK)
INNER JOIN
CLAIM.DClaimServiceLine AS DClaimServiceLine_1 WITH (NOLOCK) ON DClaimServiceLine_1.ClaimTID = DClaim_1.ClaimTID
INNER JOIN
CLAIM.DClaim WITH (NOLOCK)
INNER JOIN
CLAIM.DClaimServiceLine WITH (NOLOCK)
ON CLAIM.DClaim.ClaimTID = CLAIM.DClaimServiceLine.ClaimTID
April 3, 2017 at 11:46 am
mw112009 - Monday, April 3, 2017 11:41 AMOr let me make everyones life easier a bit...
I ve never seen a SQl statement before that has a INNER JOIN a table/view name and then again an "INNER JOIN" followed by .....if you can write an example ( I am not sure even whether that syntax is valid )
Hey! .. but it works ( That is the big surprise here )I am trying my best to understand what kind of a join this is ....
FROM
CLAIM.DClaim AS DClaim_1 WITH (NOLOCK)
INNER JOIN
CLAIM.DClaimServiceLine AS DClaimServiceLine_1 WITH (NOLOCK) ON DClaimServiceLine_1.ClaimTID = DClaim_1.ClaimTID
INNER JOIN
CLAIM.DClaim WITH (NOLOCK)
INNER JOIN
CLAIM.DClaimServiceLine WITH (NOLOCK)
ON CLAIM.DClaim.ClaimTID = CLAIM.DClaimServiceLine.ClaimTID
Don't change the order or sequence of those ON clauses.
It's a parent LEFT JOIN child INNER JOIN grandchild relationship.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 3, 2017 at 11:51 am
For my own sanity, and probably others:SELECT CLAIM.DClaim.FormNbr, CLAIM.DClaim.AdmitDate, CLAIM.DClaim.Membernbr,
CLAIM.DClaim.TotalNetAmt, DClaim_1.TotalNetAmt AS TotalNetAmt2,
CLAIM.DClaimServiceLine.NetAmt, CLAIM.DClaimServiceLine.ProcedureCode,
CLAIM.DClaimServiceLine.ProviderNbr, CLAIM.DClaim.PlaceofService,
CLAIM.DClaim.ClaimType, DClaim_1.FormNbr AS FormNbr2, DClaimServiceLine_1.ProcedureCode AS ProcedureCode2,
DClaimServiceLine_1.ProviderNbr AS ProviderNbr2,
CLAIM.DClaim.PlanCode
FROM CLAIM.DClaim AS DClaim_1 WITH (NOLOCK)
INNER JOIN CLAIM.DClaimServiceLine AS DClaimServiceLine_1 WITH (NOLOCK) ON DClaimServiceLine_1.ClaimTID = DClaim_1.ClaimTID
INNER JOIN CLAIM.DClaim WITH (NOLOCK)
INNER JOIN CLAIM.DClaimServiceLine WITH (NOLOCK) ON CLAIM.DClaim.ClaimTID = CLAIM.DClaimServiceLine.ClaimTID
ON DClaim_1.Membernbr = CLAIM.DClaim.Membernbr
AND DClaim_1.AdmitDate = CLAIM.DClaim.AdmitDate
AND DClaim_1.FormNbr > CLAIM.DClaim.FormNbr
AND DClaimServiceLine_1.NetAmt = CLAIM.DClaimServiceLine.NetAmt
AND DClaim_1.ClaimType = CLAIM.DClaim.ClaimType
WHERE CLAIM.DClaim.AdmitDate between @StartAdmitdate and @ENDAdmitDate
AND CLAIM.DClaim.TotalNetAmt > 0
AND DClaim_1.TotalNetAmt > 0
AND CLAIM.DClaimServiceLine.NetAmt > 500
AND CLAIM.DClaimServiceLine.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514')
AND DClaimServiceLine_1.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514');
I didn't even know that nested ON's were a thing, so I can't answer the question on why this works (I tested and can see it does), but I'mnot going to pretend I understand the syntax.
I would, however, strongly recommend learning to format your SQL in a readable format. 🙂
Edit and PS: I'm sure you've been asked this before, but why NOLOCK everywhere?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 3, 2017 at 11:54 am
Thom A - Monday, April 3, 2017 11:51 AMFor my own sanity, and probably others:SELECT CLAIM.DClaim.FormNbr, CLAIM.DClaim.AdmitDate, CLAIM.DClaim.Membernbr,
CLAIM.DClaim.TotalNetAmt, DClaim_1.TotalNetAmt AS TotalNetAmt2,
CLAIM.DClaimServiceLine.NetAmt, CLAIM.DClaimServiceLine.ProcedureCode,
CLAIM.DClaimServiceLine.ProviderNbr, CLAIM.DClaim.PlaceofService,
CLAIM.DClaim.ClaimType, DClaim_1.FormNbr AS FormNbr2, DClaimServiceLine_1.ProcedureCode AS ProcedureCode2,
DClaimServiceLine_1.ProviderNbr AS ProviderNbr2,
CLAIM.DClaim.PlanCode
FROM CLAIM.DClaim AS DClaim_1 WITH (NOLOCK)
INNER JOIN CLAIM.DClaimServiceLine AS DClaimServiceLine_1 WITH (NOLOCK) ON DClaimServiceLine_1.ClaimTID = DClaim_1.ClaimTID
INNER JOIN CLAIM.DClaim WITH (NOLOCK)
INNER JOIN CLAIM.DClaimServiceLine WITH (NOLOCK) ON CLAIM.DClaim.ClaimTID = CLAIM.DClaimServiceLine.ClaimTID
ON DClaim_1.Membernbr = CLAIM.DClaim.Membernbr
AND DClaim_1.AdmitDate = CLAIM.DClaim.AdmitDate
AND DClaim_1.FormNbr > CLAIM.DClaim.FormNbr
AND DClaimServiceLine_1.NetAmt = CLAIM.DClaimServiceLine.NetAmt
AND DClaim_1.ClaimType = CLAIM.DClaim.ClaimType
WHERE CLAIM.DClaim.AdmitDate between @StartAdmitdate and @ENDAdmitDate
AND CLAIM.DClaim.TotalNetAmt > 0
AND DClaim_1.TotalNetAmt > 0
AND CLAIM.DClaimServiceLine.NetAmt > 500
AND CLAIM.DClaimServiceLine.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514')
AND DClaimServiceLine_1.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514');
I didn't even know that nested ON's were a thing, so I can't answer the question on why this works (I tested and can see it does), but I'mnot going to pretend I understand the syntax.I would, however, strongly recommend learning to format your SQL in a readable format. 🙂
It's next to impossible to capture and paste a link using Android. Try googling:
Site:qa.sqlservercentral.com on clause matters chrism
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 3, 2017 at 11:59 am
ChrisM@home - Monday, April 3, 2017 11:46 AMDon't change the order or sequence of those ON clauses.
It's a parent LEFT JOIN child INNER JOIN grandchild relationship.
There are no outer joins in the posted query.
April 3, 2017 at 12:00 pm
ChrisM@home - Monday, April 3, 2017 11:54 AMIt's next to impossible to capture and paste a link using Android. Try googling:
Site:qa.sqlservercentral.com on clause matters chrism
Can't say I've ever had any problems on my Xperia or Pixel.
Believe this is the link you wanted though: https://qa.sqlservercentral.com/Forums/FindPost1606135.aspx
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 3, 2017 at 12:02 pm
ChrisM@home - Monday, April 3, 2017 11:54 AMIt's next to impossible to capture and paste a link using Android. Try googling:
Site:qa.sqlservercentral.com on clause matters chrism
Here's the link https://qa.sqlservercentral.com/Forums/FindPost1606135.aspx
April 3, 2017 at 12:03 pm
Folks
I am not interested in you wasting time on this. It works it works... But I have no clue what those joins are doing...
I guess I need some education on inner joins ( The next level )
I did see one person commenting that the syntax was correct and it represented a child, grandchild relationship. COOL!
Can that person just give us a example using some table in the Adventureworks DB
April 3, 2017 at 12:05 pm
mw112009 - Monday, April 3, 2017 12:03 PMFolks
I am not interested in you wasting time on this. It works it works... But I have no clue what those joins are doing...
I guess I need some education on inner joins ( The next level )I did see one person commenting that the syntax was correct and it represented a child, grandchild relationship. COOL!
Can that person just give us a example using some table in the Adventureworks DB
Chris explains it very well in the link both myself and Luis have posted above.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 3, 2017 at 12:21 pm
mw112009 - Monday, April 3, 2017 12:03 PMFolks
I am not interested in you wasting time on this. It works it works... But I have no clue what those joins are doing...
I guess I need some education on inner joins ( The next level )I did see one person commenting that the syntax was correct and it represented a child, grandchild relationship. COOL!
Can that person just give us a example using some table in the Adventureworks DB
If you don't want people to waste their time, you should learn to format your code to make it readable. As mentioned, Chris posted an example and we provided the link to it (twice). On OUTER JOINs the order will matter, but in this case it won't. You'll hardly see the need for this syntax and most (or all) of the time you can write it on a more comprehensive way.
Here's your query with proper format.
SELECT C2.FormNbr,
C2.AdmitDate,
C2.Membernbr,
C2.TotalNetAmt,
C1.TotalNetAmt AS TotalNetAmt2,
CSL2.NetAmt,
CSL2.ProcedureCode,
CSL2.ProviderNbr,
C2.PlaceofService,
C2.ClaimType,
C1.FormNbr AS FormNbr2,
CSL1.ProcedureCode AS ProcedureCode2,
CSL1.ProviderNbr AS ProviderNbr2,
C2.PlanCode
FROM CLAIM.DClaim AS C1
JOIN CLAIM.DClaimServiceLine AS CSL1 ON C1.ClaimTID = CSL1.ClaimTID
JOIN CLAIM.DClaim AS C2 ON C1.Membernbr = C2.Membernbr
AND C1.AdmitDate = C2.AdmitDate
AND C1.FormNbr > C2.FormNbr
AND C1.ClaimType = C2.ClaimType
JOIN CLAIM.DClaimServiceLine AS CSL2 ON C2.ClaimTID = CSL2.ClaimTID
AND CSL1.NetAmt = CSL2.NetAmt
WHERE C2.AdmitDate BETWEEN @StartAdmitdate AND @ENDAdmitDate
AND C2.TotalNetAmt > 0
AND C1.TotalNetAmt > 0
AND CSL2.NetAmt > 500
AND CSL2.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514')
AND CSL1.ProcedureCode IN ('59409', '59514', '59612', '59620', '57514');
April 3, 2017 at 12:45 pm
Thom A - Monday, April 3, 2017 12:00 PMChrisM@home - Monday, April 3, 2017 11:54 AMIt's next to impossible to capture and paste a link using Android. Try googling:
Site:qa.sqlservercentral.com on clause matters chrismCan't say I've ever had any problems on my Xperia or Pixel.
Believe this is the link you wanted though: https://qa.sqlservercentral.com/Forums/FindPost1606135.aspx
Thom you sound just like my mum 😀
Try pasting a link into a quoted window, the revealed text is something to do with an object reference. Or it is, on a Samsung phone.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply