Did some voodoo, and the problem disappeared.
Invalid object name cte
Naomi Nosonovsky
8,906
Reputation points
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
Answer recommended by moderator