Share via

Invalid object name cte

Naomi Nosonovsky 8,906 Reputation points
2025-08-19T16:18:51.7433333+00:00

Hello everyone,

Why invalid object name cte appears on the second line in this valid query in run-time:

SELECT 'Automation OHCS' AS source,
           cte.Request_Type,
           cte.Change_Type,
           cte.[Exclusion_Reason],
           -- 'Professional' AS category,
           CAST(cte.Provider_Type AS VARCHAR(10)) AS provider_type,
           CAST(cte.Social_Group AS VARCHAR(3)) AS SocialGroup,
           CAST(cte.Status AS VARCHAR(10)) AS status,
           CAST(cte.[OHCS Eff Date] AS DATE) AS effective_date,
           CAST(CASE
                    WHEN cte.Provider_Type IN ( 'Group, Solo' ) THEN
                        [cte].[OHCS Eff Date]
                    WHEN cte.Provider_Type = 'Rendering' THEN
                    (
                        SELECT TOP (1)
                               dt
                        FROM
                        (
                            SELECT FIRST_VALUE(cte.[OHCS Eff Date]) OVER (PARTITION BY npi ORDER BY cte.[OHCS Eff Date]) AS dt
                            FROM #Additions f
                            UNION
                            SELECT MIN(f.firstcontracteffectivedate) AS dt
                            FROM stg.facets_full f
                            WHERE f.npi = cte.npi
                        ) AS subselect
                    )
                END AS DATE) AS earliest_effective_date,
           CAST([cte].[OHCS Change Date] AS DATE) AS change_date,
           CAST([OHCS Term Date] AS DATE) AS term_date,
           CAST(NULL AS DATE) AS latest_term_date,



           cte.tin_number,
           cte.npi,
           CAST(cte.provider_id AS VARCHAR(50)) AS provider_id,
           CAST(cte.Prov_Last_Name AS VARCHAR(100)) AS provider_last_name,
           CAST(cte.prov_first_name AS VARCHAR(30)) AS provider_first_name,
           CAST(cte.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
           CAST(cte.clinic_name AS VARCHAR(100)) AS clinic_name,
           -- cte.prov_group,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_specialty
                END AS VARCHAR(100)) AS t50_specialty,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_provider_type
                END AS VARCHAR(100)) AS t50_provider_type,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.taxonomy
                END AS VARCHAR(10)) AS t15_taxonomy,
           CAST(cte.license_number AS VARCHAR(15)) AS license_number,
           CAST(cte.license_state AS VARCHAR(50)) AS license_state,
           CAST(cte.license_issue_date AS DATE) AS license_issue_date,
           CAST(cte.loc_address1 AS VARCHAR(50)) AS svc_address_line1,
           CAST(cte.loc_address2 AS VARCHAR(50)) AS svc_address_line2,
           CAST(cte.loc_city AS VARCHAR(30)) AS svc_city,
           CAST(cte.loc_state AS VARCHAR(2)) AS svc_state,
           CAST(cte.loc_zip AS VARCHAR(9)) AS svc_zip,
       
           CAST(cte.remit_address1 AS VARCHAR(50)) AS remit_address_line1,
           CAST(cte.remit_address2 AS VARCHAR(50)) AS remit_address_line2,
           CAST(cte.remit_city AS VARCHAR(30)) AS remit_city,
           CAST(cte.remit_state AS VARCHAR(2)) AS remit_state,
           CAST(cte.remit_zip AS VARCHAR(9)) AS remit_zip,

           --  cte.specialty,
           CAST(cte.sub_specialty AS VARCHAR(1000)) AS sub_specialty
    --cte.acn_prov_id,
    --cte.provider_id,
    --cte.office_location_id,
    --cte.main_status,
    --cte.sub_status,
    --cte.status_date,
    --cte.loc_eff_date,
    --cte.loc_term_date,
    --cte.tin_eff_date,
    --cte.tin_term_date,
    --cte.prov_phone,
    --cte.prov_fax,
    --cte.gender,
    --cte.client_term_date,
    --cte.cred_status_eff_date
    FROM #Additions cte
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte.specialty = tx.specialty
               AND tx.source = 'OHCS'

I don't see why it complains. The code is correct, the alias cte clearly exists. The query has 3 parts, identical to the first portion I posted there are 2 more queries joined with UNON ALL keyword. I'll try changed second to cte1 and third to cte2 and see what'll happen although I don't expect it helps.

Any suggestions?

SQL Server | SQL Server Transact-SQL

Answer recommended by moderator
  1. Naomi Nosonovsky 8,906 Reputation points
    2025-08-19T16:33:19.1+00:00

    Did some voodoo, and the problem disappeared.

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.