如何为在SQl中使用case语句创建的列编写where子句 [英] how to write a where clause for columns created using case statements in SQl

问题描述

这是Pavan我有一个SQL查询，它使用通过加入像这样的差异表来绑定的case语句创建的列......



< pre lang =SQL> 选择 ' S.No ' as ' S.no',TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.int_ADP_VIOLATION_WITHDRAWAL_ID as [ID]，
ISNULL（ convert （ varchar ，TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_4262_OF， 106 ），' '） as [已提交开启]，
p.str_GeneratePassID [Permit Number]，

case when av.str_type = ' C' 然后（
选择 STR_2116_OF 来自 TRANS_Contractor_Contractor_Details 其中 int_Contractor_ID
（ select str_ObjectID < span class =code-keyword>来自 STANDARD_EMPLOYEES 其中 int_STANDARD_ID = av.str_empno））
当 av.str_type = ' E' 然后 ' BIAL';
end as [Company]，

（选择 str_text 来自 CONFIG_TYPE_OF_ADP 其中 smallint_id in （选择 smallint_3936_ME 来自
trans_inspections_sections_33 其中 int_INSPECTION_ID
（ select str_checklistObjectID < span class =code-keyword> from MATRIX_INSPECTION_CHECKLIST_RELATION 其中
smallint_CategoryID = 33 和 str_objectNo = av.str_objectNo）））[ADP Type ]，

ISNULL（强制转换（TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.str_4046_OF as varchar ），' '） as [ADP取款号码]，
ISNULL（ convert （ varchar ，TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_4039_OF， 106 ），' '） as [日期 WithDrawal]
来自 TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL left 外部 join MATRIX_VIOLATION_WITHDRAWAL_RELATIONSHIP [w]
on w.int_withdrawalid = TRANS_ADP_Violation_Withdrawal_ADP_Violation_Withdrawal.int_ADP_Violation_Withdraw al_ID
left 外部 加入 上的MATRIX_ADP_AVP_AEP_PASS_ISSUE_NUMBERS [p] p.int_MatrixID = w.str_Objectid
left 外部 join MATRIX_AVIATION_SAFETY_APPLICANT_DETAILS [av] av.str_ObjectNo = p。 str_RequestID
其中 w.int_PluginID = 58

order by TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_modifiedOn desc

我们可以为公司和AdpType列写一个where子句，如果是这样的话，请提前说明谢谢..........

解决方案

你好pavan，

贝娄我举一个例子你可以做山姆e更改

 选择 * 来自（
 选择 案例 departmentid 当  9  然后 '  A'    6  然后 '  B' 当  12  然后 '  c'  end   as  [dpt]，* 来自 bugsmaster）bug 其中​​ [dpt] = '   A' 

您需要查询的位置内部选择如下：



选择*来自（------）

用------替换你的查询对where子句使用相同的别名列。



干杯

Yoour查询有点复杂，所以我建议你从这里开始： WHERE子句（T-SQL） [ ^ ]。



示例：

  SELECT  ...< fields collection> 
  FROM  ...< table collection> 
  WHERE  w.int_PluginID = 58  AND  TableAlias.Company = ' '  AND  TableAlias.AdpType =  123  

Hi,this is Pavan I have an sql query which cantains columns created using case statements bound by joining diff tables like this......

select 'S.No' as 'S.no',TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.int_ADP_VIOLATION_WITHDRAWAL_ID as [ID] ,
ISNULL(convert(varchar, TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_4262_OF,106),'') as [Submitted On] ,
p.str_GeneratePassID[Permit Number],

case when av.str_type='C' then (
select STR_2116_OF  from  TRANS_Contractor_Contractor_Details where int_Contractor_ID in
(select str_ObjectID from STANDARD_EMPLOYEES where int_STANDARD_ID = av.str_empno))
When av.str_type= 'E' then 'BIAL';
end  as [Company],

(select str_text from CONFIG_TYPE_OF_ADP  where smallint_id in (select smallint_3936_ME from
trans_inspections_sections_33 where int_INSPECTION_ID in
(select str_checklistObjectID from MATRIX_INSPECTION_CHECKLIST_RELATION where
smallint_CategoryID = 33 and str_objectNo = av.str_objectNo))) [ADP Type] ,

ISNULL(cast( TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.str_4046_OF as varchar),'') as [ADP Withdrawal No],
ISNULL(convert(varchar, TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_4039_OF,106),'') as [Date Of WithDrawal]
 from TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL left outer join MATRIX_VIOLATION_WITHDRAWAL_RELATIONSHIP[w]
 on w.int_withdrawalid=TRANS_ADP_Violation_Withdrawal_ADP_Violation_Withdrawal.int_ADP_Violation_Withdrawal_ID
left outer join MATRIX_ADP_AVP_AEP_PASS_ISSUE_NUMBERS[p] on p.int_MatrixID=w.str_Objectid
left outer join MATRIX_AVIATION_SAFETY_APPLICANT_DETAILS[av] on av.str_ObjectNo=p.str_RequestID
 where  w.int_PluginID=58 

order by TRANS_ADP_VIOLATION_WITHDRAWAL_ADP_VIOLATION_WITHDRAWAL.dt_modifiedOn desc

can we write a where clause for the columns Company and AdpType if so plz Explain thanks in advance..........

解决方案

Hello pavan ,
Bellow i am giving one example You can make same changes

select * from(
select case departmentid when 9 then'A' when 6 then 'B' when 12 then 'c' end as [dpt], * from bugsmaster) bug where [dpt]='A'

Where you need to put your query inside select as bellow

Select * from (------)
Replace your query with ------ and use the same alias column for your where clause.

Cheers

Yoour query is a bit complicated, so i would suggest you to start here: WHERE clause (T-SQL) [^].

Example:

SELECT ...<fields collection>
FROM ...<table collection>
WHERE w.int_PluginID=58 AND TableAlias.Company = '' AND TableAlias.AdpType = 123

这篇关于如何为在SQl中使用case语句创建的列编写where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！