none
Join of subqueries

    Question

  • I would appreciate any assistance w/this. I'm trying to perform a join on 2 subqueries, but it keeps saying the 'IntEncTracking.EncounterList.ClaimId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause, but I've tried qualifying it with an el and q2, to no avail. Any advice would be greatly appreciated.

    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


    Wednesday, July 17, 2013 4:30 PM

All replies

  • Just guessing, 
    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
    group by ClaimId, EncounterType, InternalICN, PreviousDpwICN) 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       
         order by q2.ClaimId, q2.LineNum, q2.EncounterLineNum, q2.EncounterType, q2.InternalICN


    Satheesh


    Wednesday, July 17, 2013 4:41 PM
  • Look at your "q2" derived table:

    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

    You are missing the GROUP BY clause. It will be something similar to what Satheesh suggested but not using "q1" table alias to reference the columns.

    AMB

    Some guidelines for posting questions...

    Wednesday, July 17, 2013 4:50 PM
    Moderator
  • How are we supposed to figure out this mess without any DDL? Did you actually model physical line numbers from a  paper form in SQL??  Want to try again after you read the front of this forum for Basic Netiquette?  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 17, 2013 5:14 PM
  • Well, first stop using "*" as your column list.  Laziness is no excuse and is (or will be) contributing to your problem. In both derived tables (not subqueries), you have a column named ClaimId.  Since you used "*" as your column list, the resultset contains 2 ClaimID columns.  Do you really need both?  Hence, best practice is to fully define the resultset you want and not let random chance muck things up.

    Next, break your query down into the components and verify the correctness.  You have 2 derived tables.  Both of those defining queries can be run separately as individual select statements.  Do both compile and execute successfully?  Do they produce the correct resultsets?  That is a hint - because the answer is not "Yes".  Your second derived table query includes an aggregrate yet it has no group by clause.

    Lastly, try the script below to see another problem:

    Declare @RunType as varchar = 'Initial';
    select @RunType;

    Wednesday, July 17, 2013 6:28 PM
  • Hi All,

    Thanks for your assistance. It turns out I was taking the wrong approach with this query....so until next time... :)
    Thursday, July 18, 2013 1:37 PM