Writing CASE statement Error ORA-00923

Question

I have a database that I have populated using CREATE and INSERT INTO statements. I am now trying to write a CASE statemenet that will display 'customers' whose payment_due_date has passed todays date. Below is the following code

CREATE STATEMENT 'Ord'(Order)

CREATE TABLE Ord(OrderID varchar2(9) PRIMARY KEY, 
CustomerID varchar(9) REFERENCES Customer(CustomerID), 
Expected_Delivery_Date date DEFAULT sysdate NOT NULL, 
Actual_Delivery_Date date DEFAULT sysdate NOT NULL, 
Payment_Due_Date date DEFAULT sysdate NOT NULL,
 Order_Date date DEFAULT sysdate NOT NULL, Price Varchar(10), 
Order_Placed varchar2(1) CONSTRAINT OrderPlaced 
CHECK(Order_Placed IN('Y','N')) NOT NULL, Order_Confirmed varchar2(1)
CONSTRAINT Order_Confirmed CHECK(Order_Confirmed IN('Y','N')) 
NOT NULL, Order_Completed varchar2(1) CONSTRAINT Order_Completed
CHECK(Order_Completed IN('Y','N')) NOT NULL)

INSERT STATEMENT

 INSERT INTO Ord VALUES(401565981, 501623129, 
    '10-Dec-10', '11-Dec-10', '07-Dec-10', '03-Dec-10','£14.99', 'Y', 'Y', 'Y')

CASE STATEMENT

 SELECT OrderID, CustomerID, Payment_Due_Date CASE WHEN 
Payment_Due_Date = '08-Dec-10' THEN 'Send Final Demand Letter'
    ELSE 'Do not send letter' 
    END FROM Ord;

When I try to run the above case statement I recieve the following error

ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause:
*Action: Error at Line: 26 Column: 50

Is there any possible way around this?

score 5 · Accepted Answer · answered Dec 09 '10 at 14:14

5

I think you need a comma between Payment_Due_Date and CASE.

answered Dec 09 '10 at 14:14

Spiny Norman

8,277
1
30
55

Yip (+1). The result of the CASE-statement is merely another column in your resultset, therefore it needs a comma. – Jules Dec 09 '10 at 14:16
You can see from the code that I am trying to write a query so that if the Payment due date has past todays date then the query should ouput the message 'Send final demand letter', its fairly similar to an 'if else statement' Payment_Due_Date = <'08-Dec-10' would this work? – Dec 09 '10 at 14:22
I'd imagine you would need to use `Payment_Due_Date <= '2010-12-08'`, but I don't know that much about Oracle specifics :) (edit: oops, <= instead of >=) – Spiny Norman Dec 09 '10 at 14:26
Ah, I'm sorry: I hadn't seen the '08-Dec-10' format before. Your solution is correct. – Spiny Norman Dec 09 '10 at 14:35
1

@user532339 Argh! Please don't rely on implicit conversion of strings to dates. Ideally use either the to_date function with a format string `to_date('08-DEC-2010', 'DD-MON-YYYY')` or the ANSI date literal `date '2010-12-08'`. Implicit conversion is just asking for trouble! – Mike Meyers Dec 09 '10 at 14:51
MikeyByCrikey, I did use the to_date function and I found that was my problem. Cheers – Dec 09 '10 at 14:54

Writing CASE statement Error ORA-00923

1 Answers1