format sql error. target database is 'MS SQL Server' detail message is :
unexpect function, function name is 'datepart'
source sql is :
Select B.*,ROW_NUMBER() OVER(ORDER BY FIDENTITYID_SEQ) FIDENTITYID INTO TMP47D2B109DE1611E694150894EF1 FROM (Select A.*,1 FIDENTITYID_SEQ FROM (select
-- F_BAM_SALORG,t_target.F_BAM_MATERIAL,t_target.F_BAM_CUST,t_target.F_BAM_SALER,t_target.F_BAM_BASEUNITID,
orgl.FNAME as 销售组织,ma.FNAME as 物料名称,custl.FNAME as 客户名称,empl.FNAME as 销售员,unl.FNAME as 单位,
FBASEQTY as 流向数量,f_bam_fstprice as 第一目标单价,F_BAM_SECPRICE as 第二目标单价,FBASEQTY/F_BAM_FIRSTYEARQTY*100 AS [第一目标完成率(%)],
FBASEQTY/F_BAM_YEARQTY*100 AS [第二目标完成率(%)],
case when FBASEQTY>=F_BAM_YEARQTY then F_BAM_SECPRICE*t_real.FBASEQTY
else f_bam_fstprice*t_real.FBASEQTY end as OTC返利金额
from
(select F_BAM_SALORG,pro_e.F_BAM_MATERIAL,PRO_H.F_BAM_CUST,pro_h.F_BAM_SALER,pro_e.F_BAM_BASEUNITID,
max(pro_e.f_bam_fstprice) as f_bam_fstprice,max(F_BAM_SECPRICE) as F_BAM_SECPRICE,max(F_BAM_FIRSTYEARQTY) as F_BAM_FIRSTYEARQTY,
max(F_BAM_YEARQTY) as F_BAM_YEARQTY,sum(pro_chk.F_BAM_QTY) as fchkqty,MAX(F_BAM_STARTDATE) as F_BAM_STARTDATE,MAX(F_BAM_ENDDATE) as F_BAM_ENDDATE
from BAM_t_OTC_PROTOCOL PRO_H
INNER JOIN BAM_t_OTC_PROTOCOLEntry pro_e on pro_e.FID=PRO_H.FID
inner join BAM_t_OTC_PROTOCOLChk pro_chk on pro_e.FEntryID=pro_chk.FEntryID
where pro_chk.F_BAM_YEAR=2016 and pro_chk.F_BAM_ISCOMPUTE=1
and PRO_H.FDOCUMENTSTATUS='C' and PRO_H.FBILLTYPEID='5859500ff593eb' --只取OTC连锁协议
and (F_BAM_FSTPRICE<>0 or F_BAM_SECPRICE<>0)
group by F_BAM_SALORG,pro_e.F_BAM_MATERIAL,PRO_H.F_BAM_CUST,pro_h.F_BAM_SALER,pro_e.F_BAM_BASEUNITID) as T_Target
inner join
--查询当年实际产生的有效流量数据
(
select F_BAM_MATIRIAL,F_BAM_CUSTOMER,F_BAM_SALER,F_BAM_SALEUNITID,SUM(F_BAM_BASEQTY) as FBASEQTY
from BAM_t_OTC_FLOWENTRY
where FEntryID in
(select distinct E.FEntryID --,F_BAM_CUSTOMER,F_BAM_SALEDATE,F_BAM_MATIRIAL
from BAM_t_OTC_FLOW H INNER JOIN BAM_t_OTC_FLOWENTRY E ON H.FID=E.FID
INNER JOIN BAM_t_OTC_PROTOCOLEntry pro_e on pro_e.F_BAM_MATERIAL=e.F_BAM_MATIRIAL
INNER JOIN BAM_t_OTC_PROTOCOL PRO_H on pro_e.FID=PRO_H.FID
inner join BAM_t_OTC_PROTOCOLChk pro_chk on pro_e.FEntryID=pro_chk.FEntryID and pro_chk.F_BAM_MONTH=datepart(MM,e.F_BAM_SALEdate)
and pro_chk.F_BAM_YEAR=datepart(YY,e.F_BAM_SALEdate)
where h.FDOCUMENTSTATUS='C' and h.F_BAM_YEAR=2016
and h.F_BAM_YEAR=pro_chk.F_BAM_YEAR AND PRO_CHK.F_BAM_IsCompute=1
and PRO_H.FDOCUMENTSTATUS='C' and e.F_BAM_CUSTOMER=PRO_H.F_BAM_CUST
and e.F_BAM_SALEDATE between pro_h.F_BAM_STARTDATE and pro_h.F_BAM_ENDDATE
)
group by F_BAM_MATIRIAL,F_BAM_CUSTOMER,F_BAM_SALER,F_BAM_SALEUNITID
) t_real
on T_Target.F_BAM_MATERIAL=t_real.F_BAM_MATIRIAL and t_target.F_BAM_CUST=t_real.f_bam_customer and
t_target.F_BAM_SALER=t_real.F_BAM_SALER and t_target.F_BAM_BASEUNITID=t_real.F_BAM_SALEUNITID
inner join T_ORG_ORGANIZATIONS_L orgl on orgl.FORGID=T_Target.F_BAM_SALORG and orgl.FLOCALEID=2052
inner join T_BD_MATERIAL_L ma on ma.FMATERIALID=T_Target.F_BAM_MATERIAL and ma.FLOCALEID=2052
inner join T_BD_CUSTOMER_L custl on custl.FCUSTID=T_Target.F_BAM_CUST and custl.FLOCALEID=2052
inner join T_HR_EMPINFO_L empl on empl.FID=T_Target.F_BAM_SALER and empl.FLOCALEID=2052
inner join T_BD_UNIT_L unl on unl.FUNITID=T_Target.F_BAM_BASEUNITID and unl.FLOCALEID=2052) A) B
以上的sql语句中查询分析内运行正常，但是在bos设计器中选择msssql测试时也报错：
format sql error. target database is 'MS SQL Server' detail message is :
unexpect function, function name is 'datepart'
，改用ksql进行测试时正常，但是运行这个直接sql账表时报错。
版本:cloud6+2016年12月补丁