I'm trying to write a conditional statement in Oracle. There's a table for customers' accounts.
The logic is: If the Account_Close_Date contains values, it means the account has been closed by the customer, so the 'Status' should be "closed". Otherwise, the 'Status' should be "Open".
I write something like this:
select
CASE Account_Close_Date WHEN null THEN 'Open'
ELSE 'Closed' END as Status,
Account_Close_Date
from customer_account_Table
However, it doesn't work. The "Status" is all "closed", even if there is no value in "Account_Close_Date".
CodePudding user response：
CASE "statements" do exist, but only in PL/SQL, not in plain SQL. What you have in your SELECT statement is an example of a CASE expression.
CASE expressions have two syntactical forms: searched and simple. You have an example of a simple CASE expression, one where the conditions are equality conditions. Problem is, in the three-valued logic of SQL, nothing is ever equal to NULL. Your condition is "if so-and-so-date equals NULL, then..." which is never TRUE.
Instead, use the searched syntax (already shown in a comment under your question):
CASE WHEN so-and-so-date IS NULL THEN ... ELSE ... END as ...
CodePudding user response：
Try to use brackets:
SELECT Account_Close_Date, (CASE WHEN Account_Close_Date IS NULL THEN 'Open' ELSE 'Closed' END) as status FROM customer_account_Table;