title: Building Subquery to be a column/Field Name
body: <p>I am unable to bundled groups of subqueries correctly in order to create column titled "Discharge_To"</p>
  
  <p>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.</p>
  
  <pre class="lang-sql prettyprint-override"><code>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 &lt;&gt; '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 &lt;&gt; '12'
        ) D On D.Member_ID = S.Member_ID And D.PAC_Sty_ID &lt;&gt; 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
  </code></pre>
  
  <p>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")</p>