with sqltmp syntax 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

with sqltmp syntax problem

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-25 : 09:48:57

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

30421 Posts

Posted - 2008-04-25 : 09:49:54

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 09:50:45

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

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

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-25 : 10:01:09

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:08:37

SELECT name, compatibility_level from sys.databases

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

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-25 : 10:15:56

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:23:08

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"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-25 : 10:34:23

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:43:18

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:46:22

[code]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[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:47:34

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"

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-25 : 11:13:45

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources