Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00907: missing right parenthesis
ORA-00907: missing right parenthesis [message #191855] Fri, 08 September 2006 07:00 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a query and I want to get the alias value of the select statement as "basedate". But I am getting an error "ORA-00907: missing right parenthesis".
select SIGN(TO_DATE(
                         (SELECT DECODE(TO_CHAR(SYSDATE,'d'),
                            1,TO_CHAR(SYSDATE,'dd-mon-yy'),
                            2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
                            3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
                            4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
                            5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
                            6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
                              TO_CHAR(SYSDATE-7,'dd-mon-yy')
                                         )
                            FROM dual
                           ) BaseDate
                        )
                       -TO_DATE(po.needdate,'dd-mon-yy')
                    )
from stsc.planorder po


While the below code is running and not giving error:
SELECT TO_DATE((SELECT DECODE(TO_CHAR(SYSDATE,'d'),
                                       1,TO_CHAR(SYSDATE,'dd-mon-yy'),
                                        2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
                                        3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
                                        4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
                                        5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
                                        6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
                                          TO_CHAR(SYSDATE-7,'dd-mon-yy')
                                  )
                        FROM dual
                     )
 ) basedate
 FROM dual


Please advice as how to run the first query so that it doesn't give the error.

Thanks,
Mona
Re: ORA-00907: missing right parenthesis [message #191862 is a reply to message #191855] Fri, 08 September 2006 07:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You cannot give an alias ("basedate") to the part of an expression.

MHE
Re: ORA-00907: missing right parenthesis [message #191865 is a reply to message #191862] Fri, 08 September 2006 07:42 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Is there no way, as I have to the Select query many times for which I am using basedate.
I have to apply some conditions too.
Please advice if I can use it in some other way.


Thanks,
Mona
Re: ORA-00907: missing right parenthesis [message #191873 is a reply to message #191865] Fri, 08 September 2006 07:57 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Why not just select the expression for basedate into a variable and then use that variable?
Re: ORA-00907: missing right parenthesis [message #191880 is a reply to message #191873] Fri, 08 September 2006 08:06 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I am using it in a file which has to be converted to csv. In that file I can't do this as this will need a pl/sql block and i faced problem exporting file using pl/sql block.
Re: ORA-00907: missing right parenthesis [message #191885 is a reply to message #191880] Fri, 08 September 2006 08:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
In SQL*Plus, not pl/sql

column basedate new_value basedate

SELECT DECODE(TO_CHAR(SYSDATE,'d'),
            1,TO_CHAR(SYSDATE,'dd-mon-yy'),
            2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
            3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
            4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
            5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
            6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
              TO_CHAR(SYSDATE-7,'dd-mon-yy')) basedate
   FROM dual
/

select to_date('&basedate','dd-mon-yy') - TO_DATE(po.needdate,'dd-mon-yy')
from stsc.planorder po;


I simplified your original SELECT statement. TO_DATE was not needed in this case.

Is po.needdate a DATE column (it better be!)? If so, you don't need the TO_DATE in the second SELECT statement.

[Updated on: Fri, 08 September 2006 08:22]

Report message to a moderator

Re: ORA-00907: missing right parenthesis [message #191891 is a reply to message #191885] Fri, 08 September 2006 08:48 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Joy,
Thanks for your great advice. Actually my query is too big with some conditions. I am attaching here my file for a better understanding. I tried using your suggestion but it didn't worked the way I wanted. Please see my sql file once. I have to export a csv file from the sql file.

Thanks,
Mona
  • Attachment: Grey.sql
    (Size: 27.89KB, Downloaded 219 times)

[Updated on: Fri, 08 September 2006 09:05]

Report message to a moderator

Re: ORA-00907: missing right parenthesis [message #191899 is a reply to message #191891] Fri, 08 September 2006 09:21 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I think it would be "cleaner" to create a function and pass in a NUMBER variable that you can add or subtract from the SYSDATE in your repeated code.

Not clear as to what you mean by "didn't worked the way I wanted." It worked the way I wanted.
Re: ORA-00907: missing right parenthesis [message #192145 is a reply to message #191899] Mon, 11 September 2006 04:00 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Joy,
I was able to create the alias the way you suggested for the basedate. But when I use the columns in my Select statement after defing at the top, I see that the select statement for the columns defined execute first. And I just want them to get the value stored in the variable column but it displays one row after another. Even if I use NOPRINT, it doesn't display but its running and takes lot of time. I only have to export the data in csv.

Please have a look at the csv file attached.
COLUMN basedate new_value basedate
COLUMN period1 new_value period2
COLUMN period2 new_value period2

SELECT DECODE(TO_CHAR(SYSDATE,'d'),
            1,TO_CHAR(SYSDATE,'dd-mon-yy'),
            2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
            3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
            4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
            5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
            6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
              TO_CHAR(SYSDATE-7,'dd-mon-yy')) basedate
   FROM dual;

SELECT po.qty period1
  FROM stsc.planorder po
 WHERE po.needdate >= TO_DATE('10-sep-2006','dd-mon-yy') 
   AND po.needdate <  (TO_DATE('10-sep-2006','dd-mon-yy') + 6);

SELECT po.qty period2
  FROM stsc.planorder po
 WHERE po.needdate >= (TO_DATE('10-sep-2006','dd-mon-yy') + 7)
   AND po.needdate <  (TO_DATE('10-sep-2006','dd-mon-yy') + 13);
    

I only want the above script to store the value in the column and then I can use the columns basedate and period2 in my Select statement which needs to be exported to csv.
Actually the condition for qty to be displayed is :
if {PLANORDER.NEEDDATE} in {@Basedate} to ({@Basedate} + 6) then {PLANORDER.QTY} else 0

Thanks,
Mona
  • Attachment: Grey1.sql
    (Size: 11.28KB, Downloaded 175 times)

[Updated on: Mon, 11 September 2006 04:17]

Report message to a moderator

Re: ORA-00907: missing right parenthesis [message #192172 is a reply to message #191855] Mon, 11 September 2006 06:18 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi Mona,


SELECT TO_DATE((SELECT DECODE(TO_CHAR(SYSDATE,'d'),
1,TO_CHAR(SYSDATE,'dd-mon-yy'),
2,TO_CHAR(SYSDATE-1,'dd-mon-yy'),
3,TO_CHAR(SYSDATE-2,'dd-mon-yy'),
4,TO_CHAR(SYSDATE-3,'dd-mon-yy'),
5,TO_CHAR(SYSDATE-4,'dd-mon-yy'),
6,TO_CHAR(SYSDATE-5,'dd-mon-yy'),
TO_CHAR(SYSDATE-7,'dd-mon-yy')
)
FROM dual
),'dd-mon-yy'
) basedate
FROM dual


todate format should be TO_date('12-sep-06','dd-mon-yy)..
Ex : select to_date('12-sep-06','dd-mon-yy') from dual;

This will work for you...
Thanks,

Mohan Reddy

Re: ORA-00907: missing right parenthesis [message #192175 is a reply to message #192172] Mon, 11 September 2006 06:23 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Mohan,
actually I have used this in this way only. The code is just to give a hint what I want. My question is something else.

Regards,
Mona
Re: ORA-00907: missing right parenthesis [message #192242 is a reply to message #192175] Mon, 11 September 2006 09:25 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Mona,
I could not understand your problem clearly. however, it seems you have to store the intermediate result set in variable form as you going to refer the same in subsequent process. so it is better to use "WITH clause".

Thanks,
Thangam
Re: Multiple Where clause in Select statement [message #192598 is a reply to message #192242] Tue, 12 September 2006 23:32 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Thangam,
My problem is to get a query in a single select statement where there are multiple conditions.

The condition is as :
if {PLANORDER.NEEDDATE} in {@Basedate} to ( {@Basedate} + 6) then {PLANORDER.QTY} else 0
if {PLANORDER.NEEDDATE} in {@Basedate + 7 } to ( {@Basedate} + 13) then {PLANORDER.QTY} else 0.........
if {PLANORDER.NEEDDATE} in {@Basedate } to ( {@Basedate} + 55) then {PLANORDER.QTY} else 0.........



I have to embedd the above condition for all the date differences in a single select and here the problem comes.
So I used DECODE as I didn't was getting how to put them all in select ....

Actually i want something like below but in a single SELECT statement :

SELECT SUM(po.qty) p1 
FROM planorder po 
WHERE
po.needdate BETWEEN TO_DATE ('&basedate','dd-mon-yy') AND (TO_DATE('&basedate','dd-mon-yy' ) + 6 ) 
, 
SELECT SUM(po.qty) p2 
FROM planorder po 
WHERE
po.needdate BETWEEN (TO_DATE ('&basedate','dd-mon-yy') + 7 ) AND (TO_DATE('&basedate','dd-mon-yy' ) + 13 ) , ..............
,
SELECT SUM(po.qty) p2
FROM planorder po 
WHERE
po.needdate BETWEEN TO_DATE ('&basedate','dd-mon-yy') AND TO_DATE('&basedate','dd-mon-yy' ) + 55 
GROUP
BY item; 


I have to display the answer in a single row for all the date differences.
And I am not getting the idea of displaying the above in single select with different WHERE condition where the date difference is different for all SUM(po.qty).

Thanks,
Mona

[Updated on: Wed, 13 September 2006 06:36]

Report message to a moderator

Multiple Where clause in Select statement [message #192633 is a reply to message #192242] Wed, 13 September 2006 02:09 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Dear All,
any hint would help me to go forward.


Thanks,
Mona

[Updated on: Wed, 13 September 2006 02:11]

Report message to a moderator

Re: Multiple Where clause in Select statement [message #192728 is a reply to message #192598] Wed, 13 September 2006 06:44 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Try this query and let me know the status,because i do not test here...

SELECT 
       ITEM, 
       SUM(DECODE(GREATEST(NEEDDATE,TO_DATE('&basedate','DD-MON-YY')),LEAST(NEEDDATE,TO_DATE('&basedate','DD-MON-YY' )+6),QTY,0)) "1-6 DAYS",
       SUM(DECODE(GREATEST(NEEDDATE,TO_DATE('&basedate','DD-MON-YY') + 7),LEAST(NEEDDATE,TO_DATE('&basedate','DD-MON-YY' ) + 13),QTY,0)) "7-13 DAYS",
       SUM(DECODE(GREATEST(NEEDDATE,TO_DATE('&basedate','DD-MON-YY')),LEAST(NEEDDATE,TO_DATE('&basedate','DD-MON-YY' )+55),QTY,0)) "1-55 DAYS"
FROM PLANORDER GROUP BY ITEM


Thanks,
Thangam
Re: Multiple Where clause in Select statement [message #192907 is a reply to message #192728] Thu, 14 September 2006 03:38 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks a lot Thangam. This query worked the way I wanted.


Thanks again,
Mona
Previous Topic: Formatting strings
Next Topic: ORA-04021 Error while recreating a Package
Goto Forum:
  


Current Time: Tue Dec 06 10:13:32 CST 2016

Total time taken to generate the page: 0.08994 seconds