上次得到无名及各位帮助作成下记LINK的电子账本,先谢谢大家.
http://www.itpub.net/viewthread.php?tid=931296&extra=&page=1
今日在使用中发现一问题,有一行的结果不对.最终结果却是对的,见附件.
我的SQL是:
select nvl2(a.transaction_code,part_no,null) part_no,
nvl2(a.transaction_code,to_char(dated,'yyyy-mm-dd'),to_char(last_day(dated),'yyyy-mm-dd')) dated,
a.transaction_code,
--nvl2(a.transaction_code,direction,null) direction,
onhand_qty last_month,
nvl2(a.transaction_code,in_qty,null) in_qty,
nvl2(a.transaction_code,out_qty,null) out_qty,
sum(to_number(direction||quantity)) over(partition by part_no order by dated) cur_qty,
nvl2(a.transaction_code,order_no,null) order_no,
nvl2(a.transaction_code,source,null) source
from (select transaction_code,
dated,
part_no,
direction,
source,
order_no,
decode(direction,'+',quantity) in_qty,
decode(direction,'-',quantity) out_qty,
'' onhand_qty,quantity
from inventory_transaction_hist_tab t
where part_no like nvl('&PART_NO','%')
and contract='SKY'
and transaction_code is not null
and date_applied>=last_day(add_months(to_date('&TO_DATE','yyyy-mm-dd'),-1))+1
and date_applied<=to_date('&TO_DATE','yyyy-mm-dd')
and location_no like NVL('&LOCATION_NO','%')
union all
select '',to_date(month ||'-1','yyyy-mm-dd') dated,
part_no,'+','','',0,0,'' ||onhand_qty,onhand_qty
from sk_month_onhand_tab
where part_no like nvl('&PART_NO','%')
and month=to_char(add_months(to_date('&TO_DATE','yyyy-mm-dd'),-1),'YYYY-MM')
and location_no like NVL('&LOCATION_NO','%')) a
我查了下,产生错误的这两行,除QUANTITY以外,所有列均相等,请大师指点,怎么解决这个行显示问题错误的问题.
http://www.itpub.net/viewthread.php?tid=931296&extra=&page=1
今日在使用中发现一问题,有一行的结果不对.最终结果却是对的,见附件.
我的SQL是:
select nvl2(a.transaction_code,part_no,null) part_no,
nvl2(a.transaction_code,to_char(dated,'yyyy-mm-dd'),to_char(last_day(dated),'yyyy-mm-dd')) dated,
a.transaction_code,
--nvl2(a.transaction_code,direction,null) direction,
onhand_qty last_month,
nvl2(a.transaction_code,in_qty,null) in_qty,
nvl2(a.transaction_code,out_qty,null) out_qty,
sum(to_number(direction||quantity)) over(partition by part_no order by dated) cur_qty,
nvl2(a.transaction_code,order_no,null) order_no,
nvl2(a.transaction_code,source,null) source
from (select transaction_code,
dated,
part_no,
direction,
source,
order_no,
decode(direction,'+',quantity) in_qty,
decode(direction,'-',quantity) out_qty,
'' onhand_qty,quantity
from inventory_transaction_hist_tab t
where part_no like nvl('&PART_NO','%')
and contract='SKY'
and transaction_code is not null
and date_applied>=last_day(add_months(to_date('&TO_DATE','yyyy-mm-dd'),-1))+1
and date_applied<=to_date('&TO_DATE','yyyy-mm-dd')
and location_no like NVL('&LOCATION_NO','%')
union all
select '',to_date(month ||'-1','yyyy-mm-dd') dated,
part_no,'+','','',0,0,'' ||onhand_qty,onhand_qty
from sk_month_onhand_tab
where part_no like nvl('&PART_NO','%')
and month=to_char(add_months(to_date('&TO_DATE','yyyy-mm-dd'),-1),'YYYY-MM')
and location_no like NVL('&LOCATION_NO','%')) a
我查了下,产生错误的这两行,除QUANTITY以外,所有列均相等,请大师指点,怎么解决这个行显示问题错误的问题.
problem1.zip
(2008-02-22 11:32:43, Size: 4.94 KB, Downloads: 0)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12391917/viewspace-247733/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12391917/viewspace-247733/