SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Join of 2 subqueries


Join of 2 subqueries

Author
Message
daniness
daniness
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 173
Hi All,

I've been trying to do a join of 2 subqueries, but haven't been having much luck and keep getting errors. Can anyone please advise on what I'm doing wrong here? Thanks!

Declare @SweepId as int = 10160, @RunType as varchar = 'Initial'

Select *
from
(Select distinct ClaimId
, LineNum
, 0 as EncounterLineNum
, EncounterType
, InternalICN
, PreviousDpwICN
, 0 as ClaimFrequencyCd
, EncounterPeriod
, 2 as StatusCd
, BypassReason
, null as EncounterFileNm
,@SweepId as SweepId
from IntEncTracking.EncounterList
where bypassflag = 1) as q1

join

( Select ClaimId
, 0 as LineNum
, 0 as EncounterLineNum
, EncounterType
, InternalICN
, PreviousDpwICN
, max(ClaimFreqCd) as ClaimFreqCd
, max(EncounterPeriod) as EncounterPeriod
, case when exists (select 'x'
from IntEncTracking.EncounterList el1
where el1.claimid = claimid
and BypassFlag = 0) then 1
else 2
end stscd
, case when @RunType = 'Initial' then 100 else 300 end as [StatusReasonCd]
, null as EncounterFileNm
, @SweepId as SweepId
from IntEncTracking.EncounterList el
where BypassFlag = 0) as q2

on q1.ClaimId = q2.ClaimId and
q1.LineNum = q2.LineNum and
q1.EncounterLineNum = q2.EncounterLineNum and
q1.EncounterType = q2.EncounterType and
q1.InternalICN = q2.InternalICN

group by q1.ClaimId, q1.EncounterType, q1.InternalICN, q1.PreviousDpwICN
order by q2.ClaimId, q2.LineNum, q2.EncounterLineNum, q2.EncounterType, q2.InternalICN
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16091 Visits: 19534
You won't get the two queries to JOIN in the way that you are attempting to, unless each query works in isolation. The second query has aggregate functions but there's no GROUP BY, it's associated with the outer query. If all you want from the second query is those aggregate columns then there's almost certainly a better way of doing this. Can you give us some more detail?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4371 Visits: 3668
It's not really clear what you want, but I think you can put the GROUP BY statement inside the second sub-select (and remove the "q1" alias within this GROUP BY)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
daniness
daniness
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 173
Hi Chris,

Sure, I'd be happy to provide more detail. So the results of both queries are being inserted into the same table as part of a longer stored procedure, which errored out, saying that there cannot be a duplicate key inserted into it. So, this is why I'm trying to find out what the duplicate records are. Any suggestions would be appreciated. Thanks.
daniness
daniness
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 173
Hi Hanshi,

Actually, originally the group by was part of the 1st subquery, but it wasn't working, so this is why I tried placing it at the end, but obviously it's still not working... :-(
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16091 Visits: 19534
daniness (7/17/2013)
Hi Hanshi,

Actually, originally the group by was part of the 1st subquery, but it wasn't working, so this is why I tried placing it at the end, but obviously it's still not working... :-(


Don't you know what results you want from this query? Making a query work is trivial, but it's pointless if you can't tell if the result set is correct or not.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
daniness
daniness
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 173
Chris,

What I'm trying to get from this query are records that are in common to both subqueries. This would tell me which records are responsible for the duplicate error I mentioned earlier.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16091 Visits: 19534
daniness (7/17/2013)
Chris,

What I'm trying to get from this query are records that are in common to both subqueries. This would tell me which records are responsible for the duplicate error I mentioned earlier.


Which columns are included in the key which is responsible for generating the dupes error?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
daniness
daniness
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 173
The columns that are in the order by: ClaimId, LineNum, EncounterLineNum, EncounterType, and InternalICN. These make up the primary key that results of both queries are being inserted into in the stored procedure. Please let me know if I can further clarify. Thanks.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16091 Visits: 19534
daniness (7/17/2013)
The columns that are in the order by: ClaimId, LineNum, EncounterLineNum, EncounterType, and InternalICN. These make up the primary key that results of both queries are being inserted into in the stored procedure. Please let me know if I can further clarify. Thanks.


Like I said earlier, tweaking this query to get it through the PK error is a trivial matter - but isn't it a bit pointless if you don't know whether or not the output is actually correct?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search