SQL Query gives missing parenthesis error

Question

I have been working on this for a few days and cannot figure out where the error is. The query is:

Select distinct p.PkgID, p.PkgName, p.PkgCost, c.CustFName || ' ' || c.CustLName as   "CUSTOMERNAME" 
FROM Subscription s, Package p, Customer c
WHERE p.PkgID = s.PkgID
AND c.CustID = s.CustID     
AND to_date 
 (s.StartDate, 'Mon DD, YYYY')=
to_date 
 (&StartDate, 'Mon DD, YYYY')
ORDER BY p.PkgID;

I keep getting:

SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis".

I cannot find the issue. Any help would be much appreciated. Here is what the table and insert statement looks like.

CREATE TABLE SUBSCRIPTION
(
SubID NUMBER(5) NOT NULL CONSTRAINT SUBSCRIPTION_SubID_PK PRIMARY KEY,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
CustID NUMBER(5) NOT NULL,   
PkgID NUMBER(5) NOT NULL  
 );

ALTER TABLE SUBSCRIPTION ADD CONSTRAINT SUBSCRIPTION_CustID_FK FOREIGN KEY (CustID)     REFERENCES CUSTOMER (CustID);
ALTER TABLE SUBSCRIPTION ADD CONSTRAINT SUBSCRIPTION_PkgID_FK FOREIGN KEY (PkgID) REFERENCES PACKAGE (PkgID);

INSERT INTO SUBSCRIPTION
VALUES (010, to_date('Jan 01, 2010 11:30','Mon DD, YYYY hh24:mi'), to_date('Jan 01,   2013 14:30','Mon DD, YYYY hh24:mi'), 00001, 10101);
INSERT INTO SUBSCRIPTION
VALUES (015,to_date('Mar 01, 2012 17:00','Mon DD, YYYY hh24:mi'), to_date('Dec 05, 2012 17:00','Mon DD, YYYY hh24:mi'), 00002, 20202);

As a side note, I did try it this way as well:

Select distinct p.PkgID, p.PkgName, p.PkgCost, c.CustFName || ' ' || c.CustLName as   "CUSTOMERNAME" 
FROM Subscription s
        Left OUTER JOIN Package p on p.PkgID = s.PkgID
    Left OUTER JOIN Customer c on c.CustID = s.CustID       
WHERE to_date (s.StartDate, 'Mon DD, YYYY')=
to_date (&StartDate, 'Mon DD, YYYY')    
ORDER BY p.PkgID;

Could you please provide the code that creates the table and inserts some data in it? — Dilshod
– Dilshod, Commented Aug 21, 2013 at 17:45
@Ben, wow. Honestly I don't even remember changing the title. I totally agree with you. My mistake on that big time! — eatonphil
– eatonphil, Commented Aug 21, 2013 at 17:47
While not central to the discussion, you should really be using ANSI-92 SQL JOIN syntax. — Aaron
– Aaron, Commented Aug 21, 2013 at 17:50

Ben · Accepted Answer · 2013-08-21 18:00:55Z

5

The input to TO_DATE() is two strings; even though you're using SQL*Plus substitution variables, you still need to enforce this.

Change

AND to_date(s.StartDate, 'Mon DD, YYYY') = to_date(&StartDate, 'Mon DD, YYYY')

to

AND to_date(s.StartDate, 'Mon DD, YYYY') = to_date('&StartDate', 'Mon DD, YYYY')

Are you sure s.StartDate is a character in this format? It sounds like it's already a date (I hope), in which case you don't need the TO_DATE() but it might be worth using TRUNC() on the date to remove the time portion.

To edit my answer based on the comments I don't believe you've changed this in the correct way. It should be:

AND trunc(s.StartDate) = to_date('&StartDate', 'Mon DD, YYYY')

Note the TRUNC() and the ' around '&StartDate'

edited Aug 21, 2013 at 18:00

answered Aug 21, 2013 at 17:38

Ben

53.1k36 gold badges133 silver badges156 bronze badges

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

Collectives™ on Stack Overflow

SQL Query gives missing parenthesis error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related