MUST DECLARE THE SCLAR VARIABLE "@STARTDATE"

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 2005 Forums

Transact-SQL (2005)

MUST DECLARE THE SCLAR VARIABLE "@STARTDATE"

Author

Topic

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-10 : 15:55:33

Good Afternoon,

I am having trouble with my query that I wrote. I added IN(@PILOTNAME) in my WHERE clause to input a dropdwn menu. I am recieving a error when I try to run the following code:

SELECT CASE WHEN INS_PRODUCT = 'FB' THEN 'FPCC' WHEN INS_PRODUCT = 'PS' THEN 'PCCC' WHEN INS_PRODUCT = ' HB' THEN 'HBCC' WHEN INS_PRODUCT
= 'Outreach' THEN 'CVCC' ELSE CAST(INS_PRODUCT AS varchar(20)) END AS Split, SR_AREA, SR_SUB_AREA, COUNT(SR_AREA) AS Total_SR,
COUNT(SR_SUB_AREA) AS Total_SubArea
FROM S_SRV_REQ
WHERE (INS_PRODUCT IS NOT NULL) AND (CREATED >= @Startdate) AND (CREATED < DATEADD(d, 1, @Enddate)) AND (SR_AREA IS NOT NULL) AND
(INS_PRODUCT IN ('FB', 'PS', 'HB', 'Outreach')) AND (SR_SUB_AREA IS NOT NULL) IN(@PILOTNAME)
GROUP BY INS_PRODUCT, SR_AREA, SR_SUB_AREA
ORDER BY PILOT

I am utilizing Microsoft Visual Studio (Report Services)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 16:39:41

Where do @StartDate variable or parameter derive from?

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

sergeant_time
Yak Posting Veteran

73 Posts

Posted - 2010-02-10 : 16:42:54

I have no clue. This is the dataset that goes along with the above code;

SELECT 'FP' AS Pilot, 'FPCC' AS PilotName, 1 AS OrderID
UNION ALL
SELECT 'HB' AS Pilot, 'HBCC' AS PilotName, 2 AS OrderID
UNION ALL
SELECT 'PS' AS Pilot, 'PCCC' AS PilotName, 3 AS OrderID
UNION ALL
SELECT 'Outreach' AS Pilot, 'CVCC' AS PilotName, 4 AS OrderID
ORDER BY OrderID

The IN(@PILOTNAME)) is supposed to be pulling the data in the above code

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 16:55:42

Your syntax is screwed.

SELECT		CASE INS_PRODUCT
			WHEN 'FB' THEN 'FPCC'
			WHEN 'PS' THEN 'PCCC'
			WHEN 'HB' THEN 'HBCC'
			WHEN 'Outreach' THEN 'CVCC'
		END AS Split,
		SR_AREA,
		SR_SUB_AREA,
		COUNT(SR_AREA) AS Total_SR, 
		COUNT(SR_SUB_AREA) AS Total_SubArea
FROM		S_SRV_REQ
WHERE		INS_PRODUCT IS NOT NULL
		AND CREATED >= @StartDate
		AND CREATED < DATEADD(d, 1, @EndDate)
		AND SR_AREA IS NOT NULL
		AND INS_PRODUCT IN ('FB', 'PS', 'HB', 'Outreach')
		AND SR_SUB_AREA IS NOT NULL
		AND PILOT IN (@PILOTNAME)
GROUP BY	INS_PRODUCT,
		SR_AREA,
		SR_SUB_AREA
ORDER BY	PILOT

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources