CTE Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CTE Problem

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-05-24 : 02:33:47

Hi can any body correct the below query.the error throw while executing the sp.while iam commenting the highlighted query its working fine.but i need to insert even in another temp table too.

Msg 208, Level 16, State 1, Procedure test_sp, Line 31
Invalid object name 'sql_sch_account'.

Sample Data:

create procedure test_sp
as
begin
Declare @test table(company varchar(10))
Declare @test2 table(company varchar(10))
;WITH SQL_SCH(acc_sch_no)
as
(
SELECT ACC_SCH_NO
FROM FIN_ODS..fsl_schlay_dtl as dtl with(NOLOCK)
WHERE schlay_no = 'INDREVMATRIX'
AND company_code = 'RSICO'
)

, sql_sch_account(company_code,group_desc,account_code)
as
(
SELECT company_code,group_desc,acc_sch_no
FROM FIN_ODS..fsl_schlay_dtl as dtl with(NOLOCK)
WHERE schlay_no in(
select acc_sch_no
from SQL_SCH as sch with (nolock)
)
and company_code='RSICO'
)

insert into @test(company) select company_code from sql_sch_account as sql with (nolock)
insert into @test2(company) select company_code from sql_sch_account as sql with (nolock)

end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 03:35:55

quote:
Originally posted by sent_sara

Hi can any body correct the below query.the error throw while executing the sp.while iam commenting the highlighted query its working fine.but i need to insert even in another temp table too.

Msg 208, Level 16, State 1, Procedure test_sp, Line 31
Invalid object name 'sql_sch_account'.

Sample Data:

create procedure test_sp
as
begin
Declare @test table(company varchar(10))
Declare @test2 table(company varchar(10))
;WITH SQL_SCH(acc_sch_no)
as
(
SELECT ACC_SCH_NO
FROM FIN_ODS..fsl_schlay_dtl as dtl with(NOLOCK)
WHERE schlay_no = 'INDREVMATRIX'
AND company_code = 'RSICO'
)

, sql_sch_account(company_code,group_desc,account_code)
as
(
SELECT company_code,group_desc,acc_sch_no
FROM FIN_ODS..fsl_schlay_dtl as dtl with(NOLOCK)
WHERE schlay_no in(
select acc_sch_no
from SQL_SCH as sch with (nolock)
)
and company_code='RSICO'
)

insert into @test(company) select company_code from sql_sch_account as sql with (nolock)
insert into @test2(company) select company_code from sql_sch_account as sql with (nolock)

end

You can access the CTE only once that too immediately after the creation of it. So the CTE wont be available for you in the second insert statement and it will error. I think you can do this by merging the current two inserts into one like below:-

create procedure test_sp
as
begin
Declare @test table(company varchar(10))
Declare @test2 table(company varchar(10))
;WITH SQL_SCH(acc_sch_no)
as
	(
		SELECT ACC_SCH_NO 
		FROM 	FIN_ODS..fsl_schlay_dtl as dtl with(NOLOCK)
		WHERE 	schlay_no 	    =  'INDREVMATRIX'
		AND	    company_code 	=  'RSICO'	
	)

, sql_sch_account(company_code,group_desc,account_code) 
as
	(
		SELECT  company_code,group_desc,acc_sch_no
		FROM 	FIN_ODS..fsl_schlay_dtl as dtl with(NOLOCK)
		WHERE   schlay_no in(
							 select acc_sch_no 
							 from   SQL_SCH as sch with (nolock)
							 )
		 and   company_code='RSICO'
	)




insert into @test(company) 
output inserted.company_code into @test2
select company_code from sql_sch_account as sql with (nolock)


end

I still cant understand why you are populating the company code values to two table variables though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources