SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 with sqltmp syntax problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 04/25/2008 :  09:48:57  Show Profile  Reply with Quote
WITH SQLTMP(opcoa_id)
AS
(select opcoa_id
from as_opaccount_dtl (nolock)
where opcoa_id <> 'RITSOPCOA'
and account_group = 'Revenue')

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.


can any one correct the syntax



SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/25/2008 :  09:49:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/25/2008 :  09:50:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
;WITH SQLTMP(opcoa_id)
AS (
	select	opcoa_id
	from	as_opaccount_dtl with (nolock)
	where	opcoa_id <> 'RITSOPCOA'
		and account_group = 'Revenue'
)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 04/25/2008 :  10:01:09  Show Profile  Reply with Quote
my database server sqlserver2005 only..where to check the Compatibiity level

quote:
Originally posted by Peso

;WITH SQLTMP(opcoa_id)
AS (
	select	opcoa_id
	from	as_opaccount_dtl with (nolock)
	where	opcoa_id <> 'RITSOPCOA'
		and account_group = 'Revenue'
)



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/25/2008 :  10:08:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT name, compatibility_level from sys.databases



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 04/25/2008 :  10:15:56  Show Profile  Reply with Quote
txs for your immediate reply Mr.peso
compatability is 90 only ..
even then the syntax error is coming.
quote:
Originally posted by Peso

SELECT name, compatibility_level from sys.databases



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/25/2008 :  10:23:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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.



E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 04/25/2008 10:23:21
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 04/25/2008 :  10:34:23  Show Profile  Reply with Quote
create procedure test
as
begin
set nocount on

;
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.



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/25/2008 :  10:43:18  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There is no FROM keyword after the Tran_date column and the table name on next line.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/25/2008 :  10:46:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 04/25/2008 10:46:35
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 04/25/2008 :  10:47:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

India
366 Posts

Posted - 04/25/2008 :  11:13:45  Show Profile  Reply with Quote
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.



E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000