Join Multple CTEs in SQL - Error " Invalid Table Name"

Question

I am trying to get my three CTEs to join and plus one more table . Below is what I am doing but I am missing something . I have looked up same error and I am being told I am missing "," but I did that and still the same error. The error is "Invalid Table Name". Any help is much appreciated as always .

Side note - I did run each CTE and each one ran perfectly by themselves and as single CTE, its when I try and put them together I get the error .

--------1. CTE  

WITH

Memb AS (
Select *
 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'PA' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')

  Union 


  Select *
 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'NJ' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')                    

),
  --select * from Memb


----------- 2.CTE Fax Flag 
 With 

  Fax_Flag AS(

SELECT Distinct
  cmt.CNTC_NM As Provider_Name,
  cmt.FAX_NBR,

  CASE When cmt.FAX_NBR ='201-553-7889' THEN 'Yes'
     ELSE 'No'
       END  AS Fax_Flag

   From  icue.cmnct_trans cmt
    Where cmt.CNTC_NM ='CHILDRENS HOSP PHILADELPHIA'

   ),   

   --Select * From Fax_Flag


   ------------3. CTE  Letter Flag

  With 

   Letter_Sent AS(

   Select Distinct
   act.Actv_strt_dttm AS Fax_Date,
   act.ACTV_TYP_ID,


    CASE When ACTV_TYP_ID ='5' THEN 'Yes'
     ELSE 'No'
       END  AS Letter_Flag

  From icue.actv act
     Where trunc(act.actv_strt_dttm) between to_date('19-FEB-2018','DD-MON-YYYY') and to_date('06-MAR-2018','DD-MON-YYYY')

),

  Select * From Letter_sent



---------FINAL - I want to put all the CTE together

Select Distinct

    mb.Fst_Nm AS First_Name,
    mb.Lst_Nm AS Last Name,
    mbc.Member_Policy_State,
    mbc.mbr_id AS Memeber_ID,
    cmt.HSC_ID AS Auth_Number,
    cmt.Fax_Number,
    cmt.Provider_Name,
    act.Fax_Date,
    act.Letter_Flag,
    cmt.Fax_Flag

    From icue.mbr mb


 Inner Join Memb mbc     --- CTE 1
  On mb.Mbr_ID = mbc.Mbr_ID

 Inner Join Fax_Flag cmt   ---- CTE 2
  On cmt.HSC_ID = cmt.HSC_ID

 Inner Join Letter_Sent act ---- CTE 3
  On act.Mbr_ID = mbc.Mbr_ID

update *** I did try and join the two CTEs below, but I keep getting an "Table/view does not exists" error

   --------1. CTE  Bring in only PA and NJ market

With

Memb AS (
Select Distinct 
  mbc.Hsc_Id                                   AS  Auth_Number,
  mbc.POL_ISS_ST_CD                            AS  Policy_State

 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'PA' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')

  Union 


  Select Distinct 
    mbc.hsc_id                         AS Auth_Number,
    mbc.POL_ISS_ST_CD                  AS  Policy_State

 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'NJ' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')  



),
  ---select * from Memb


----------- 2.CTE Fax Flag 


  Fax_Flag AS(

SELECT Distinct
  cmt.CNTC_NM                     As Provider_Name,
  cmt.FAX_NBR                     AS Fax_Number,
  cmt.HSC_ID                      AS Auth_Number,


  CASE When cmt.FAX_NBR ='201-553-7889' THEN 'Yes'
     ELSE 'No'
       END  AS Fax_Flag

   From icue.cmnct_trans cmt

  Left Join Memb mbc
   On cmt.hsc_id = mbc.Hsc_Id
     And cmt.CNTC_NM ='CHILDRENS HOSP PHILADELPHIA'

   )

   Select * From Fax_Flag 

Answer 1

I figured out what I was doing wrong . I was Referencing a column with out Referencing its Alias, since I defined it in the first CTE . Code is below

--------1. CTE  Bring in only PA and NJ market

With

Memb AS (
Select Distinct 
  mbc.Hsc_Id                                   AS  Auth_Number,
  mbc.POL_ISS_ST_CD                            AS  Policy_State

 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'PA' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')

  Union 


  Select Distinct 
    mbc.hsc_id                         AS Auth_Number,
    mbc.POL_ISS_ST_CD                  AS  Policy_State

 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'NJ' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')  



),
  ---select * from Memb


----------- 2.CTE Fax Flag 


  Fax_Flag AS(

SELECT Distinct
  cmt.CNTC_NM                     As Provider_Name,
  cmt.FAX_NBR                     AS Fax_Number,
  --cmt.HSC_ID                      AS Auth_Number,


  CASE When cmt.FAX_NBR ='201-553-7889' THEN 'Yes'
     ELSE 'No'
       END  AS Fax_Flag

   From Memb mbc

  Left Join icue.cmnct_trans cmt
   On mbc.Auth_Number = cmt.hsc_id ----- needed to update mbc.HSC_ID to mbc.auth_number
     And cmt.CNTC_NM ='CHILDRENS HOSP PHILADELPHIA'

   )

   Select * From Fax_Flag

Answer 2

First of all, it's easier to read code if you lay it out neatly. When I do that I get this:

with memb as
     ( select distinct
              mbc.hsc_id as auth_number
            , mbc.pol_iss_st_cd as policy_state
       from   icue.hsc_mbr_cov mbc
       where  mbc.pol_iss_st_cd = 'PA'
       and    (mbc.lob_typ_id = '12' or mbc.clm_pltfm_id = 'A9')
       union
       select distinct
              mbc.hsc_id as auth_number
            , mbc.pol_iss_st_cd as policy_state
       from   icue.hsc_mbr_cov mbc
       where  mbc.pol_iss_st_cd = 'NJ'
       and    (mbc.lob_typ_id = '12' or mbc.clm_pltfm_id = 'A9')
      )
    , fax_flag as
      ( select distinct
               cmt.cntc_nm as provider_name
             , cmt.fax_nbr as fax_number
             , cmt.hsc_id as auth_number
             , case
                   when cmt.fax_nbr = '201-553-7889' then 'Yes'
                   else 'No'
               end as fax_flag
        from   icue.cmnct_trans cmt
               left join memb mbc
                    on  mbc.hsc_id = cmt.hsc_id
                    and cmt.cntc_nm = 'CHILDRENS HOSP PHILADELPHIA' )
select * from fax_flag;

That union looks odd to me, as the two queries are the same apart from pol_iss_st_cd, so why not simply:

with memb as
     ( select distinct
              mbc.hsc_id as auth_number
            , mbc.pol_iss_st_cd as policy_state
       from   icue.hsc_mbr_cov mbc
       where  mbc.pol_iss_st_cd in ('PA','NJ')
       and    (mbc.lob_typ_id = '12' or mbc.clm_pltfm_id = 'A9') )
    , fax_flag as
      ( select distinct
               cmt.cntc_nm as provider_name
             , cmt.fax_nbr as fax_number
             , cmt.hsc_id as auth_number
             , case
                   when cmt.fax_nbr = '201-553-7889' then 'Yes'
                   else 'No'
               end as fax_flag
        from   icue.cmnct_trans cmt
               left join memb mbc
                    on  cmt.hsc_id = mbc.hsc_id
                    and cmt.cntc_nm = 'CHILDRENS HOSP PHILADELPHIA' )
select * from fax_flag;

That gives me ORA-00942: table or view does not exist because I don't have icue.hsc_mbr_cov, but otherwise it looks fine. If you are getting that error, which table is it pointing to and can you query it on its own (just to take WITH clause syntax out of the equation).

If this is about the syntax in general, though, we can simplify it to a generic test case:

with demo1 (id) as ( select 'X' from dual )
   , demo2 (id) as ( select 'X' from dual )
   , demo3 (id) as ( select 'X' from dual )
select d.dummy
from   dual d
       join demo1 d1 on d1.id = d.dummy
       join demo2 d2 on d2.id = d1.id
       join demo3 d3 on d3.id = d2.id
where  d.dummy = 'X';

Answer 3

Try removing the 2nd and 3rd WITH and the comma at the end of the 3rd CTE

--------1. CTE  

WITH

Memb AS (
Select *
 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'PA' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')

  Union 


  Select *
 From icue.HSC_MBR_COV mbc
     Where mbc.POL_ISS_ST_CD = 'NJ' 
  And  (mbc.LOB_TYP_ID = '12' OR mbc.CLM_PLTFM_ID  = 'A9')                    

),
  --select * from Memb


----------- 2.CTE Fax Flag 
--- With 

  Fax_Flag AS(

SELECT Distinct
  cmt.CNTC_NM As Provider_Name,
  cmt.FAX_NBR,

  CASE When cmt.FAX_NBR ='201-553-7889' THEN 'Yes'
     ELSE 'No'
       END  AS Fax_Flag

   From  icue.cmnct_trans cmt
    Where cmt.CNTC_NM ='CHILDRENS HOSP PHILADELPHIA'

   ),   

   --Select * From Fax_Flag


   ------------3. CTE  Letter Flag

---  With 

   Letter_Sent AS(

   Select Distinct
   act.Actv_strt_dttm AS Fax_Date,
   act.ACTV_TYP_ID,


    CASE When ACTV_TYP_ID ='5' THEN 'Yes'
     ELSE 'No'
       END  AS Letter_Flag

  From icue.actv act
     Where trunc(act.actv_strt_dttm) between to_date('19-FEB-2018','DD-MON-YYYY') and to_date('06-MAR-2018','DD-MON-YYYY')

) -- , Removed the comma



---------FINAL - I want to put all the CTE together

Select Distinct

    mb.Fst_Nm AS First_Name,
    mb.Lst_Nm AS Last Name,
    mbc.Member_Policy_State,
    mbc.mbr_id AS Memeber_ID,
    cmt.HSC_ID AS Auth_Number,
    cmt.Fax_Number,
    cmt.Provider_Name,
    act.Fax_Date,
    act.Letter_Flag,
    cmt.Fax_Flag

    From icue.mbr mb


 Inner Join Memb mbc     --- CTE 1
  On mb.Mbr_ID = mbc.Mbr_ID

 Inner Join Fax_Flag cmt   ---- CTE 2
  On cmt.HSC_ID = cmt.HSC_ID

 Inner Join Letter_Sent act ---- CTE 3
  On act.Mbr_ID = mbc.Mbr_ID