Writing a CASE statement within WHERE clause? 1

[sconti11]

I am trying to get the following code to work within a ssrs dataset:

SELECT COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM WRK_ORDER
WHERE (MONTH(dateCreated) = CASE @Month WHEN NULL THEN
(SELECT MONTH(MAX(dateCreated)) AS mMonth
FROM WRK_ORDER AS WRK_ORDER_1
WHERE (YEAR(dateCreated) = @RAAG_Year) AND (franchiseID = @franchise)) ELSE @Month END)
GROUP BY franchiseID, YEAR(dateCreated)

But I continue to get 'Incorrect Parameter' error.

I know it has something to do with the CASE statement and the @Month parameter...but I can not figure out what it is?

Basically I want to check the parameter and if it is NULL then use the value from the subquery else use the parameter value.

Anybody done something like this?

 

[xlbo]

try removing the AS mMonth


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sconti11]

Still does not work...

What I am attempting to do with this case statement within the where clause, is to capture a ytd value when the user does not select a month parameter.

This is because we do not have a true time dimension set up on the database.

Any other ideas?

 

[xlbo]

Hmmm - silly question but your parameter is actuually called Month isn't it? you can have a label value as well as a name for a parameter...otherwise, the syntax seems sound...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[sconti11]

I am getting the following error:

Parameter 3 ([HMC_DW].[].[uknown]): The CLR type does not exist or you do not have permissions to access it.

Not sure what this means???

 

[xlbo]

Sounds like a "code behind" error...how is the report being invoked? or is this in design mode? Is teh parameter set as "hidden" or "internal" ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question