Join 2 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

You don't have a `from` clause. `join` is not like `union` and `union all`. It can only appear in a `from` clause. — Gordon Linoff, Jul 16 '13 at 19:38
Okay, so I've tried editing to the above, but still a no go...any suggestions, please? — daniness, Jul 16 '13 at 19:47
Thanks Gordon and everyone. It turns out I was taking the wrong approach with this query...until next time. :-) — daniness, Jul 18 '13 at 13:24

score 0 · Answer 1 · answered Jul 16 '13 at 19:43

0

this is just a syntex..

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

select *

from   (Select distinct ClaimId
     , LineNum
     , EncounterLineNum
     , 0
     , EncounterType
     , InternalICN
     , PreviousDpwICN
     , 0
     , EncounterPeriod
     , 2
     , BypassReason
     , null
     ,@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
  from IntEncTracking.EncounterList el
 where BypassFlag = 0) as q2


 on q1.ClaimId = q2.ClaimId and
 on q1.LineNum = q2.LineNum and
 on q1.EncounterLineNum = q2.EncounterLineNum and
 on q1.EncounterType = q2.EncounterType and
 on q1.InternalICN = q2.InternalICN 
 group by ClaimId, encountertype, internalicn, PreviousDpwICN
  order by ClaimId, LineNum, EncounterLineNum,EncounterType,InternalICN

answered Jul 16 '13 at 19:43

AJP

2,125
3
16
22

Thanks AJP, but I was getting more errors, so I had to make some more changes, but still having issues: – daniness Jul 16 '13 at 20:03
Msg 102, Level 15, State 1, Line 12 Incorrect syntax near '8'. Msg 156, Level 15, State 1, Line 41 Incorrect syntax near the keyword 'as'. – daniness Jul 16 '13 at 20:07
first remove space from this.. , 0 as col 8 and change it to , 0 as col8 then try again. – AJP Jul 16 '13 at 20:10
Okay did that, but now it's giving me: Msg 8120, Level 16, State 1, Line 23 Column 'IntEncTracking.EncounterList.ClaimId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 8155, Level 16, State 2, Line 43 No column name was specified for column 12 of 'q2'. – daniness Jul 16 '13 at 20:13
looks like you changed your query.. re-post the new query. – AJP Jul 16 '13 at 20:17
change your select to q1.ClaimId, q1.EncounterType, q1.InternalICN, q1.PreviousDpwICN. this will fix your problem.. than add whatyou need one by by in select. you should look at how group by works. google it. – AJP Jul 16 '13 at 20:22
Thanks AJP. It turns out I was taking the wrong approach with this query. – daniness Jul 18 '13 at 13:23

score 0 · Answer 2 · answered Jul 16 '13 at 19:54

I always do this sort of stuff like this

    ;with EncounterValues as(
    SELECT
    CommonValueOrId,
    ect..
    FROM
    IntEntTracking.EncountrList
    )

    , EncounterFileNums as(
    SELECT
    CommonValueOrId,
    ect..
    FROM
    IntEncTracking.EncounterList
    )

    SELECT
    ev.AnyField,
    efn.AnyField
    FROM
    EncounterValues ev
    LEFT JOIN EncounterFileNums efn
    ON ev.CommonValueOrId = evn.CommonValueOrId

Not sure what the implications of doing it this way is but might be worth giving it a go if you're struggling.

Thanks Scott Allen. It turns out I was taking the wrong approach with this query...so until next time then. :-) — daniness, Jul 18 '13 at 13:25

Join 2 subqueries

2 Answers2

Linked