时间戳

首页 > 解决方案 > 将子查询构建为列/字段名称

sql - 将子查询构建为列/字段名称

问题描述

我无法正确捆绑子查询组以创建标题为“Discharge_To”的列

我正在使用 Teradata Studio Express。我被要求为不在我们通常使用的表中的字段创建一个列。我们想知道患者从以前的服务地点出院到哪里。为了回答这个问题，必须有步骤来确定这一点。到目前为止，我可以在第 94 行之前正确读取它。

Select S.Member_ID, S.PAC_Sty_ID, S.Stay_Admit_Date, S.Stay_Discharge_Date, S.POS, S.LOS,
  (
    Select
      S.Member_ID, S.PAC_Sty_ID,
      Case
        When S.Discharge_To is null 
          and H.POS is not null And S.POS <> '12' then 'Home With Care'
        When S.Discharge_To is null then 'Home Without Care'
        Else S.Discharge_To 
      End Discharge_To
    From (
      Select
        S.Member_ID, S.PAC_Sty_ID, S.Stay_Admit_Date, S.Stay_Discharge_Date, S.POS,
        Case trim(D.POS)
          When '21' then 'Hospital' When '23' then 'ER' When '31' then 'SNF'
          When '61' then 'IRF' When 'LTAC' then 'LTAC'
        End Discharge_To
      From ECONIMICS.PAC_02_MODEL_SUMMARY_Combined S
      Left Join (
        Select S.Member_ID, S.PAC_Sty_ID, S.POS, S.Stay_Admit_Date, S.Stay_Discharge_Date
        From ECONIMICS.PAC_02_MODEL_SUMMARY_Combined S
        Where PAC_Sty_ID is not null
        And POS <> '12'
      ) D On D.Member_ID = S.Member_ID And D.PAC_Sty_ID <> S.PAC_Sty_ID
        And D.Stay_Admit_Date Between S.Stay_Admit_Date and S.Stay_Discharge_Date + 1
      Where S.PAC_Sty_ID is not null
      Qualify Row_Number() Over (
        Partition By S.PAC_Sty_ID Order By Case trim(D.POS) 
          When '21' then 1 When 'LTAC' then 2 when '61' then 3 When '31' then 4 end
      ) = 1
    ) S
    Left Join (
      Select *
      From ECONIMICS.PAC_02_MODEL_SUMMARY_Combined
      Where POS = '12'
    ) H On H.Member_ID = S.Member_ID
      And H.From_Date Between S.Stay_Discharge_Date and S.Stay_Discharge_Date + 7
    Qualify Row_Number() Over (Partition By S.PAC_Sty_ID Order By H.From_Date) = 1
) E On E.Member_ID = S.Member_ID And E.PAC_Sty_ID = S.PAC_Sty_ID
Where S.PAC_Sty_ID is not Null
AND S.STAY_DISCHARGE_DATE between '2017-01-01' and '2020-12-31'
AND S.LOB in ('CARE', 'DUAL')
AND S.ORPHAN_CLM_ID IS NULL
AND S.ORPHAN_CLM_LN IS NULL
Group By 1, 2, 3, 4, 5, 6

应该有 7 列，第 7 列标题为“Discharge_to”，第七列中的值将是文本（例如，“Home without Care”）

标签： sqlteradata

解决方案

在这里发帖，因为它更容易。您的查询格式似乎不正确。它是这样的形式：

select S.Member_ID, ... , 
  (
    Select ... -- Sub-query trying to derive Discharge_To field
  ) E on E.Member_ID = S.Member_ID ...
where ...

几点注意事项：

  • 外部查询中没有FROM子句，但您正在尝试返回S.字段
  • 没有S结果集可将您的E结果加入到
  • 结果E集试图用作子 SELECT，但它也有别名

不知道您的错误消息是什么，我建议将查询分解为其子查询并单独运行这些查询以确定问题所在。

推荐阅读