Oracle CASE WHEN 'null' to work with an Int for a SSRS report

Question

I have a query that is being filtered by res_id. This query is being used to fetch the data for a SSRS report. This is doing the job however I would like to be able to select the 'allow null' tick box on the parameter properties, rather than entering a 0. My res_id is an Int.

SELECT Employee
FROM Employees  
WHERE res_id = case when :ResID = 0 THEN res_id ELSE :ResID END 

I have tried the following but I get a, 'Expected Number, got a CHAR' error.

WHERE res_id = case when :ResID = 'null' THEN res_id ELSE :ResID END 

WHERE res_id = case when :ResID IS NULL THEN res_id ELSE :ResID END

I believe the issue is within my report builder for which I am using Visual Studio 2008. I am aware the code translation between Oracle and the report can be a bit hit and miss sometimes so that's why I am asking for help on another approach.

Thanks all for your help in advance :)

Answer 1

You could also try

where res_id=nvl(:ResID,res_id)