Sql Case statment filter issue for ssrs-CodePudding

I have code that i want to use on ssrs filter and not working here is the Query

 Select empid, empName, 
 Case WHEN HR.Pos=1 THEN 'MANAGER'
 WHEN  HM.POS=2 THEN  'HR MANAGER'
 WHEN   HM.POS=3 THEN  'AC MANAGER'
 ELSE  'IT Manager' END [Position]
 from dbo.tbl_Emp  e
 LEFT JOIN  dbo.pos pos ON e.id=pos.id
 LEFT JOIN  dbo.HM HM on  e.id=HM.id
 where [Position]='HR MANAGER'

temp# Position not recognized is there another way to do this

CodePudding user response：

Your query can be significantly simplified here to the following:

SELECT e.empid, --Assumed qualifier
       e.empName, --Assumed qualifier
       'HR MANAGER' AS Position
FROM dbo.tbl_Emp e
     JOIN dbo.HM ON e.id=HM.id
WHERE HM.Pos = 2;

Firstly, I removed the CASE expression, as it'll only ever resolve to 'HR MANAGER'; so might as well just write 'HR MANAGER'.

I also remove the LEFT JOIN to dbo.pos as I can't see that it's used, and therefore it does nothing (apart from possibly causing duplicate rows, which I doubt is desired). Note that I do assume that the first 2 columns are from dbo.tbl_Emp, so if this assumption is wrong, you may need to address this.

Next I change your LEFT JOIN to dbo.HM to an INNER JOIN, and remove the alias. The alias was pointless as you alias HM to HM; it did nothing. The INNER JOIN because you require that HM.Pos have a non-NULL value in the WHERE. It's impossible for HM.Pos to have a non-NULL value if no row was found. As such it should be an INNER JOIN.

Then, finally, I change the WHERE to reference the column in the table HM, not an expression defined in the SELECT, which can't be referenced at that point.

CodePudding user response：

I'm assuming that you wan to pass, in this case, "HR Manager" to the dataset query from a parameter. If that is true then you need to setup your parameter to match your HM table.

Ultimately, your parameter values would be 1,2,3,4 and respective labels would be "Manager", "HR Manager", "AC Manager", "ITManager". The user would see the text(label) but you pass the value to the query.

You will need to change the WHERE clause to somethingg like

WHERE HM.Pos = @myParamName