CODEWITHSUNDEEP
sqloracle-database
Ranger
Ranger

Reputation: 1174

Exposing a column in an Oracle SQL CASE statement

I'm trying to modify an SQL select statement to select the inverse value of a column (as in, negative if the value was positive, positive if the value was negative) based on the value of a different column.

This (not inversed) works:

BSEG_WBTR as INVOICE_AMOUNT

But this does not:

CASE
  WHEN BSEG_SHKZG = 'S' THEN
    -BSEG_WBTR
  ELSE
    BSEG_WBTR
  END AS INVOICE_AMOUNT,

Giving the error of:

[Error] Execution (13: 45): ORA-00904: "BSEG_WBTR": invalid identifier

Which seems to imply that BSEG_WBTR is no longer exposed once inside the CASE statement.

How do I expose a column to be used inside of a CASE statement?

Upvotes: 0

Views: 300

Answers (3)

Stan Shaw
Stan Shaw

Reputation: 3034

Just multiply the value by -1. Try this:

CASE
  WHEN BSEG_SHKZG = 'S' THEN
    BSEG_WBTR * -1
  ELSE
    BSEG_WBTR
  END AS INVOICE_AMOUNT,

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

To select the inverse value of a column (as in, negative if the value was positive, positive if the value was negative) based on the value of a different column.

Multiply any value with -1 will return the inverse value.

Example:

DECLARE @BSEG_SHKZG AS INT = 2;
SELECT @BSEG_SHKZG * -1  -- results -2

DECLARE @BSEG_SHKZG_ AS INT = -2;
SELECT @BSEG_SHKZG_ * -1  -- results 2

So the below query will return your expected result:

CASE
WHEN BSEG_SHKZG = 'S' THEN
    BSEG_WBTR * -1
ELSE
    BSEG_WBTR
END AS INVOICE_AMOUNT

Upvotes: 0

Allan
Allan

Reputation: 17429

The query below shows three methods of accomplishing what you are attempting. My preferred syntax is the second (use the column name once, with the case providing a multiplier).

SELECT CASE WHEN bseg_shkzg = 'S' THEN -bseg_wbtr ELSE bseg_wbtr END
          AS invoice_amount,
       bseg_wbtr * CASE bseg_shkzg WHEN 'S' THEN -1 ELSE 1 END
          AS invoice_amount2,
       CASE WHEN bseg_shkzg = 'S' THEN 0 - bseg_wbtr ELSE bseg_wbtr END
          AS invoice_amount3
FROM   (SELECT 10 AS bseg_wbtr, 'S' AS bseg_shkzg FROM DUAL
        UNION ALL
        SELECT 20 AS bseg_wbtr, 'R' AS bseg_shkzg FROM DUAL)

The first appears to be what you initially tried, so I'm not sure that the error you're seeing is directly related to the error you're getting.

Upvotes: 0

Related Questions