这个函数为啥不能这样写
select a.PART_NO, a.域03期末库存, b.域06期末库存,c.域01期末库存,d.域02期末库存
from
(SELECT a.PART_NO,sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域03期末库存
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('03') and upper( LOCATION_NO ) like upper( 'SM%' )
 AND DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )a,
(SELECT  b.PART_NO, sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域06期末库存
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('06') and upper( LOCATION_NO ) like upper( '%' )
and  DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )b,
(SELECT c.PART_NO, sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域01期末库存
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('01') and upper( LOCATION_NO ) like upper( '%' )
and  DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )c,
(SELECT d.PART_NO, sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域02期末库存
FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('02') and upper( LOCATION_NO ) like upper( 'SM%' )
 AND DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )d
 where a.PART_NO=b.PART_NO and b.PART_NO=c.PART_NO and c.PART_NO=d.PART_NO

解决方案 »

  1.   


    select a.域03期末库存, b.域06期末库存,c.域01期末库存,d.域02期末库存
    from
    (SELECT sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域03期末库存
    FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('03') and upper( LOCATION_NO ) like upper( 'SM%' )
     AND DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )a,
    (SELECT  sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域06期末库存
    FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('06') and upper( LOCATION_NO ) like upper( '%' )
    and  DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )b,
    (SELECT  sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域01期末库存
    FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('01') and upper( LOCATION_NO ) like upper( '%' )
    and  DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )c,
    (SELECT  sum(decode(DIRECTION,'+',quantity,0))-sum(decode(DIRECTION,'-',quantity,0))  域02期末库存
    FROM IFSAPP.INVENTORY_TRANSACTION_HIST2 where  CONTRACT in('02') and upper( LOCATION_NO ) like upper( 'SM%' )
     AND DATE_APPLIED<=to_date('&结束时间','yyyy-mm-dd') )d这样可以运行 但是不能达到我的需求
      

  2.   

    第二个查询的结果
    a.域03期末库存, b.域06期末库存,c.域01期末库存,d.域02期末库存1 2180 802043.9738 7811998.44058031 10545.15
     我想要的是
     part_no    a.域03期末库存  b.域06期末库存    c.域01期末库存           d.域02期末库存       aaa    2180    802043.9738    7811998.44058031 10545.15     bbb    2180     20000    78555          55555
         ..... 这样显示该怎么写呢
      

  3.   

    是不是多了一个列part_no,主要是这个问题!