去掉红色部分就可以正确执行,加上这个条件就报缺少又括号错误(ORA-00907)
很奇怪的一个问题。
select
b.ITEMS_ID,
b.TITEMOID,
b.WAHE_ID,
c.END_INV_J as START_INV_J,
b.REC_INV_J,
b.PAY_INV_J,
c.END_INV_J + b.REC_INV_J - b.PAY_INV_J as END_INV_J,
c.END_INV_K as START_INV_K,
b.REC_INV_K,
b.PAY_INV_K,
c.END_INV_K + b.REC_INV_K - b.PAY_INV_K as END_INV_K,
c.END_INV_D as START_INV_D,
b.REC_INV_D,
b.PAY_INV_D,
c.END_INV_D + b.REC_INV_D - b.PAY_INV_D as END_INV_D,
c.START_INV_J as PREV_START_INV_J,
c.REC_INV_J as PREV_REC_INV_J,
c.PAY_INV_J as PREV_PAY_INV_J,
c.END_INV_J as PREV_END_INV_J,
c.START_INV_K as PREV_START_INV_K,
c.REC_INV_K as PREV_REC_INV_K,
c.PAY_INV_K as PREV_PAY_INV_K,
c.END_INV_K as PREV_END_INV_K,
c.START_INV_D as PREV_START_INV_D,
c.REC_INV_D as PREV_REC_INV_D,
c.PAY_INV_D as PREV_PAY_INV_D,
c.END_INV_D as PREV_END_INV_D
from (
select
a.ITEMS_ID,
a.TITEMOID,
a.WAHE_ID,
sum(a.REC_INV_J) as REC_INV_J,
sum(a.PAY_INV_J) as PAY_INV_J,
sum(a.REC_INV_K) as REC_INV_K,
sum(a.PAY_INV_K) as PAY_INV_K,
sum(a.REC_INV_D) as REC_INV_D,
sum(a.PAY_INV_D) as PAY_INV_D
from (
select
e.ITEMS_ID,
c.TITEMOID,
f.WAHE_ID,
l.TQTY as REC_INV_J,
0 as PAY_INV_J,
case when i.UNIT_ID = '13641' then c.TQUANTITY else c.TQUANTITY * j.QUOTIETY end as REC_INV_K,
0 as PAY_INV_K,
case when i.UNIT_ID = '13641' then c.TQUANTITY * k.QUOTIETY else c.TQUANTITY * j.QUOTIETY * k.QUOTIETY end as REC_INV_D, --入库量担(担)
0 as PAY_INV_D
from DWKCFX.DW_B_KCFX_YL_WHSE f
left join WYEASDB.TINVMOVEINVOUCHER b on b.TWAREHOUSEOID = f.S_ID
left join WYEASDB.TINVMOVEINVLINE c on b.TOID = c.THEADEROID
left join DWKCFX.DW_B_KCFX_ITEMS_S_REF d ON c.TITEMOID = d.S_ID AND d.S_SYS_ID = '1'
left join DWKCFX.DW_B_KCFX_ITEMS e
on e.ITEMS_ID = d.ITEMS_ID AND e.STATUS = '1'
and (
to_date('2009-01-01','yyyy-mm-dd') between e.eff_time and e.exp_time or
to_date('2009-01-31','yyyy-mm-dd') between e.eff_time and e.exp_time or
(
e.eff_time>to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')>e.exp_time
)
)
left join DWBASE.DW_B_BASE_UNIT_S_REF i on c.TUOMOID = i.S_ID and i.S_SYS_ID = '1'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM j on i.UNIT_ID = j.UNIT_ID and j.TRANSFORM_UNIT_ID = '13641'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM k on k.UNIT_ID = '13641' and k.TRANSFORM_UNIT_ID = '13643'
left join WYEASDB.TINVINASSISTUOM l on l.TLINEOID = c.TOID
left join WYEASDB.TINVINVREASON m on m.TOID = b.TINVREASONOID
where f.S_SYS_ID = '1' and b.TCRTTIME between to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')
and m.TNAME in ('采购入库','委加工入库','盘盈入库','烟叶厂际移库入库','初始化调帐','初始化库存台帐')
union all
select
e.ITEMS_ID,
c.TITEMOID,
f.WAHE_ID,
0 as REC_INV_J,
l.TQTY as PAY_INV_J,
0 as REC_INV_K,
case when i.UNIT_ID = '13641' then c.TQUANTITY else c.TQUANTITY * j.QUOTIETY end as PAY_INV_K,
0 as REC_INV_D,
case when i.UNIT_ID = '13641' then c.TQUANTITY else c.TQUANTITY * j.QUOTIETY * k.QUOTIETY end as PAY_INV_D
from DWKCFX.DW_B_KCFX_YL_WHSE f
left join WYEASDB.TINVMOVEOUTVOUCHER b on b.TWAREHOUSEOID = f.S_ID
left join WYEASDB.TINVMOVEOUTVLINE c on b.TOID = c.THEADEROID
left join DWKCFX.DW_B_KCFX_ITEMS_S_REF d ON c.TITEMOID = d.S_ID AND d.S_SYS_ID = '1'
left join DWKCFX.DW_B_KCFX_ITEMS e
on e.ITEMS_ID = d.ITEMS_ID AND e.STATUS = '1'
and (
to_date('2009-01-01','yyyy-mm-dd') between e.eff_time and e.exp_time or
to_date('2009-01-31','yyyy-mm-dd') between e.eff_time and e.exp_time or
(
e.eff_time>to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')>e.exp_time
)
)
left join DWBASE.DW_B_BASE_UNIT_S_REF i on c.TUOMOID = i.S_ID and i.S_SYS_ID = '1'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM j on i.UNIT_ID = j.UNIT_ID and j.TRANSFORM_UNIT_ID = '13641'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM k on k.UNIT_ID = '13641' and k.TRANSFORM_UNIT_ID = '13643'
left join WYEASDB.TINVOUTASSISTUOM l on l.TLINEOID = c.TOID
left join WYEASDB.TINVINVREASON m on m.TOID = b.TINVREASONOID
where f.S_SYS_ID = '1' and b.TCRTTIME between to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')
and m.TNAME in ('烟叶厂际移库出库','委加工出库','其它销售出库','烟叶试验领用','烟叶生产领用(用于一般生产)','烟叶仓耗')
)a group by a.ITEMS_ID,a.TITEMOID,a.WAHE_ID
)b left join DWKCFX.DW_TX_KCFX_YL_MONTH_RPS_MT c on c.ITEMS_ID = b.ITEMS_ID and c.WAHE_ID = b.WAHE_ID and c.MONTH_S = '200602'
很奇怪的一个问题。
select
b.ITEMS_ID,
b.TITEMOID,
b.WAHE_ID,
c.END_INV_J as START_INV_J,
b.REC_INV_J,
b.PAY_INV_J,
c.END_INV_J + b.REC_INV_J - b.PAY_INV_J as END_INV_J,
c.END_INV_K as START_INV_K,
b.REC_INV_K,
b.PAY_INV_K,
c.END_INV_K + b.REC_INV_K - b.PAY_INV_K as END_INV_K,
c.END_INV_D as START_INV_D,
b.REC_INV_D,
b.PAY_INV_D,
c.END_INV_D + b.REC_INV_D - b.PAY_INV_D as END_INV_D,
c.START_INV_J as PREV_START_INV_J,
c.REC_INV_J as PREV_REC_INV_J,
c.PAY_INV_J as PREV_PAY_INV_J,
c.END_INV_J as PREV_END_INV_J,
c.START_INV_K as PREV_START_INV_K,
c.REC_INV_K as PREV_REC_INV_K,
c.PAY_INV_K as PREV_PAY_INV_K,
c.END_INV_K as PREV_END_INV_K,
c.START_INV_D as PREV_START_INV_D,
c.REC_INV_D as PREV_REC_INV_D,
c.PAY_INV_D as PREV_PAY_INV_D,
c.END_INV_D as PREV_END_INV_D
from (
select
a.ITEMS_ID,
a.TITEMOID,
a.WAHE_ID,
sum(a.REC_INV_J) as REC_INV_J,
sum(a.PAY_INV_J) as PAY_INV_J,
sum(a.REC_INV_K) as REC_INV_K,
sum(a.PAY_INV_K) as PAY_INV_K,
sum(a.REC_INV_D) as REC_INV_D,
sum(a.PAY_INV_D) as PAY_INV_D
from (
select
e.ITEMS_ID,
c.TITEMOID,
f.WAHE_ID,
l.TQTY as REC_INV_J,
0 as PAY_INV_J,
case when i.UNIT_ID = '13641' then c.TQUANTITY else c.TQUANTITY * j.QUOTIETY end as REC_INV_K,
0 as PAY_INV_K,
case when i.UNIT_ID = '13641' then c.TQUANTITY * k.QUOTIETY else c.TQUANTITY * j.QUOTIETY * k.QUOTIETY end as REC_INV_D, --入库量担(担)
0 as PAY_INV_D
from DWKCFX.DW_B_KCFX_YL_WHSE f
left join WYEASDB.TINVMOVEINVOUCHER b on b.TWAREHOUSEOID = f.S_ID
left join WYEASDB.TINVMOVEINVLINE c on b.TOID = c.THEADEROID
left join DWKCFX.DW_B_KCFX_ITEMS_S_REF d ON c.TITEMOID = d.S_ID AND d.S_SYS_ID = '1'
left join DWKCFX.DW_B_KCFX_ITEMS e
on e.ITEMS_ID = d.ITEMS_ID AND e.STATUS = '1'
and (
to_date('2009-01-01','yyyy-mm-dd') between e.eff_time and e.exp_time or
to_date('2009-01-31','yyyy-mm-dd') between e.eff_time and e.exp_time or
(
e.eff_time>to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')>e.exp_time
)
)
left join DWBASE.DW_B_BASE_UNIT_S_REF i on c.TUOMOID = i.S_ID and i.S_SYS_ID = '1'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM j on i.UNIT_ID = j.UNIT_ID and j.TRANSFORM_UNIT_ID = '13641'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM k on k.UNIT_ID = '13641' and k.TRANSFORM_UNIT_ID = '13643'
left join WYEASDB.TINVINASSISTUOM l on l.TLINEOID = c.TOID
left join WYEASDB.TINVINVREASON m on m.TOID = b.TINVREASONOID
where f.S_SYS_ID = '1' and b.TCRTTIME between to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')
and m.TNAME in ('采购入库','委加工入库','盘盈入库','烟叶厂际移库入库','初始化调帐','初始化库存台帐')
union all
select
e.ITEMS_ID,
c.TITEMOID,
f.WAHE_ID,
0 as REC_INV_J,
l.TQTY as PAY_INV_J,
0 as REC_INV_K,
case when i.UNIT_ID = '13641' then c.TQUANTITY else c.TQUANTITY * j.QUOTIETY end as PAY_INV_K,
0 as REC_INV_D,
case when i.UNIT_ID = '13641' then c.TQUANTITY else c.TQUANTITY * j.QUOTIETY * k.QUOTIETY end as PAY_INV_D
from DWKCFX.DW_B_KCFX_YL_WHSE f
left join WYEASDB.TINVMOVEOUTVOUCHER b on b.TWAREHOUSEOID = f.S_ID
left join WYEASDB.TINVMOVEOUTVLINE c on b.TOID = c.THEADEROID
left join DWKCFX.DW_B_KCFX_ITEMS_S_REF d ON c.TITEMOID = d.S_ID AND d.S_SYS_ID = '1'
left join DWKCFX.DW_B_KCFX_ITEMS e
on e.ITEMS_ID = d.ITEMS_ID AND e.STATUS = '1'
and (
to_date('2009-01-01','yyyy-mm-dd') between e.eff_time and e.exp_time or
to_date('2009-01-31','yyyy-mm-dd') between e.eff_time and e.exp_time or
(
e.eff_time>to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')>e.exp_time
)
)
left join DWBASE.DW_B_BASE_UNIT_S_REF i on c.TUOMOID = i.S_ID and i.S_SYS_ID = '1'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM j on i.UNIT_ID = j.UNIT_ID and j.TRANSFORM_UNIT_ID = '13641'
left join DWBASE.B_MEASURE_UNIT_TRANSFORM k on k.UNIT_ID = '13641' and k.TRANSFORM_UNIT_ID = '13643'
left join WYEASDB.TINVOUTASSISTUOM l on l.TLINEOID = c.TOID
left join WYEASDB.TINVINVREASON m on m.TOID = b.TINVREASONOID
where f.S_SYS_ID = '1' and b.TCRTTIME between to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-01-31','yyyy-mm-dd')
and m.TNAME in ('烟叶厂际移库出库','委加工出库','其它销售出库','烟叶试验领用','烟叶生产领用(用于一般生产)','烟叶仓耗')
)a group by a.ITEMS_ID,a.TITEMOID,a.WAHE_ID
)b left join DWKCFX.DW_TX_KCFX_YL_MONTH_RPS_MT c on c.ITEMS_ID = b.ITEMS_ID and c.WAHE_ID = b.WAHE_ID and c.MONTH_S = '200602'
解决方案 »
- 数据库之间的同步
- 怎么更新一个为null的值?
- 请高手进来解答~~~~~~(很有难度的查询)
- 我想记录一个关与哪些人查询这个表的记录
- C#访问package的一个存储过程出错
- 求脚本(如果按量插入)
- ORACLE数据库开发经验总结
- 我是oracle初学者,请问大家学习有什末步骤吗?
- 菜鸟问题,%rowtype是什么意思呀?
- 请问在VB中用ADO连接oracle数据服务器的字符串是什么?
- java读取oracle的时候无法读取到最新的数据
- -----------在java中执行SQL的问题,奇怪的ora-01843: not a valid month,100分求助《急》《急》-------------
这样写,解析的时候会把 c.MONTH_S = '200602'当作left join的条件。
join on关键字只能写用于连接的列,如c.ITEMS_ID = b.ITEMS_ID,.WAHE_ID = b.WAHE_ID 你可以在连接之前、或连接之后做MONTH_S = '200602'的筛选,
where c.MONTH_S = '200602'
你这样写,on后面关联的都是表C与B的字段,所以用这个'200602' 就会出错
改成 where c.MONTH_S = '200602'
能行吗
第二,放在帅选的前面