I feel like my last post [http://ww

[timmoser]

I feel like my last post thread183-738120 had to much info. So here is the short of it. When I add the datepart function to the query it fails. The error I get is:


Server: Msg 8630, Level 17, State 38, Line 1
Internal Query Processor Error: The query processor encountered an unexpected error during execution.


This works...

SELECT z.uid, datepart(year,z.week_date) as totyear,
ISNULL(SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday),0) AS total,
holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE (a.activity = 'Holiday' OR a.activity = 'Floating Holiday') AND uid = @uid ),0)
FROM it_tt_activity z
WHERE z.uid=@uid
GROUP BY z.uid, datepart(year,z.week_date)


This does not...

SELECT z.uid, datepart(year,z.week_date) as totyear,
ISNULL(SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday),0) AS total,
holiday = ISNULL((SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE (a.activity = 'Holiday' OR a.activity = 'Floating Holiday') AND uid = '23061' AND datepart(year,a.week_date)=datepart(year,z.week_date) ),0)
FROM it_tt_activity z
WHERE z.uid='23061'
GROUP BY z.uid, datepart(year,z.week_date)


Both work in Sybase which is also TSQL. The only difference is the "datepart" functions in the "where" statements.

I apologize for posting this twice. I will make sure I reference this in the old one.

Thanks for your help.

 

[SonOfEmidec1100]

Not sure this is the prob but in this select you are referencing z.week_date but z isnt in the from clause.

SELECT SUM(sunday+monday+tuesday+wednesday+thursday+friday+saturday)
FROM it_tt_activity a
WHERE (a.activity = 'Holiday' OR a.activity = 'Floating Holiday') AND uid = '23061' AND datepart(year,a.week_date)=datepart(year,z.week_date)

 

[timmoser]

Thanks "SonOfEmidec1100" but "z" is part of the parent query.

 

[SonOfEmidec1100]

Sorry bit confused, so I tested on my setup (sql2000sp3) no problem. I found a few references to this error searching MDSN for the string "Internal Query Processor Error"

Which version of sql and sp are you using?

 

[timmoser]

Humm, good point, I didn't realize this server is running 7.
MSSQL 7.00.1063 Standard on Windows 2000 Server

Wonder if it has all of the service packs? I'm going to check that now.

Thanks for your help!