Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Join of 2 subqueries Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 7:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:30 PM
Points: 38, 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
Post #1474586
Posted Wednesday, July 17, 2013 7:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1474591
Posted Wednesday, July 17, 2013 7:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 2,449, Visits: 2,990
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’! **
Post #1474594
Posted Wednesday, July 17, 2013 7:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:30 PM
Points: 38, 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.
Post #1474597
Posted Wednesday, July 17, 2013 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:30 PM
Points: 38, 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... :-(
Post #1474599
Posted Wednesday, July 17, 2013 7:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1474602
Posted Wednesday, July 17, 2013 7:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:30 PM
Points: 38, 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.
Post #1474605
Posted Wednesday, July 17, 2013 7:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1474611
Posted Wednesday, July 17, 2013 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:30 PM
Points: 38, 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.
Post #1474613
Posted Wednesday, July 17, 2013 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1474626
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse