Syntax Error causing Dementia - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Syntax Error causing Dementia

Author

Topic

cfaheybestpitch
Starting Member

3 Posts

Posted - 2004-09-04 : 13:33:05

Hi All,

I'm hoping someone can help my partner and I with a tricky query that keeps giving us a syntax error "near the keyword aND". We have tried so many different ideas our minds are mush.

We have tried eliminating our while loop, substituting SET for SELECT when establishing variables, counting parenthesis pairs, and many other things, but nothing prevents the ambiguous syntax error. The only thing we can imagine is we've made a mistake in our cast statements, but it's beyond us what it could be.

You can view the page (error) here: http://staging.dgb-online.com/analysis/medical_trend.cfm

Following is the query:

declare @months numeric
declare @counter numeric
declare @this_month numeric
declare @this_year numeric

select COUNT(ehnmbr) as month_tot,
rnefdt as date_ref,
rnefdt,
ehgpn2,
rnchdt
from ben_codes, ben_hist, gr_info
where ben_hist.ehgpn2 = ben_codes.gbgpno
and ben_hist.ehembc = ben_codes.gbclas
and ben_hist.ehgpn2 = gr_info.rnricd
and (gbbncl_table_1 = 'M '
or gbbncl_table_2 = 'M '
or gbbncl_table_3 = 'M '
or gbbncl_table_4 = 'M '
or gbbncl_table_5 = 'M '
or gbbncl_table_6 = 'M '
or gbbncl_table_7 = 'M '
or gbbncl_table_8 = 'M '
or gbbncl_table_9 = 'M '
or gbbncl_table_10 = 'M '
or gbbncl_table_11 = 'M '
or gbbncl_table_12 = 'M '
or gbbncl_table_13 = 'M '
or gbbncl_table_14 = 'M '
or gbbncl_table_15 = 'M '
or gbbncl_table_16 = 'M '
or gbbncl_table_17 = 'M '
or gbbncl_table_18 = 'M '
or gbbncl_table_19 = 'M '
or gbbncl_table_20 = 'M '
or gbbncl_table_21 = 'M '
or gbbncl_table_22 = 'M '
or gbbncl_table_23 = 'M '
or gbbncl_table_24 = 'M '
or gbbncl_table_25 = 'M '
or gbbncl_table_26 = 'M '
or gbbncl_table_27 = 'M '
or gbbncl_table_28 = 'M '
or gbbncl_table_29 = 'M '
or gbbncl_table_30 = 'M ')

IF (left(rnefdt, 4) < left(rnchdt, 4))
begin
select @months = (12- cast(substring(rnefdt, 5,2) as int) + cast(substring(rnchdt, 5,2) as int))
end
else
begin
select @months = (cast(substring(rnchdt, 5,2) as int) - cast(substring(rnefdt, 5,2) as int))
end

select @counter = 1
while (@counter < @months)
Begin
if (cast(substring(rnefdt, 5,2) as int) + @counter > 12)
begin
select @this_month = @counter - 12
select @this_year = cast(left(rnefdt, 4) as int) + 1
end
else
begin
select @this_month = @counter
select @this_year = cast(left(rnefdt, 4) as int)
end

/* ################################# */
/* HERE's WHERE THE ERROR SAYS IT IS 'NEAR' */
/* ################################# */

aND (
(cast(left(ehstdt, 4) as char(4)) + cast(substring(ehstdt,5,2) as char(2)) <= cast(@this_year as char(4)) + cast(@this_month as char(2))
AND cast(left(ehtodt, 4) as char(4)) + cast(substring(ehtodt,5,2) as char(2)) >= cast(@this_year as char(4)) + cast(@this_month as char(2)))
OR (cast(left(ehstdt, 4) as char(4)) + cast(substring(ehstdt,5,2) as char(2)) = cast(@this_year as char(4)) + cast(@this_month as char(2)))
)
AND cast(left(eeitdt, 4) as char(4)) + cast(substring(eeitdt,5,2) as char(2)) > cast(@this_year as char(4)) + cast(@this_month as char(2))
AND cast(left(eeftdt, 4) as char(4)) + cast(substring(eeftdt,5,2) as char(2)) > cast(@this_year as char(4)) + cast(@this_month as char(2))

select @counter = @counter + 1
end

group by rnefdt, ehgpn2

Thanks for all your help!

- Charles

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-04 : 15:32:07

You cant have IF statements and WHILE loops inside the
WHERE ...
clause

To replace the IF use a CASE statement.
To replace the WHILE loop, rewrite.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

cfaheybestpitch
Starting Member

3 Posts

Posted - 2004-09-04 : 15:46:48

Hi Rockmoose,

Wow. That sounds like something we should have known.

Thank you so much! We will try it out.

- Charles

quote:
Originally posted by rockmoose

You cant have IF statements and WHILE loops inside the
WHERE ...
clause

To replace the IF use a CASE statement.
To replace the WHILE loop, rewrite.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-04 : 16:04:18

I guess I didn't do much except pointing out the obvious,
but thanks

The query seems quite a mouthful, maybe you can chunk it out bit by bit.
Perhaps using 2 simpler queries with UNION ALL

Well any way, good luck.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-04 : 18:40:04

A bigger problem is the design.
Get rid of all the gbbncl_table_n's and put them in another table linking on the PK.
You will find queries a lot simpler and probably faster.

Not sure what it's trying to do but all the date manipulation looks like it could be helped by a better design too.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources