首页 新闻 搜索 专区 学院

ora-00936: 缺失表达式

0
悬赏园豆:160 [已解决问题] 解决于 2017-01-13 16:56

我在PLSQL Developer 客户端查询可以出来结果,   visual Studio里OLE DB源里面sql语句会报错 【ora-00936: 缺失表达式】 , 代码如下:

SELECT --产品批次汇总表
to_number(to_char(sysdate,'yyyymm')||'01') dateid
,a1.fname orgname --分支机构
,a.fnumber matnumber -- 产品代码
,b.fname matname --产品名称
,b.fspecification matspec --规格型号
,i.stockname --仓库
,i.fstockstatus stcokstatus --库存状态
,d.fname unit --单位
,h.fprice price --单价
,stocck.fexpperiod fexpperiod --保质期
,i.batchnum batchnum --批次
,(CASE
WHEN instr(b.fname, '雀巢', 1) > 0 THEN
(CASE
WHEN regexp_like(substr(i.batchnum, 1, 6)
,'(\d{2})(0[^0]0[1-9]|0[^0]1[0-9]|0[^0]2[0-8]|1[0-2]0[1-9]|1[0-2]1[0-9]|1[0-2]2[0-8]|0[13-9]29|1[0-2]29|0[13-9]30|1[0-2]30|0[13578]31|1[02]31)|[2468][048]0229|[13579][26]0229') THEN
to_date(substr(i.batchnum, 1, 6), 'yymmdd') - stocck.fexpperiod
WHEN regexp_like(substr(i.batchnum, 1, 8)
,'(\d{3}[^0]|[^0]\d{3})(0[^0]0[1-9]|0[^0]1[0-9]|0[^0]2[0-8]|1[0-2]0[1-9]|1[0-2]1[0-9]|1[0-2]2[0-8]|0[13-9]29|1[0-2]29|0[13-9]30|1[0-2]30|0[13578]31|1[02]31)|\d{2}0[48]0229|\d{2}[2468][048]0229|\d{2}[13579][26]0229|0[48]000229') THEN
to_date(substr(i.batchnum, 1, 8), 'yyyymmdd') - stocck.fexpperiod
ELSE
NULL
END)
ELSE
CASE
WHEN regexp_like(substr(i.batchnum, 1, 8)
,'(\d{3}[^0]|[^0]\d{3})(0[^0]0[1-9]|0[^0]1[0-9]|0[^0]2[0-8]|1[0-2]0[1-9]|1[0-2]1[0-9]|1[0-2]2[0-8]|0[13-9]29|1[0-2]29|0[13-9]30|1[0-2]30|0[13578]31|1[02]31)|\d{2}0[48]0229|\d{2}[2468][048]0229|\d{2}[13579][26]0229|0[48]000229') THEN
to_date(substr(i.batchnum, 1, 8), 'yyyymmdd')
ELSE
NULL
END
END) batchdate --批次日期
,i.fproducedate proddate --生产采购日期
,i.fexpirydate --有效期
,SUM(nvl(i.fbaseavbqty, 0)) baseqty --现有库存
,SUM(nvl(j.flockqty, 0)) lockqty --锁库库存
,0 ttlflag
FROM cloudyl.t_bd_material a --物料
INNER JOIN cloudyl.t_bd_material_l b
ON a.fmaterialid = b.fmaterialid --物料
INNER JOIN cloudyl.t_bd_materialsale c
ON a.fmaterialid = c.fmaterialid --物料销售信息
INNER JOIN cloudyl.t_bd_materialstock stocck
ON a.fmaterialid = stocck.fmaterialid --库存信息
INNER JOIN cloudyl.t_bd_stock_l b1
ON stocck.fstockid = b1.fstockid
INNER JOIN cloudyl.t_bd_unit_l d
ON d.funitid = c.fsaleunitid --单位
LEFT JOIN (SELECT d.forgid
,bat.fnumber batchnum
,b.fproducedate --生产日期
,b.fexpirydate --有效期至
,b.fmaterialid
,c1.fname stockname
,b1.fname fstockstatus
,SUM(b.fqty) fbaseavbqty
FROM cloudyl.t_stk_inventory b
LEFT JOIN cloudyl.t_bd_stockstatus_l b1
ON b.fstockstatusid = b1.fstockstatusid
INNER JOIN cloudyl.t_bd_stock c
ON b.fstockid = c.fstockid
INNER JOIN cloudyl.t_bd_stock_l c1
ON c.fstockid = c1.fstockid
INNER JOIN cloudyl.t_org_organizations_l d
ON c.f_yld_orgid = d.forgid
INNER JOIN cloudyl.t_bd_lotmaster bat
ON b.flot = bat.flotid
WHERE b.fbaseqty <> 0
GROUP BY d.forgid
,bat.fnumber
,b.fexpirydate
,b.fproducedate
,b.fmaterialid
,b1.fname
,c1.fname) i
ON i.fmaterialid = a.fmaterialid
INNER JOIN cloudyl.t_org_organizations_l a1
ON a1.forgid = i.forgid --仓库所在地
LEFT JOIN (SELECT f_yld_orgid
,f_yld_itemid
,SUM(nvl(f_yld_qty, 0)) flockqty
FROM cloudyl.yl_t_stk_lockstock
GROUP BY f_yld_orgid, f_yld_itemid) j
ON j.f_yld_orgid = a1.forgid
AND j.f_yld_itemid = a.fmaterialid
LEFT JOIN (SELECT rn, fsaleorgid, fmaterialid, fprice
FROM (SELECT row_number() over(PARTITION BY t1.fsaleorgid, t2.fmaterialid ORDER BY fmodifydate DESC, t1.fid DESC) rn
,t1.fsaleorgid
,t2.fmaterialid
,t2.fprice
FROM cloudyl.t_sal_pricelist t1
INNER JOIN cloudyl.t_sal_pricelistentry t2
ON t1.fid = t2.fid
WHERE t1.flimitcustomer <> '1'
AND t1.flimitcustomer <> '2'
AND t2.fprice > 0
AND t1.fdocumentstatus = 'C'
AND t1.fforbidstatus = 'A'
AND t2.fforbidstatus = 'A'
AND SYSDATE BETWEEN t1.feffectivedate AND
t1.fexpirydate
AND SYSDATE BETWEEN t2.feffectivedate AND
t2.fexpriydate) t
WHERE rn = 1) h
ON a.fmaterialid = h.fmaterialid
AND h.fsaleorgid = a.fuseorgid --单价销售组织匹配物料使用组织
WHERE a.fmaterialid IN
(SELECT bas.fmaterialid
FROM cloudyl.t_bd_materialbase bas
,cloudyl.t_bd_materialcategory_l gry
WHERE bas.fcategoryid = gry.fcategoryid
AND fname IN ('产成品', '委外半成品', '自制半成品'))
GROUP BY a1.fname
,a.fnumber
,b.fname
,b.fspecification
,i.stockname
,d.fname
,h.fprice
,stocck.fexpperiod
,i.batchnum --批次号
,i.fproducedate --生产日期
,i.fexpirydate --有效期至
,i.fstockstatus
,c.f_yl_ratio
ORDER BY a1.fname
,a.fnumber
,b.fname
,b.fspecification
,i.stockname
,d.fname
,h.fprice

逍遥天一的主页 逍遥天一 | 初学一级 | 园豆:58
提问于:2017-01-13 15:09
< >
分享
最佳答案
0

这玩意儿我好像遇到过,我是改了一下数据库字段类型就解决了,不知道你这个是不是这样的问题

收获园豆:160
大师兄丶 | 小虾三级 |园豆:843 | 2017-01-13 16:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册