I'm trying to figure out how to get a result from a case statement, then use that result in several other case statements throughout the query. It should work the way that I am getting the "TransactionDate", but also for "Period", "BudgetYear", "CalendarMonth", and "CalendarYear". The result of the case statement would be replacing where it has "aila.ACCOUNTING_DATE" in each of those statements. I was considering trying case statements inside of each case statement, but that would be extremely long and I was thinking there had to be a better way.
SELECT
r.TRANSACTION_ID,
CASE
WHEN TRANSACTION_TYPE = 'RECEIVE'
THEN r.PRIMARY_QUANTITY * l.UNIT_PRICE
WHEN TRANSACTION_TYPE = 'RETURN TO VENDOR'
THEN -1 * (r.PRIMARY_QUANTITY * l.UNIT_PRICE)
END AS "Total",
glcc.SEGMENT5 AS BinNumber,
CAST(SUBSTR(d.ATTRIBUTE2, -2) AS INT) AS LineNumber,
CASE
WHEN r.PRIMARY_QUANTITY * l.UNIT_PRICE = aila.AMOUNT
THEN aila.ACCOUNTING_DATE
ELSE (SELECT MAX(ACCOUNTING_DATE) FROM AP_INVOICE_LINES_ALL WHERE d.PO_DISTRIBUTION_ID = PO_DISTRIBUTION_ID)
END AS TransactionDate,
CASE
WHEN EXTRACT(MONTH FROM aila.ACCOUNTING_DATE) >= 9
THEN EXTRACT(MONTH FROM aila.ACCOUNTING_DATE) - 8
ELSE
EXTRACT(MONTH FROM aila.ACCOUNTING_DATE) + 4
END AS Period,
CASE
WHEN EXTRACT(MONTH FROM aila.ACCOUNTING_DATE) >= 9
THEN EXTRACT(YEAR FROM aila.ACCOUNTING_DATE) + 1
ELSE
EXTRACT(YEAR FROM aila.ACCOUNTING_DATE)
END AS BudgetYear,
EXTRACT(MONTH FROM aila.ACCOUNTING_DATE) AS CalendarMonth,
EXTRACT(YEAR FROM aila.ACCOUNTING_DATE) AS CalendarYear,
s.VENDOR_ID AS VendorId,
s.VENDOR_NAME AS VendorName,
h.SEGMENT1 AS PONumber,
l.LINE_NUM AS POLineNumber
FROM
RCV_TRANSACTIONS r
JOIN
PO_DISTRIBUTIONS_ALL d
ON r.PO_LINE_ID = d.PO_LINE_ID
JOIN
GL_CODE_COMBINATIONS glcc
ON d.CODE_COMBINATION_ID = glcc.CODE_COMBINATION_ID
LEFT OUTER JOIN
AP_SUPPLIERS s
ON s.VENDOR_ID = r.VENDOR_ID
LEFT OUTER JOIN
PO_HEADERS_ALL h
ON h.PO_HEADER_ID = d.PO_HEADER_ID
LEFT OUTER JOIN
PO.PO_LINES_ALL l
ON l.PO_LINE_ID = r.PO_LINE_ID
LEFT OUTER JOIN
AP_INVOICE_LINES_ALL aila
ON d.PO_DISTRIBUTION_ID = aila.PO_DISTRIBUTION_ID
AND r.PRIMARY_QUANTITY * l.UNIT_PRICE = aila.AMOUNT
WHERE
(TRANSACTION_TYPE = 'RECEIVE' or TRANSACTION_TYPE = 'RETURN TO VENDOR')
AND
(glcc.SEGMENT2 = '1070'
OR
glcc.SEGMENT2 = '1071');