如何将变量(别名)传递到where条件SQL

Question

大家好，

我想传递'coalesce(MDT.DeptName,DD5.Name) as departname'到where条件。现在错误显示无效列。有可能吗?下面是我的代码:

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 

我想在c#中使用这段代码。请告知

Answer 1

不能在Where子句中使用Alias name。使用原始列

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

这是逻辑上处理查询的方式

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

由于Where子句在Select之前处理，因此Where子句中不会有Alias的名称