The syntax is correct. However you need at least SQL Server 2005 or above to use the code. Also the database must have compatibiity level set to 90 or higher.
; WITH SQLTMP(account_code) AS ( select account_code from as_opaccount_dtl with (nolock) where opcoa_id <> 'RITSOPCOA' and account_group = 'Revenue' )
select tran_no as 'tran_no', fbp_voucher_date as 'Tran_date' fin_ods..fbp_posted_trn_dtl as dtl JOIN SQLTMP ON (dtl.account_code = SQLTMP.account_code)
set nocount off
end
quote:Originally posted by Peso
Did you include the leading ; ? And did you put the WITH keyword?
Otherwise, post full query. Maybe something else is wrong not related to the CTE definition.
CREATE PROCEDURE Test
AS
SET NOCOUNT ON
;WITH TmpSQL(Account_Code)
AS (
SELECT Account_Code
FROM As_OpAccount_Dtl WITH (NOLOCK)
WHERE OpCoa_ID <> 'RITSOPCOA'
AND Account_Group = 'Revenue'
)
SELECT dtl.Tran_No AS Tran_No,
dtl.Fbp_Voucher_Date AS Tran_date
FROM Fin_Ods..Fbp_Posted_Trn_Dtl AS dtl
INNER JOIN TmpSQL AS ts ON ts.Account_Code = dtl.Account_Code
I am glad you choose this very easy example to learn CTE. You really don't need a CTE for this easy task so it's perfect for learning. Also it makes debugging much easier.
txs mr.pseo now the code works fine..atlast you said it makes debugging much easier..how this can be done??
quote:Originally posted by Peso
I am glad you choose this very easy example to learn CTE. You really don't need a CTE for this easy task so it's perfect for learning. Also it makes debugging much easier.
with sqltmp syntax problem
Posted - 04/25/2008 : 09:48:57
