sql - 将数据合并到单个查询

Question

我有两个查询，一个是从表中获取项目的 ISSUE 和 RETURN。

select MATU.ITEMNUM ,
MATU.DESCRIPTION ,
MATU.STORELOC ,
MATU.CONDITIONCODE,
MATU.ISSUETYPE,
SUM(MATU.QUANTITY )as QUANTITY,
INV.MINLEVEL,
INV.MAXLEVEL
from MATUSETRANS MATU
LEFT OUTER JOIN Inventory INV
ON INV.ITEMNUM=MATU.ITEMNUM
AND INV.LOCATION=MATU.STORELOC
where MATU.ITEMNUM='H95-04-0010'  --A57-01-0459A
AND MATU.TRANSDATE >'01-JAN-19'
AND MATU.CONDITIONCODE='01' and MATU.STORELOC='09'
GROUP BY MATU.ITEMNUM ,MATU.DESCRIPTION ,MATU.STORELOC ,INV.MINLEVEL,INV.MAXLEVEL,MATU.PONUM ,MATU.ISSUETYPE,MATU.CONDITIONCODE;

哪个结果看起来像

我的第二个查询是从表中获取 TRANSFER IN 和 TRANSFER OUT 项目

select ITEMNUM ,
DESCRIPTION,
TOSTORELOC ,
FROMSTORELOC ,
--TRANSDATE ,
sum(QUANTITY) as QUANTITY ,
ISSUE_CATEGORY as ISSUETYPE,LOCATION from
(select MATR.ITEMNUM,MATR.DESCRIPTION,MATR.TOSTORELOC,MATR.FROMSTORELOC,MATR.TRANSDATE,MATR.QUANTITY,MATR.ISSUETYPE,
        CASE WHEN INV.LOCATION = MATR.FROMSTORELOC THEN 'TRANSFER OUT'
             WHEN INV.LOCATION = MATR.TOSTORELOC THEN 'TRANSFER IN'
             ELSE '_'END as ISSUE_CATEGORY,
        MATR.DOCNUM,INV.LOCATION  
from MATRECTRANS MATR
INNER JOIN INVENTORY INV
ON INV.ITEMNUM=MATR.ITEMNUM 
where MATR.Itemnum='H95-04-0010' AND MATR.TRANSDATE>'01-JAN-19')
where ISSUE_CATEGORY in ('TRANSFER IN','TRANSFER OUT') and DOCNUM is not null and location='09'
group by ITEMNUM,DESCRIPTION ,TOSTORELOC ,FROMSTORELOC ,ISSUETYPE ,ISSUE_CATEGORY,LOCATION;

哪个结果看起来像

当我试图加入这两个记录时，记录正在重复！

我该如何解决这个问题？

我需要这样

如果有任何 TRANSFER IN/OUT 值，则显示它们，否则为 0。

标签： sqloracle