1

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

2 Answers 2

1

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.

Sign up to request clarification or add additional context in comments.

Comments

1

Meaning this as a comment to Alex's answer, but comments don't allow proper formatting of code.

The following use of the CASE expression can be simplified somewhat. Instead of

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",

you can also write

CASE TRANSACTION_TYPE
  WHEN 'RECEIVE'          THEN r.PRIMARY_QUANTITY * l.UNIT_PRICE
  WHEN 'RETURN TO VENDOR' THEN  -1 * (r.PRIMARY_QUANTITY * l.UNIT_PRICE) 
  END AS "Total",

Alex used a "searched" case expression; what I show above is called a "simple" case expression. Oracle documentation: https://docs.oracle.com/cd/B28359_01/server.111/b28286/expressions004.htm

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.