Derek Derek - 6 months ago 17
SQL Question

Oracle SQL, getting a value and using it in multiple case statements

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');

Answer

You can put the joins and initial case expression in an inline view or CTE (common table expression, a.k.a subquery factoring), and then query that - you can then refer to the case expression's column alias:

WITH CTE AS (
  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,
    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')
)
SELECT
   TRANSACTION_ID,
   "Total",
   BinNumber,
   LineNumber,
   TransactionDate,
   CASE
      WHEN EXTRACT(MONTH FROM TransactionDate) >= 9
      THEN EXTRACT(MONTH FROM TransactionDate) - 8
      ELSE
      EXTRACT(MONTH FROM TransactionDate) + 4
   END AS Period,
   CASE
      WHEN EXTRACT(MONTH FROM TransactionDate) >= 9
      THEN EXTRACT(YEAR FROM TransactionDateE) + 1
      ELSE
      EXTRACT(YEAR FROM TransactionDate)
   END AS BudgetYear,
   EXTRACT(MONTH FROM TransactionDate) AS CalendarMonth,
   EXTRACT(YEAR FROM TransactionDate) AS CalendarYear,
   VendorId,
   VendorName,
   PONumber,
   POLineNumber
  FROM
    CTE;

So the CTE query is everything from your initial query minus the four expressions that referred to aila.ACCOUNTING_DATE. And the query against CTE gets all the columns from that and adds the expressions for those four, but now referring to TransactionDate instead.

Comments