加入2个子查询

问题描述

如果有任何帮助，我将不胜感激.我正在尝试对2个子查询执行连接，但它一直说'IntEncTracking.EncounterList.ClaimId'在选择列表中无效，因为它既不包含在聚合函数中也不在GROUP BY子句中，但是我已经尝试用el和q2进行排位赛，但无济于事.任何建议将不胜感激.

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.

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

推荐答案

我总是这样做

    ;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.

这篇关于加入2个子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！