CODEWITHSUNDEEP
sqloracle-database
Derek
Derek

Reputation: 13

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

Upvotes: 1

Views: 402

Answers (2)

user5683823
user5683823

Reputation:

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

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191315

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.

Upvotes: 1

Related Questions