user3538475 user3538475 - 2 months ago 16
C# Question

How to pass variable (Alias) into where condition SQL

Goodday to all,

I want pass 'coalesce(MDT.DeptName,DD5.Name) as departname' into where condition. Now the error shows invalid column. Any possibilties ?
below is my code:

SELECT op_id,
DD.NAME Unit,
DD1.NAME Freq,
DD2.NAME Calc,
COALESCE(MDT.deptname, DD5.NAME) AS departname,
COALESCE(CO.yearlytarget + ' ' + dd3.NAME, CO.yearlytarget)AS
YearlyTarget,
CO.pastyearresult,
CO.weight,
CO.project,
CO.description,
Co.datecreated,
MDT.weightvalue
FROM [MBO].[dbo].[m_newcaloprt] CO
LEFT JOIN [MBO].[dbo].[m_ddl] DD
ON CO.unit_ddl = DD.d_id
LEFT JOIN [MBO].[dbo].[m_ddl] DD1
ON CO.freq_ddl = DD1.d_id
LEFT JOIN [MBO].[dbo].[m_ddl] DD2
ON CO.calc_ddl = DD2.d_id
LEFT JOIN [MBO].[dbo].[m_user] US
ON CO.uid = US.id
LEFT JOIN [MBO].[dbo].[m_user] US1
ON US1.deptid = US.deptid
LEFT JOIN [MBO].[dbo].[m_ddl] DD5
ON US1.deptid = DD5.d_id
LEFT JOIN [MBO].[dbo].[m_multipledept] MDT
ON MDT.projectid = CO.op_id
LEFT JOIN [MBO].[dbo].[m_ddl] DD3
ON CO.calcsymbol = DD3.d_id
WHERE departname = ''
AND isactive = 0
AND isverifed = 1


i want use this code into C#. Please advise

Answer

You cannot use Alias name in Where clause. Use the original columns

WHERE  COALESCE(MDT.deptname, DD5.NAME) = '' 
       AND isactive = 0 
       AND isverifed = 1 

This is how a query is logically processed

1. FROM
2. ON
3. OUTER
4. WHERE --here
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT --here
9. DISTINCT
10 ORDER BY
11. TOP

Since Where clause is processed before Select we will not have Alias name in Where clause