我正在尝试编写一份自定义报告,其中发票要么在PO_Number列中有一个 PO 编号,要么不会。
如果他们没有 PO 编号,我希望从 4 列(段 1、段 2、段 3 和段 4(中获取数据,其中 2 个 ID 号相同。在同一个语句中,如果有一个 PO 号,我想拉出相同的 4 列,但 2 个不同的 ID 号匹配。
到目前为止,我拥有的SQL是:
SELECT DISTINCT
AID.INVOICE_ID,
AID.AMOUNT,
AID.PERIOD_NAME,
GCC.SEGMENT1 as Organisation,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
INV.INVOICE_NUM,
INV.CREATION_DATE,
PO.SEGMENT1 as PO_Number,
SUP.VENDOR_NAME,
AID.LINE_TYPE_LOOKUP_CODE,
LINES.LINE_NUMBER
FROM
AP_INVOICES_All INV
INNER JOIN
AP_INVOICE_LINES_ALL LINES
ON INV.INVOICE_ID = LINES.INVOICE_ID
INNER JOIN
AP_INVOICE_DISTRIBUTIONS_ALL AID
ON INV.INVOICE_ID = AID.INVOICE_ID
INNER JOIN
GL_CODE_COMBINATIONS GCC
ON AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
INNER JOIN
POZ_SUPPLIERS_V SUP
ON INV.VENDOR_ID = SUP.VENDOR_ID
LEFT JOIN
PO_HEADERS_ALL PO
ON LINES.PO_HEADER_ID = PO.PO_HEADER_ID
WHERE
AID.LINE_TYPE_LOOKUP_CODE NOT IN
(
'REC_TAX',
'NONREC_TAX'
)
AND LINES.LINE_TYPE_LOOKUP_CODE NOT IN
(
'TAX'
)
ORDER BY
AID.INVOICE_ID,
LINES.LINE_NUMBER
如果有 PO,我希望在以下位置完成匹配:
PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
如果没有采购订单:
AID.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
(我知道我需要在某处引用PO_DISTRIBUTIONS_ALL表,但不确定在哪里(
任何帮助都会很棒,因为我对 SQL 很陌生,不知道如何表达我想在 SQL 代码中实现的目标。
如果我不清楚任何部分,请告诉我
您可以将 join 留PO_DISTRIBUTION_ALL
并在GL_CODE_COMBINATIONS
上创建条件连接,如下所示:
SELECT DISTINCT
AID.INVOICE_ID,
AID.AMOUNT,
AID.PERIOD_NAME,
GCC.SEGMENT1 as Organisation,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
INV.INVOICE_NUM,
INV.CREATION_DATE,
PO.SEGMENT1 as PO_Number,
SUP.VENDOR_NAME,
AID.LINE_TYPE_LOOKUP_CODE,
LINES.LINE_NUMBER
FROM AP_INVOICES_All INV
INNER JOIN AP_INVOICE_LINES_ALL LINES
ON INV.INVOICE_ID = LINES.INVOICE_ID
INNER JOIN AP_INVOICE_DISTRIBUTIONS_ALL AID
ON INV.INVOICE_ID = AID.INVOICE_ID
LEFT JOIN PO_HEADERS_ALL PO
ON LINES.PO_HEADER_ID = PO.PO_HEADER_ID
LEFT JOIN PO_DISTRIBUTIONS_ALL PDA
ON PO.PO_HEADER_ID = PDA.PO_HEADER_ID
INNER JOIN GL_CODE_COMBINATIONS GCC
ON GCC.CODE_COMBINATION_ID = nvl(PDA.CODE_COMBINATION_ID, AID.DIST_CODE_COMBINATION_ID)
INNER JOIN
POZ_SUPPLIERS_V SUP
ON INV.VENDOR_ID = SUP.VENDOR_ID
WHERE AID.LINE_TYPE_LOOKUP_CODE NOT IN ( 'REC_TAX', 'NONREC_TAX')
AND LINES.LINE_TYPE_LOOKUP_CODE NOT IN ('TAX')
ORDER BY
AID.INVOICE_ID,
LINES.LINE_NUMBER