Building Subquery to be a column/Field Name

I am unable to bundled groups of subqueries correctly in order to create column titled “Discharge_To”

I am using Teradata Studio Express. I was asked to create a column for field that is not inside a table we normally used. We want to know where a patient was discharged to from a previous place of service. In order to answer this, there has to be steps to determine that. So far, I can get it read correctly until line 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

There should be 7 columns with the 7th column titled “Discharge_to” and values in the seventh column would be text (e.g., “Home without Care”)

Answer

Posting here, since it’s easier. Your query doesn’t seem to be formatted correctly. It’s of this form:

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

A couple notes:

• There is no FROM clause in the outer query yet you are trying to return S. fields
• There is no S result set to join your E result to
• The E result set is trying to be used as a sub-SELECT, but it also has an alias

Not knowing what your error message is, I’d suggest breaking apart the query into its sub-queries and running those individually to try to determine where the problem lies.