CTE and PIVOT in main query

Transact-SQL
1

What did I do wrong with this, when I try to link it to my main query it can not find the CTE table.
I can run it alone fine, works.. I need to be able to use the results in another query BUT cant use a temp table.
Any help is appreciated.

WITH CTEerror AS
(
Select * from
(
SELECT TPB712_BILL_EXCEPTION.vst_int_id, sp_name, bil_cre_dt,
row_number() over (partition by TPB712_BILL_EXCEPTION.vst_int_id order by error_message) as Seq
FROM TPB712_BILL_EXCEPTION WITH (NOLOCK)
JOIN TPB710_PRE_BILL TPB710 WITH (NOLOCK) ON TPB710.pre_bill_int_id = TPB712_BILL_EXCEPTION.pre_bill_int_id
JOIN TSM710_CLAIMS_RETNTN_CRITERIA rc WITH (NOLOCK) on rc.claims_retntn_criteria_int_id = TPB712_BILL_EXCEPTION.claims_retntn_criteria_int_id
WHERE TPB712_BILL_EXCEPTION.bil_cre_dt >= getdate()-2 and
TPB712_BILL_EXCEPTION.ivo_int_id = 0 and
not exists (select 1 from TPB711_BILL WITH (NOLOCK)
WHERE TPB712_BILL_EXCEPTION.pre_bill_int_id = TPB711_BILL.pre_bill_int_id )
) as basedata
)
select * from CTEerror
PIVOT(
MAX(sp_name)
FOR Seq
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
)AS PV

2

Hard to say without seeing how you are linking it to your main query. A CTE can be used only within the same statement.

3

Thanks James, I dont know what you mean in the same statement.
Its not a complex query but here it is... I am assuming my logic or naming isnt correct to add it to my query.

ERROR is=Invalid object name 'CTEerror'

WITH CTEerror AS
(
Select * from
(
SELECT TPB712_BILL_EXCEPTION.vst_int_id, sp_name, bil_cre_dt,
row_number() over (partition by TPB712_BILL_EXCEPTION.vst_int_id order by error_message) as Seq
FROM TPB712_BILL_EXCEPTION WITH (NOLOCK)
JOIN TPB710_PRE_BILL TPB710 WITH (NOLOCK) ON TPB710.pre_bill_int_id = TPB712_BILL_EXCEPTION.pre_bill_int_id
JOIN TSM710_CLAIMS_RETNTN_CRITERIA rc WITH (NOLOCK) on rc.claims_retntn_criteria_int_id = TPB712_BILL_EXCEPTION.claims_retntn_criteria_int_id
WHERE TPB712_BILL_EXCEPTION.bil_cre_dt >= getdate()-2 and
TPB712_BILL_EXCEPTION.ivo_int_id = 0 and
not exists (select 1 from TPB711_BILL WITH (NOLOCK)
WHERE TPB712_BILL_EXCEPTION.pre_bill_int_id = TPB711_BILL.pre_bill_int_id )
) as basedata
)
select * from CTEerror
PIVOT(
MAX(sp_name)
FOR Seq
IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
)AS PV

SELECT

V.med_rec_no as [MedRecordNumber]
,V.vst_ext_id as [AccountNumber]
,PC.cod_dtl_ext_id as [Patient Category]
,PT.cod_dtl_ext_id as [Patient Type]

,V.dschrg_ts as [Discharge Date]
,GEND.cod_dtl_ds as [Patient Sex]
,V.adm_ts as [AdmitDate]
,P.bth_ts as [PatientDateofBirth]
,RE.lst_mod_ts as [ModifyDate]
,AST.cod_dtl_ds as [Account Status]
, [1] as[CRC edit] ,[2]as[CRC edit2],[3]as[CRC edit3],[4]as[CRC edit4],[5]as[CRC edit5],[6]as[CRC edit6],[7]as[CRC edit7],[8]as[CRC edit8],[9]as[CRC edit9],[10]as[CRC edit10]


,'' as [ChargeCodes]
,'' as [Charges$]
,final_bill_dt as [Billed Date]
,RE.tot_chg_at as [Amount $ Billed] 
,Isnull(RE.tot_chg_at,0) - Isnull(RE.atl_pri_reb_at,0) -Isnull(RE.atl_sdy_reb_at,0) - Isnull(RE.tot_adj_at,0) as [AccountBalance]
,Isnull(RE.atl_pri_reb_at,0) -Isnull(RE.atl_sdy_reb_at,0)   as [Payments Recd]
,last_payment_dt as [Payment Date Recd]
,SC.cod_dtl_ds as [Location]
,SC.cod_dtl_ds as [MedService]

FROM
TPM300_PAT_VISIT AS V
LEFT JOIN
TMR400_REIMBURSEMENT AS RE ON V.vst_int_id = RE.vst_int_id
LEFT JOIN
TSM180_MST_COD_DTL PT ON V.pat_ty = PT.cod_dtl_int_id
LEFT JOIN
TSM180_MST_COD_DTL AST ON V.vst_sta_cd= AST.cod_dtl_int_id
LEFT JOIN
TSM180_MST_COD_DTL PC ON V.pat_cat_cd = PC.cod_dtl_int_id
LEFT JOIN
TSM180_MST_COD_DTL SC ON V.adm_srv_cd = SC.cod_dtl_int_id
left join
TSM040_PERSON_HDR as P ON V.psn_int_id = P.psn_int_id
LEFT JOIN
TSM180_MST_COD_DTL fc ON V.fin_cls_cd = fc.cod_dtl_int_id
JOIN
TSM180_MST_COD_DTL GEND ON P.sex_cd = GEND.cod_dtl_int_id
JOIN CTEerror on CTEerror.vst_int_id = V.vst_int_id
WHERE

V.dschrg_ts is not null 
and V.dschg_sta_cd is not null --canceled accounts
and V.pat_ty <> 15331 --unapplied
and (Isnull(RE.tot_chg_at,0) - Isnull(RE.atl_pri_reb_at,0) -Isnull(RE.atl_sdy_reb_at,0) - Isnull(RE.tot_adj_at,0)) <>0
and vst_sta_cd <> 14796 -- bad debt
and dschrg_ts is not null
4

A statement ends at the semi-colon.

Perhaps you could do something like this:

WITH CTEerror
AS ( 
  -- your existing code for CTError here
),
cte2 AS -- wrapping your pivot query into another cte
(
	SELECT
		*
	FROM
		CTEerror
		PIVOT
			(
				MAX(sp_name)
				FOR Seq IN
				(
					[1], [2], [3], [4], [5], [6], [7], [8], [9], [10]
				)
			) AS PV
)
SELECT -- your final query where you can use both CTError and cte2 as well as other tables.
	col1,col2,....
FROM
	CTEerror c1
	inner join Cte2 c2 ON ....
5

Good solution, that worked for me.. THank you.