日期:2014-05-19  浏览次数:20411 次

为什么这句话会报错?
这句话报列名   'MCYL '   无效,为什么?

select
           
              DateStart   as   FYRQ   ,
              PharmacologicalCode   as     XMBH,
              dbo.h_DocAdviceItems.ChargeItemName   as   XMMC,
              dbo.h_DocAdviceItems.FeeTypeID   as   FLDM,
              dbo.h_DocAdviceItems.Specification   as   YPGG,
              dbo.h_DocAdviceItems.RetailPrice   as   JG,
              [MCYL]=
                        case   when   dbo.h_DocAdviceItems.FeeTypeID   =14   then   cast((DateStopped   -DateStart   )   as   int)
                                  when   dbo.h_DocAdviceItems.FeeTypeID <> 14   then   cast((DateStopped   -DateStart   )   as   int)*dbo.h_DocAdviceItems.DailyFrequency
                        end   ,
            JE   =   dbo.h_DocAdviceItems.RetailPrice*[MCYL]
from   dbo.h_DocAdviceItems   inner   join   dbo.b_ChargeItems  
          on   dbo.h_DocAdviceItems.ChargeItemID=dbo.b_ChargeItems.ChargeItemID
where   DocAdviceID=1158


------解决方案--------------------
在MS SQL中,計算列不能直接拿來用。
------解决方案--------------------
select

DateStart as FYRQ ,
PharmacologicalCode as XMBH,
dbo.h_DocAdviceItems.ChargeItemName as XMMC,
dbo.h_DocAdviceItems.FeeTypeID as FLDM,
dbo.h_DocAdviceItems.Specification as YPGG,
dbo.h_DocAdviceItems.RetailPrice as JG,
[MCYL]=
case when dbo.h_DocAdviceItems.FeeTypeID =14 then cast((DateStopped -DateStart ) as int)
when dbo.h_DocAdviceItems.FeeTypeID <> 14 then cast((DateStopped -DateStart ) as int)*dbo.h_DocAdviceItems.DailyFrequency
end ,
JE = dbo.h_DocAdviceItems.RetailPrice*
( case when dbo.h_DocAdviceItems.FeeTypeID =14 then cast((DateStopped -DateStart ) as int)
when dbo.h_DocAdviceItems.FeeTypeID <> 14 then cast((DateStopped -DateStart ) as int)*dbo.h_DocAdviceItems.DailyFrequency
end)
from dbo.h_DocAdviceItems inner join dbo.b_ChargeItems
on dbo.h_DocAdviceItems.ChargeItemID=dbo.b_ChargeItems.ChargeItemID
where DocAdviceID=1158
------解决方案--------------------
或者

Select
*,
JG * MCYL As JE
From
(
select

DateStart as FYRQ ,
PharmacologicalCode as XMBH,
dbo.h_DocAdviceItems.ChargeItemName as XMMC,
dbo.h_DocAdviceItems.FeeTypeID as FLDM,
dbo.h_DocAdviceItems.Specification as YPGG,
dbo.h_DocAdviceItems.RetailPrice as JG,
[MCYL]=
case when dbo.h_DocAdviceItems.FeeTypeID =14 then cast((DateStopped -DateStart ) as int)
when dbo.h_DocAdviceItems.FeeTypeID <> 14 then cast((DateStopped -DateSt