title: Variable declarations with sub-queries and unions?
body: <p>I am trying to have a user run continuous code where I need dates and data sets changed from multiple sub queries and union steps. However the issue is that it only will run the first sub-query not any of the preceding unions or sub queries within the code.</p>
  
  <p>The code below is a simple version of the process. I can supply a more detailed version.</p>
  
  <p>Essentially
  I want several declarations so the user doesn't have to comb through the code to change dates and data-sets the code draws from. (Error Reduction.) Thanks. </p>
  
  <p>3 Unions with different sources. 
  I am trying to automate a lot of the variable attributes that need changing monthly, however the def page only works for the first set of select statements and doesn't allow me to have it attribute to all. </p>
  
  <p>Thanks. </p>
  
  <pre><code>def RecMonth    = 201401
  def BeginDate   = 1/01/2014
  def EndDate     = 1/31/2014
  def YearMonth   = 2014-01
  def BirthMonth  = 31-JAN-2014
  def OPData      = schema.X
  def IPData      = schema.Y
  
  
  
  
  CREATE TABLE UTIL_REPORT_2014 AS 
  
  
  (
  
  (SELECT
  &amp;RECMONTH RECORD_MONTH
  ,M.RECIPIENT_ID_1010
  ,M.GRP_RPT
  ,M.SUBGRP_RPT
  ,M.PROV_ENTITY_ID
  ,M.P_DAYS
  ,M.LOS_C
  ,M.LOS
  ,M.FFS_CLAIM
  ,M.MMC_CLAIM
  ,M.FFS_AMT
  ,M.MMC_AMT
  ,M.TOTAL_SUPPLY
  ,M.TOTAL_DISPENSED
  ,M.FLAG_ADM
  ,M.FLAG_DCH
  ,M.DRUG_NAME
  ,M.DRUG_CLASS
  ,M.INJECTIONS
  ,M.J_CODE
  ,M.RECIPIENT_RACE_CODE_1190
  ,M.REGION
  ,M.TYPE_ORD
  ,M.MANAGED_CARE
  ,M.CARE
  ,M.PCP_PROVIDER_ID_2001_3
  ,M.RP_2013_14
  ,M.RP_2013_14_OMH
  ,M.RP_2013_14_OASAS
  ,M.HOME_ENROLL
  ,M.RECIPIENT_CO
  ,M.COUNTY_LABEL
  ,M.LOCATION
  ,M.D_GROUP
  ,M.DATE_OF_BIRTH_RECIPIENT_1180 
  ,M.RECIPIENT_SEX_1210
  ,M.D_CATEGORY_ROLLUP_GROUP
  ,M.AGE
  ,M.PLAN_TYPE
  ,M.PLAN_NAME
  ,M.AGE_GROUP
  ,(Case when M.SUBGRP_RPT =  'X'
          OR M.SUBGRP_RPT = 'XX'
          OR M.SUBGRP_RPT = 'XXXXXX' then 1 
   END) as Serv_Group3
  
  ,(CASE WHEN M.SUBGRP_RPT LIKE ('XX%')    THEN 'XX'   
        WHEN M.SUBGRP_RPT LIKE ('XX%')   THEN 'XX' ELSE NULL END ) AS BH_Serv  )
  
  
  
  FROM(
  
  
  (Select 
  t1.RECIPIENT_ID_1010
  ,t1.GRP_RPT
  ,t1.SUBGRP_RPT
  ,t1.PROV_ENTITY_ID
  ,t1.P_DAYS
  ,NULL as LOS_C
  ,NULL as LOS
  ,t1.FFS_CLAIM
  ,t1.MMC_CLAIM
  ,t1.FFS_AMT
  ,t1.MMC_AMT
  ,0 AS TOTAL_SUPPLY
  ,0 AS TOTAL_DISPENSED
  ,null AS FLAG_ADM
  ,null AS FLAG_DCH
  ,NULL AS DRUG_NAME
  ,T1.DRUG_CLASS
  ,T1.INJECTIONS
  ,T1.J_CODE
  ,T2.RECIPIENT_RACE_CODE_1190
  ,T2.REGION
  ,t2.TYPE_ORD
  ,t2.MANAGED_CARE
  ,t2.CARE
  ,t2.PCP_PROVIDER_ID_2001_3
  ,t2.RP_2013_14
  ,t2.RP_2013_14_OMH
  ,t2.RP_2013_14_OASAS
  ,t2.HOME_ENROLL
  ,t2.RECIPIENT_CO
  ,t2.COUNTY_LABEL
  ,t2.LOCATION
  ,t2.D_GROUP
  ,t2.DATE_OF_BIRTH_RECIPIENT_1180 
  ,t2.RECIPIENT_SEX_1210
  ,t2.D_CATEGORY_ROLLUP_GROUP
  ,t2.AGE
  ,t2.PLAN_TYPE
  ,t2.PLAN_NAME
  ,t2.AGE_GROUP
  
  FROM (SELECT DISTINCT 
  
  Recipient_ID_1010,
  (CASE WHEN GRP_RPT= 'Xx' and SUBGRP_RPT = 'XXXX' THEN 'OP'
    ELSE GRP_RPT END ) as GRP_RPT,
  
  (CASE WHEN SUBGRP_RPT = 'XXXXX' THEN 'MH_OP_NLC'
    ELSE SUBGRP_RPT END ) as SUBGRP_RPT,
  
  (CASE WHEN GRP_RPT= 'Xx' and SUBGRP_RPT = 'XXXX' THEN 1
  ELSE 0 END) as INJECTIONS,
  
  (CASE WHEN GRP_RPT= 'Xx' and SUBGRP_RPT = 'XXXX' THEN 'XXX' ELSE NULL END ) AS DRUG_CLASS,
  
  (CASE WHEN instr(proc_concat,'J0400')     &gt; 0 THEN   'J0400'
        WHEN instr(proc_concat,'J0401')      &gt; 0 THEN  'J0401' 
        WHEN instr(proc_concat,'J2680')      &gt; 0 THEN  'J2680' 
        WHEN instr(proc_concat,'J1630')      &gt; 0 THEN  'J1630'
        WHEN instr(proc_concat,'J1631')      &gt; 0 THEN  'J1631' 
        WHEN instr(proc_concat,'J2358')      &gt; 0 THEN  'J2358'
        WHEN instr(proc_concat,'S0166')      &gt; 0 THEN  'S0166'
        WHEN instr(proc_concat,'J2426')      &gt; 0 THEN  'J2426' 
        WHEN instr(proc_concat,'J2794')      &gt; 0 THEN  'J2794' 
        WHEN instr(proc_concat,'S0163')      &gt; 0 THEN  'S0163' 
        WHEN instr(proc_concat,'J3490')      &gt; 0 THEN  'J3490'
        WHEN instr(proc_concat,'J0592')      &gt; 0  OR RATE_CODE_2078 IN ('2531', '2532', '2533', '2534' ) THEN  'J0592'
        WHEN instr(proc_concat,'J2315')      &gt; 0 THEN  'J2315' 
  
        ELSE NULL END ) as J_CODE,
  
  provider_entity as Prov_Entity_ID,                        
  COUNT(distinct recipient_id_1010||to_char(DATE_OF_SERVICE_3013,'MMDDYYYY')||PROVIDER_ENTITY) AS P_DAYS , 
  COUNT(distinct case when RECORD_CODE_H002=3 then recipient_id_1010||to_char(DATE_OF_SERVICE_3013,'MMDDYYYY')||PROVIDER_ENTITY_ID_E2135 end) AS FFS_claim,
  COUNT(distinct case when RECORD_CODE_H002=4 then recipient_id_1010||to_char(DATE_OF_SERVICE_3013,'MMDDYYYY')||PROVIDER_ENTITY_ID_E2135 end) AS MMC_claim,
  
  sum(case when RECORD_CODE_H002=3 then AMT_PAID_CLAIM Else 0 end) as FFS_AMT,
  sum(case when RECORD_CODE_H002=4 then decode(AMT_CHARGED_CLAIM_3199,0, AMT_CHARGED_CLAIM_TOTAL_E1025,AMT_CHARGED_CLAIM_3199 ) else 0  end) as MMC_AMT
  
  
  FROM &amp;OPDATA
  WHERE GRP_RPT not in ( 'IP') 
  AND SUBGRP_RPT not like ('Gen%') 
  AND SUBGRP_RPT not in ('XXXXXXX')
  and DATE_OF_SERVICE_3013 BETWEEN 
                                    TO_DATE('&amp;BeginDate','MM/DD/YYYY') AND 
                                    TO_DATE('&amp;EndDate','MM/DD/YYYY') 
  GROUP BY  Recipient_ID_1010, GRP_RPT, SUBGRP_RPT , provider_entity_id_E2135, proc_concat, RATE_CODE_2078  ) t1
  
  LEFT JOIN 
  (SELECT 
  A.*,
  (CASE WHEN    A.AGE  &lt;= 20                   THEN  1
        WHEN    A.AGE  &gt;= 21 AND A.AGE &lt;= 64   THEN  2
        WHEN    A.AGE  &gt;= 65                   THEN  3
        ELSE     0  END) as AGE_GROUP 
  
  ,(CASE WHEN A.PLAN_TYPE = 'X'        THEN 1
        WHEN A.PLAN_TYPE =  'XX'           THEN 2
        WHEN A.PLAN_TYPE = 'XXX'               THEN 3
        WHEN A.PLAN_TYPE = 'XXXX' THEN 4
        WHEN A.PLAN_TYPE = 'XXXXX'                THEN 5
        ELSE 0 END ) as TYPE_ORD
  FROM  
  
  (SELECT DISTINCT 
  RECIPIENT_ID_1010
  , T1.RECIPIENT_RACE_CODE_1190
  , T1.=REGION
  , T1.MANAGED_CARE
  , T1.CARE
  , T1.PCP_PROVIDER_ID_2001_3
  , T1.RP_2013_14
  , T1.RP_2013_14_OMH
  , T1.RP_2013_14_OASAS 
  , T1.RECIPIENT_COUNTY_1220
  , t1.HEALTH_HOME_ENROLL
  ,(CASE WHEN T1.RECIPIENT_CO IN ('66') THEN 'xXXXXXx' 
    ELSE 'XXxx' END) AS LOCATION
  ,(CASE WHEN T1.D_CATEGORY_ROLLUP_GROUP = 'XXX' THEN 'XXXX' 
    ELSE 'XXXXXX' END) AS AID_GROUP
  , T1.DATE_OF_BIRTH_RECIPIENT_1180
  , T1.RECIPIENT_SEX_1210
  , T1.D_CATEGORY_ROLLUP_GROUP
  ,(case when T1.DATE_OF_BIRTH_RECIPIENT_1180 &gt; to_date('&amp;BirthMonth','DD-MON-YY') then 0
       else floor(abs(months_between(to_date('&amp;BirthMonth','DD-MON-YY'),T1.DATE_OF_BIRTH_RECIPIENT_1180 )/12))
  end) as AGE
  , T3.COUNTY_LABEL
  ,(CASE
  WHEN  T1.MAN_CARE = 1  AND T2.PLAN_TYPE like ('XX%') THEN 'XXXXXX'
  WHEN  T1.MAN_CARE not in (1) THEN  'XXXX'
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE = 'XXXXXX'  THEN 'XXXXXX'
  WHEN  T1.MAN_CARE = 1 AND T2.Plan_Name IS NULL  THEN 'XXXXXX'
  ELSE  T2.PLAN_TYPE
  END) AS PLAN_TYPE
  
  ,(CASE
  WHEN  T1.MAN_CARE = 1  AND T2.PLAN_TYPE = 'XX'             THEN 'XX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXX'            THEN  'XXX'  
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXX'     THEN  'XXXX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXXX'            THEN  'XXXXX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXXXXX'  THEN 'XXXXXX' 
  WHEN  T1.MAN_CARE not in (1) AND T1.CARE not in (1) THEN 'XXXXX'
  WHEN  T1.MAN_CARE not in (1) AND T1.CARE= 1 THEN 'XXXXX'
  WHEN  T1.MANAGED_CARE = 1 AND T2.PlAN_NAME IS NULL  THEN  'XXXXXXXX'
  ELSE T2.PLAN_NAME END ) as PLAN_NAME
  
  FROM schema1.RECIPIENT_MASTER_TABLE   T1 
  LEFT JOIN schema2.REF_MCP_PRODUCT_TYPE     T2       ON  T1.PCP_PROVIDER_ID_2001_3 = T2.AID_PROVIDER_ID 
  LEFT JOIN schema3.CNF_DIM_COUNTY     T3       ON  T1.RECIPIENT_COUNTY_1220  = T3.COUNTY_CODE  
  WHERE YEAR_MONTH = '&amp;YearMonth'
  ) A
  WHERE  A.PLAN_TYPE IN ('XXX', 'XXX', 'XXXX', 'XXXXXX', 'XXXXX') ) t2  on t1.recipient_ID_1010= t2.recipient_id_1010 )
  
  UNION     
  (SELECT
  T1.RECIPIENT_ID_1010
  ,T1.GRP_RPT
  ,T1.SUBGRP_RPT
  ,'     ' as PROV_ENTITY_ID
  ,T1.P_DAYS
  ,NULL as LOS_C
  ,NULL as LOS
  ,T1.FFS_CLAIM
  ,T1.MMC_CLAIM
  ,T1.FFS_AMT
  ,T1.MMC_AMT
  ,T1.TOTAL_SUPPLY
  ,T1.TOTAL_DISPENSED
  ,null AS FLAG_ADM
  ,null AS FLAG_DCH
  ,T1.DRUG_NAME
  ,T1.DRUG_CLASS
  ,NULL AS INJECTIONS
  ,NULL AS J_CODE
  ,T2.RECIPIENT_RACE_CODE_1190
  ,T2.REGION
  ,t2.TYPE_ORD
  ,t2.MAN_CARE
  ,t2.CARE
  ,t2.PCP_PROVIDER_ID_2001_3
  ,t2.RP_2013_14
  ,t2.RP_2013_14_OMH
  ,t2.RP_2013_14_OASAS
  ,t2.ENROLL
  ,t2.RECIPIENT_COUNTY_1220
  ,t2.COUNTY_LABEL
  ,t2.LOCATION
  ,t2.D_GROUP
  ,t2.DATE_OF_BIRTH_RECIPIENT_1180 
  ,t2.RECIPIENT_SEX_1210
  ,t2.D_CATEGORY_ROLLUP_GROUP
  ,t2.AGE
  ,t2.PLAN_TYPE
  ,t2.PLAN_NAME
  ,t2.AGE_GROUP
  
  FROM 
  
  (SELECT
  
               T1.RECIPIENT_ID_1010
              ,T2.DRUG_NAME_NAME_RTYPE4 AS DRUG_NAME 
              , (CASE WHEN T1.DRUG_CLASS = 'XXXXX' THEN 'XXXXX' ELSE T1.DRUG_CLASS  END ) AS DRUG_CLASS
              ,'Rx' as GRP_RPT
              ,  CASE WHEN     t2.P_MED_INDICATOR=1 and t2.ADD_MED_INDICATOR=1 THEN  'XXXXXX'
                      WHEN     t2.P_MED_INDICATOR=1  THEN   'XXXXX' 
                      WHEN     t2.ADD_MED_INDICATOR=1 THEN 'XX' END AS  SUBGRP_RPT
  
  
  ,COUNT(distinct t1.original_claim_number_W026) AS P_DAYS 
  ,COUNT(distinct case when T1.RECORD_CODE_H002=3 then t1.original_claim_number_W026 end) AS FFS_claim
  ,COUNT(distinct case when T1.RECORD_CODE_H002=4 then t1.original_claim_number_W026 end) AS MMC_claim
  
  ,sum(case when T1.RECORD_CODE_H002=3 then T1.AMT_PAID_CLAIM_WITHOUT_PGP else 0 end) as FFS_AMT
  ,sum(case when T1.RECORD_CODE_H002=4 then decode(T1.AMT_CHARGED_CLAIM_3199,0, T1.AMT_CHARGED_CLAIM_TOTAL_E1025,T1.AMT_CHARGED_CLAIM_3199 ) else 0 end) as MMC_AMT
  ,sum(T1.NET_DAYS_SUPPLY_WITHOUT_PGP) as total_supply
  ,sum(T1.NET_QUANTITY_DISPENSED) as total_dispensed
  
  
        FROM schema1.NET_SERVICE_PHARMACY T1  , schema2.ALLMEDSWITHNDC t2      
      where T1.NATIONAL_DRUG_CODE_E1856 = T2.NDC_UPC_HRI and T1.NATIONAL_DRUG_CODE_E1856 is not null
  
  
        and (T2.ADD_MED_INDICATOR = 1 OR  T2.P_MED_INDICATOR = 1)
  
        AND   T1.INVOICE_TYPE_3301 IN ('09','10')
  
        AND T1.DATE_OF_SERVICE_3013 BETWEEN 
                                    TO_DATE('&amp;BeginDate','MM/DD/YYYY') AND 
                                    TO_DATE('&amp;EndDate','MM/DD/YYYY')
        group by  
          T1.RECIPIENT_ID_1010,
          T2.DRUG_NAME_NAME_RTYPE4
              ,T1.DRUG_CLASS,      
          (CASE WHEN t2.PS_MED_INDICATOR=1 and t2.ADD_MED_INDICATOR=1 THEN  'XXXXX'
                      WHEN     t2.P_MED_INDICATOR=1  THEN   'XXX' 
                       WHEN t2.ADD_MED_INDICATOR=1 THEN 'XXXXX' end
                    ) )
    t1 
  LEFT JOIN 
  (SELECT 
  A.*,
  (CASE WHEN    A.AGE  &lt;= 20                   THEN  1
        WHEN    A.AGE  &gt;= 21 AND A.AGE &lt;= 64   THEN  2
        WHEN    A.AGE  &gt;= 65                   THEN  3
        ELSE     0  END) as AGE_GROUP 
  
  ,(CASE WHEN A.PLAN_TYPE = 'X'        THEN 1
        WHEN A.PLAN_TYPE =  'XX'           THEN 2
        WHEN A.PLAN_TYPE = 'XXX'               THEN 3
        WHEN A.PLAN_TYPE = 'XXXX' THEN 4
        WHEN A.PLAN_TYPE = 'XXXXX'                THEN 5
        ELSE 0 END ) as TYPE_ORD
  FROM  
  
  (SELECT DISTINCT 
  RECIPIENT_ID_1010
  , T1.RECIPIENT_RACE_CODE_1190
  , T1.=REGION
  , T1.MANAGED_CARE
  , T1.CARE
  , T1.PCP_PROVIDER_ID_2001_3
  , T1.RP_2013_14
  , T1.RP_2013_14_OMH
  , T1.RP_2013_14_OASAS 
  , T1.RECIPIENT_COUNTY_1220
  , t1.HEALTH_HOME_ENROLL
  ,(CASE WHEN T1.RECIPIENT_CO IN ('66') THEN 'xXXXXXx' 
    ELSE 'XXxx' END) AS LOCATION
  ,(CASE WHEN T1.D_CATEGORY_ROLLUP_GROUP = 'XXX' THEN 'XXXX' 
    ELSE 'XXXXXX' END) AS AID_GROUP
  , T1.DATE_OF_BIRTH_RECIPIENT_1180
  , T1.RECIPIENT_SEX_1210
  , T1.D_CATEGORY_ROLLUP_GROUP
  ,(case when T1.DATE_OF_BIRTH_RECIPIENT_1180 &gt; to_date('&amp;BirthMonth','DD-MON-YY') then 0
       else floor(abs(months_between(to_date('&amp;BirthMonth','DD-MON-YY'),T1.DATE_OF_BIRTH_RECIPIENT_1180 )/12))
  end) as AGE
  , T3.COUNTY_LABEL
  ,(CASE
  WHEN  T1.MAN_CARE = 1  AND T2.PLAN_TYPE like ('XX%') THEN 'XXXXXX'
  WHEN  T1.MAN_CARE not in (1) THEN  'XXXX'
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE = 'XXXXXX'  THEN 'XXXXXX'
  WHEN  T1.MAN_CARE = 1 AND T2.Plan_Name IS NULL  THEN 'XXXXXX'
  ELSE  T2.PLAN_TYPE
  END) AS PLAN_TYPE
  
  ,(CASE
  WHEN  T1.MAN_CARE = 1  AND T2.PLAN_TYPE = 'XX'             THEN 'XX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXX'            THEN  'XXX'  
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXX'     THEN  'XXXX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXXX'            THEN  'XXXXX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXXXXX'  THEN 'XXXXXX' 
  WHEN  T1.MAN_CARE not in (1) AND T1.CARE not in (1) THEN 'XXXXX'
  WHEN  T1.MAN_CARE not in (1) AND T1.CARE= 1 THEN 'XXXXX'
  WHEN  T1.MANAGED_CARE = 1 AND T2.PlAN_NAME IS NULL  THEN  'XXXXXXXX'
  ELSE T2.PLAN_NAME END ) as PLAN_NAME
  
  FROM schema1.RECIPIENT_MASTER_TABLE   T1 
  LEFT JOIN schema2.REF_MCP_PRODUCT_TYPE     T2       ON  T1.PCP_PROVIDER_ID_2001_3 = T2.AID_PROVIDER_ID 
  LEFT JOIN schema3.CNF_DIM_COUNTY     T3       ON  T1.RECIPIENT_COUNTY_1220  = T3.COUNTY_CODE  
  WHERE YEAR_MONTH = '&amp;YearMonth'
  ) A
  WHERE  A.PLAN_TYPE IN ('XXX', 'XXX', 'XXXX', 'XXXXXX', 'XXXXX') ) t2  on t1.recipient_ID_1010= t2.recipient_id_1010 )
  
  UNION    
  (SELECT
  T1.RECIPIENT_ID_1010
  ,'IP' AS GRP_RPT
  ,T1.SUBGRP_RPT
  ,T1.PROV_ENTITY_ID
  ,NUll as P_DAYS
  ,(T1.DCH_DT - t1.ADM_DT)+1  AS LOS
  ,T1.FFS_CLAIM
  ,T1.MMC_CLAIM
  ,T1.FFS_AMT
  ,T1.MMC_AMT
  ,0 AS TOTAL_SUPPLY
  ,0 AS TOTAL_DISPENSED
  ,(CASE WHEN ADM_DT between to_date('&amp;BeginDate','MM/DD/YYYY') and to_date('&amp;EndDate','MM/DD/YYYY') THEN   1  END  ) as FLAG_ADM
  ,(CASE WHEN DCH_DT between to_date('&amp;EndDate','MM/DD/YYYY') and to_date('&amp;BeginDate','MM/DD/YYYY') THEN   1   END ) as FLAG_DCH
  ,NULL AS DRUG_NAME
  ,NULL AS DRUG_CLASS
  ,NULL as INJECTIONS
  ,NULL AS J_CODE
  ,T2.RECIPIENT_RACE_CODE_1190
  ,T2.OMH_REGION
  ,t2.TYPE_ORD
  ,t2.MAN_CARE
  ,t2.MEDICARE
  ,t2.PCP_PROVIDER_ID_2001_3
  ,t2.RP_2013_14
  ,t2.RP_2013_14_OMH
  ,t2.RP_2013_14_OASAS
  ,t2.HH_ENROLL
  ,t2.RECIPIENT_COUNTY_1220
  ,t2.COUNTY_LABEL
  ,t2.LOCATION
  ,t2.AID_GROUP
  ,t2.DATE_OF_BIRTH_RECIPIENT_1180 
  ,t2.RECIPIENT_SEX_1210
  ,t2.AID_CATEGORY_ROLLUP_GROUP
  ,t2.AGE
  ,t2.PLAN_TYPE
  ,t2.PLAN_NAME
  ,t2.AGE_GROUP
  FROM &amp;IPData t1 
  
  LEFT JOIN 
  (SELECT 
  A.*,
  (CASE WHEN    A.AGE  &lt;= 20                   THEN  1
        WHEN    A.AGE  &gt;= 21 AND A.AGE &lt;= 64   THEN  2
        WHEN    A.AGE  &gt;= 65                   THEN  3
        ELSE     0  END) as AGE_GROUP 
  
  ,(CASE WHEN A.PLAN_TYPE = 'X'        THEN 1
        WHEN A.PLAN_TYPE =  'XX'           THEN 2
        WHEN A.PLAN_TYPE = 'XXX'               THEN 3
        WHEN A.PLAN_TYPE = 'XXXX' THEN 4
        WHEN A.PLAN_TYPE = 'XXXXX'                THEN 5
        ELSE 0 END ) as TYPE_ORD
  FROM  
  
  (SELECT DISTINCT 
  RECIPIENT_ID_1010
  , T1.RECIPIENT_RACE_CODE_1190
  , T1.=REGION
  , T1.MANAGED_CARE
  , T1.CARE
  , T1.PCP_PROVIDER_ID_2001_3
  , T1.RP_2013_14
  , T1.RP_2013_14_OMH
  , T1.RP_2013_14_OASAS 
  , T1.RECIPIENT_COUNTY_1220
  , t1.HEALTH_HOME_ENROLL
  ,(CASE WHEN T1.RECIPIENT_CO IN ('66') THEN 'xXXXXXx' 
    ELSE 'XXxx' END) AS LOCATION
  ,(CASE WHEN T1.D_CATEGORY_ROLLUP_GROUP = 'XXX' THEN 'XXXX' 
    ELSE 'XXXXXX' END) AS AID_GROUP
  , T1.DATE_OF_BIRTH_RECIPIENT_1180
  , T1.RECIPIENT_SEX_1210
  , T1.D_CATEGORY_ROLLUP_GROUP
  ,(case when T1.DATE_OF_BIRTH_RECIPIENT_1180 &gt; to_date('&amp;BirthMonth','DD-MON-YY') then 0
       else floor(abs(months_between(to_date('&amp;BirthMonth','DD-MON-YY'),T1.DATE_OF_BIRTH_RECIPIENT_1180 )/12))
  end) as AGE
  , T3.COUNTY_LABEL
  ,(CASE
  WHEN  T1.MAN_CARE = 1  AND T2.PLAN_TYPE like ('XX%') THEN 'XXXXXX'
  WHEN  T1.MAN_CARE not in (1) THEN  'XXXX'
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE = 'XXXXXX'  THEN 'XXXXXX'
  WHEN  T1.MAN_CARE = 1 AND T2.Plan_Name IS NULL  THEN 'XXXXXX'
  ELSE  T2.PLAN_TYPE
  END) AS PLAN_TYPE
  
  ,(CASE
  WHEN  T1.MAN_CARE = 1  AND T2.PLAN_TYPE = 'XX'             THEN 'XX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXX'            THEN  'XXX'  
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXX'     THEN  'XXXX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXXX'            THEN  'XXXXX' 
  WHEN  T1.MAN_CARE = 1 AND T2.PLAN_TYPE  = 'XXXXXXX'  THEN 'XXXXXX' 
  WHEN  T1.MAN_CARE not in (1) AND T1.CARE not in (1) THEN 'XXXXX'
  WHEN  T1.MAN_CARE not in (1) AND T1.CARE= 1 THEN 'XXXXX'
  WHEN  T1.MANAGED_CARE = 1 AND T2.PlAN_NAME IS NULL  THEN  'XXXXXXXX'
  ELSE T2.PLAN_NAME END ) as PLAN_NAME
  
  FROM schema1.RECIPIENT_MASTER_TABLE   T1 
  LEFT JOIN schema2.REF_MCP_PRODUCT_TYPE     T2       ON  T1.PCP_PROVIDER_ID_2001_3 = T2.AID_PROVIDER_ID 
  LEFT JOIN schema3.CNF_DIM_COUNTY     T3       ON  T1.RECIPIENT_COUNTY_1220  = T3.COUNTY_CODE  
  WHERE YEAR_MONTH = '&amp;YearMonth'
  ) A
  WHERE  A.PLAN_TYPE IN ('XXX', 'XXX', 'XXXX', 'XXXXXX', 'XXXXX') ) t2  on t1.recipient_ID_1010= t2.recipient_id_1010 
  WHERE (t1.ADM_DT &lt;=to_date('&amp;EndDate','MM/DD/YYYY') and (t1.DCH_DT&gt;=to_date('&amp;BeginDate','MM/DD/YYYY')  or   t1.DCH_DT is null ) )  
  ) )
  
  
  M ) 
  </code></pre>