如何在SQL Server 2012中更改此查询 [英] How to change this query in SQL server 2012

问题描述

SELECT b.trn_sno，b.bill_date，b.bill_bno，b.pt_name，b.age，b.sex，iif（d.doct_nm Is Null，''，d.doct_nm） ，& _ 
b.amount，iif（b.chk_free = 1，b.amount，0），iif（b.chk_free = 1,0，b.amount），iif（iif（b.app_ref为null， 0，b.app_ref）= 1，iif（iif（b.rec_ref为null，0，b.rec_ref）= 1，'Refunded'，'Approved for Refund'），''）FROM rec_all as b LEFT JOIN医生as d ON b.doct_id = d.doct_id WHERE& txtfld& 和& _ 
b.rw = 0 and b.co_code ='& CoCode& 'ORDER BY b.r​​ec_ref，b.bill_date，b.trn_sno

我尝试过：



MS ACCESS QUERY到SQl Server 2012更改

解决方案

 选择 b.trn_sno，b.bill_date，b.bill_bno，b.pt_name，b.age，b.sex，
 isnull（d.doct_nm，< span class =code-string>' '） as  doct_nm， case  何时 b.chk_free = 1 然后 b.amount  else   0   end   as  b.amount， case  何时 b.chk_free = 1 < span class =code-keyword>   0   else  b.amount  end   as  b.amount，
  case  当 isnull时（b.app_ref， 0 ）= 1 那么（ case  当 isnull时（b.rec_ref， 0 ）= 1 然后 ' 退款' 其他 ' 批准退款'   end ） else  ' '  end   FROM  rec_all  as  b  LEFT   JOIN  doctor  as  d  ON  b.doct_id = d.doct_id  WHERE   & txtfld&  和  & _ 
 b.rw = 0 和 b.co_code = ' < span class =code-string>& CoCode& '  ORDER   BY  b.rec_ref，b.bill_date，b.trn_sno 

"SELECT b.trn_sno, b.bill_date, b.bill_bno, b.pt_name, b.age, b.sex, iif(d.doct_nm Is Null,'',d.doct_nm), " & _
"b.amount, iif(b.chk_free=1,b.amount,0), iif(b.chk_free=1,0,b.amount), iif(iif(b.app_ref is null,0,b.app_ref)=1,iif(iif(b.rec_ref is null,0,b.rec_ref)=1,'Refunded','Approve for Refund'),'') FROM rec_all as b LEFT JOIN doctors as d ON b.doct_id=d.doct_id WHERE " & txtfld & " and " & _
"b.rw=0 and b.co_code='" & CoCode & "' ORDER BY b.rec_ref, b.bill_date, b.trn_sno"

What I have tried:

MS ACCESS QUERY TO SQl Server 2012 change

解决方案

select b.trn_sno, b.bill_date, b.bill_bno, b.pt_name, b.age, b.sex,
       isnull(d.doct_nm,'') as doct_nm,case when b.chk_free=1 then b.amount else 0 end as b.amount,case when b.chk_free=1 then 0 else b.amount end as b.amount,
case when isnull( b.app_ref,0)=1 then (case when isnull(b.rec_ref,0)=1 then 'Refunded' else 'Approve for Refund' end ) else '' end  FROM rec_all as b LEFT JOIN doctors as d ON b.doct_id=d.doct_id WHERE " & txtfld & " and " & _
"b.rw=0 and b.co_code='" & CoCode & "' ORDER BY b.rec_ref, b.bill_date, b.trn_sno

这篇关于如何在SQL Server 2012中更改此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！