Incorrect syntax near '0' - 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

Incorrect syntax near '0'

Author

Topic

LOLCatLady
Starting Member

24 Posts

Posted - 2010-09-20 : 17:46:37

The code below is giving me the error Incorrect syntax near '0' referring to the very last character of the code. I am completely stumped as to how to fix this. It doesn't matter what I type there I get the same error.

Can anybody help?

declare @appeal varchar(10) set @appeal = 'Non'
declare @description varchar(50) set @description = '201009All-Non'
declare @today smalldatetime set @today = getdate()

delete from millwork.dbo.AllSep10 where description = @description

declare @startFY smalldatetime
set @startFY = case when month(@today)>='3' then '3/1/'+ cast(YEAR(@today) AS varchar(4))
else DATEADD(yy,-1, '3/1/'+ cast(YEAR(@today) as varchar(4))) end
declare @startFYM1 smalldatetime
set @startFYM1 = case when month(@today)>='3' then DATEADD(yy,-1, '3/1/'+ cast(YEAR(@today) as varchar(4)))
else DATEADD(yy,-2, '3/1/'+ cast(YEAR(@today) as varchar(4))) end
declare @endMinus1 smalldatetime
set @endMinus1 = cast(cast(month(@today) as varchar(2)) +'/1/' + CAST(YEAR(@today) AS varchar(4)) as datetime)
declare @startFYM2 smalldatetime
set @startFYM2 = case when month(@today)>='3' then DATEADD(yy,-2, '3/1/'+ cast(YEAR(@today) as varchar(4)))
else DATEADD(yy,-3, '3/1/'+ cast(YEAR(@today) as varchar(4))) end
declare @startFYM3 smalldatetime
set @startFYM3 = case when month(@today)>='3' then DATEADD(yy,-3, '3/1/'+ cast(YEAR(@today) as varchar(4)))
else DATEADD(yy,-4, '3/1/'+ cast(YEAR(@today) as varchar(4))) end

declare @solnone char(5) declare @solnoneMin varchar(50) declare @solnoneMax varchar(50)

set @solnone = '00000' set @solnoneMin = 0 set @solnoneMax = 0

----------------------------------------------------------------------

--Non

insert INTO millwork.dbo.AllSep10

SELECT distinct nameid, rtrim(nameformn) as nameformn, rtrim(addrline1) as addrline1,
rtrim(addrline2) as addrline2, rtrim(addrline3) as addrline3, rtrim(addrcity) as addrcity,
rtrim(addrplace) as addrplace, rtrim(addrzipcod) as addrzipcod, rtrim(addrcntry) as addrcntry, rtrim(namesalut) as namesalut,
mrg.gifteffdat as lastGiftDate, mrg.giftdeduct as lastGift, @appeal as appeal, @description as description

FROM dbo.corebio
INNER JOIN dbo.address WITH (NOLOCK) ON coreid = addrid
INNER JOIN dbo.name WITH (NOLOCK) ON nameid = coreid
INNER JOIN millwork.dbo.mostRecentGift mrg on mrg.giftid = nameid
INNER JOIN dbo.memories memLCt WITH (NOLOCK) ON memLCt.memid = coreid and memLCt.memname='LifeCt'
INNER JOIN
(SELECT g.giftid, SUM(g.giftdeduct) AS cumGift
FROM dbo.gifts g WITH (NOLOCK)
INNER JOIN millwork.dbo.mostRecentGiftPriorFY11 mrg ON mrg.giftid = g.giftid
WHERE g.gifteffdat >= DATEADD(yy,-3,@startFYM3)
AND g.gifteffdat < DATEADD(yy,-2,@startFYM3)
AND mrg.gifteffdat < DATEADD(yy,-2,@startFYM3)
AND mrg.gifteffdat >= DATEADD(yy,-3,@startFYM3)
GROUP BY g.giftid) as cumGiving on cumGiving.giftid = coreid

INNER JOIN (select @solnone as solicitation, @solnoneMin as minVal, @solnoneMax as maxVal

WHERE nametype = 'a'
AND addrmc1 = 'Y'
AND addrlocatr not in ('l ','r','form','d','am')
AND addrmc2 = 'N'
AND nameid not in (select distinct attrid from dbo.attribute where attrtype in ('59-102','59-193') and attrstop is null)--excludes Elec Fund Transfer Donor and Credit Card Donor gifts
AND nameid not in (select distinct attrid from dbo.attribute where attrtype in (
'59-636',
'59-189',
'59-095',
'59-099'
))
AND coreconst in ('I','T','B')
AND addrcntry = 'USA'
AND cumGift <= 0.00
AND corecrdate< DATEADD(yy,-2,@startFYM3)
AND mrg.gifteffdat>=DATEADD(yy,-3,@startFYM3)
AND memamount = 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-20 : 18:27:09

[code]
INNER JOIN (select @solnone as solicitation, @solnoneMin as minVal, @solnoneMax as maxVal
FROM <table>
WHERE nametype = 'a'
AND addrmc1 = 'Y'
AND addrlocatr not in ('l ','r','form','d','am')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-20 : 18:37:02

oye...did you inherit this?

why are you using NOLOCK?

my only advice

Start small

build little pieces...then put the pieces together so you can make sure each one is working

BUT

It seems like a mess

Can you give us, in a business requirement terms, what this is suppose to do?

Oh, and always list the columns on the INSERT INTO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

LOLCatLady
Starting Member

24 Posts

Posted - 2010-09-21 : 09:33:59

quote:
Originally posted by X002548

oye...did you inherit this?

why are you using NOLOCK?

my only advice

Start small

build little pieces...then put the pieces together so you can make sure each one is working

BUT

It seems like a mess

Can you give us, in a business requirement terms, what this is suppose to do?

Oh, and always list the columns on the INSERT INTO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

I did inherit this. I have some experience with sql but this query (and others) have been beyond it. I don't know why NOLOCK is being used.

The purpose of this query is to return all individuals in the database who have never donated (I work for a non-profit org.) The table where memamount is located has records with 0 indicating no gift. That's why I can't understand why the query won't work.

Thanks for your reminder to start small, in pieces, that's what I usually do, but since this query was already written I thought I'd be able to modify it to get it to work. Good idea about adding the columns to INSERT INTO.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources