Could use some HELP with the WITH clause

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

Could use some HELP with the WITH clause

Author

Topic

jturkel
Starting Member

3 Posts

Posted - 2009-05-14 : 13:12:54

Any ideas what's causing this syntax error?

This is the syntax error msg:

error 156: Incorrect syntax near the word "WITH".
Line 19: Incorrect syntax near ','.

**********************************************************************

CREATE PROCEDURE USP_RP42
(
@From_Expire_Date CHAR (8),
@To_Expire_Date CHAR (8),
@Property CHAR (6)
)

as

declare @PropertyValue as char(8)
set @PropertyValue = '%%'
if ltrim(rtrim(@Property)) <> '*ALL' and ltrim(rtrim(@Property)) <> space(1)
begin
set @PropertyValue = '%' + ltrim(rtrim(@Property)) + '%'
end

WITH
sj (cono, copg, amt) as
(select sjcono, sjcopg, sum((sjcash+sjagcm+sjtax+sjadj)*sjcvrt) FROM fpsj GROUP BY sjcono, sjcopg),
rt (cono, copg, amt, csdt) as
(select rtcono, rtcopg, sum(rtroy*rtcvrt), max(rtcsdt) FROM fprt GROUP BY rtcono, rtcopg)

SELECT
ISNULL(ppname, coppno) as Property,
ISNULL(RTRIM(acname), ' ') + '...' + coacno as Licensee,
cocono + ' ' + CAST(cocopg as char) as Contract,
SUBSTRING(cast(cofrdt as char),5,2) + '/' + SUBSTRING(cast(cofrdt as char),7,2) + '/' + SUBSTRING(cast(cofrdt as char),1,4) as Start_Date,
SUBSTRING(cast(cotodt as char),5,2) + '/' + SUBSTRING(cast(cotodt as char),7,2) + '/' + SUBSTRING(cast(cotodt as char),1,4) as Expire_Date,
CAST(coslof as int) as SellOff,
coguar*cocvrt as Guarantee_US$,
ISNULL(sj.amt,0) as Gross_Cash,
ISNULL(rt.amt,0) as Gross_Royalties
FROM fpco
LEFT OUTER JOIN fppp ON ppppno=coppno
LEFT OUTER JOIN fpac ON acacno=coacno
LEFT OUTER JOIN fpztd div ON div.tdtabl='DIV ' and div.tdkey=codiv
LEFT OUTER JOIN sj ON sj.cono=cocono and sj.copg=cocopg
LEFT OUTER JOIN rt ON rt.cono=cocono and rt.copg=cocopg
WHERE COSGST='FE' and cotodt between @From_Expire_Date and @To_Expire_Date and ltrim(rtrim(coppno)) like ltrim(rtrim(@PropertyValue))
ORDER BY Property, Licensee, Contract
GO

**********************************************************************

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-05-14 : 13:22:28

Pretty sure if you are making 2 CTEs, you need 2 WITH statements...

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-14 : 13:23:43

No. This particular problem is that the last statement before the with must be terminated with a semicolon ";".

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-14 : 13:24:28

You can make several CTE's as long as they are separated with comma, as OP writes.

E 12°55'05.63"
N 56°04'39.26"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-05-14 : 13:26:25

Ah. I like learning new things

Too bad it's not Enterprisie enough for me to use though...

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jturkel
Starting Member

3 Posts

Posted - 2009-05-14 : 13:37:58

Thanks for the suggestion Peso, but unfortunately the semicolon didn't resolve the problem.
I placed the ; right after "end" in the line above the "WITH".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-14 : 13:46:29

And you are using SQL Server 2005 with compatibility level set to 90 or higher?

E 12°55'05.63"
N 56°04'39.26"

jturkel
Starting Member

3 Posts

Posted - 2009-05-14 : 14:02:56

I'm using SQL Server 2005.
How do I check the compatibility level?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-14 : 14:03:41