Home » SQL & PL/SQL » SQL & PL/SQL » Problem with a bracket
Problem with a bracket [message #262742] Tue, 28 August 2007 03:51 Go to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
When I execute this statement I get ORA-00907. I cant see where I did not include the bracket. Could anyone help?

SELECT
ID, zaklad_id, lokalizacja_id, jednostka_id, rachunek_id,
rodzaj, numer, nr, nazwa, opis, data_przyjecia, data_likwidacji,
status
FROM st.st_srodek s, (SELECT ROWNUM + 2004 rok
FROM st.ST_SLOWNIK_KST
WHERE ROWNUM <100 ) r
WHERE (s.srodek_id IN (SELECT srodek_id FROM ST.ST_SRODEK_V
WHERE 1=1 AND JEDNOSTKA_ID = 18651 AND STATUS IN ('A','W')ORDER BY nr DESC, numer DESC)) OR
( s.zaklad_id = '1'
AND s.jednostka_id = '18651'
AND s.rodzaj LIKE 'R'
AND s.klasyfikacja IN
(SELECT slownik_id FROM st.st_slownik
WHERE typ = 'KLASYFIKACJA' AND wartosc0 LIKE '50%'
AND status = 'A' ))
AND r.rok >= TO_NUMBER(NVL(SUBSTR('01-STY-07',1,4),r.rok))
AND r.rok <= TO_NUMBER(NVL(SUBSTR('31-GRU-07',1,4),r.rok))
AND status IN ('A','W')
Re: Problem with a bracket [message #262745 is a reply to message #262742] Tue, 28 August 2007 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you execute it in SQL*Plus, it will tell you where it finds the error.

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Problem with a bracket [message #262747 is a reply to message #262742] Tue, 28 August 2007 04:00 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
s.zaklad_id = '1'
AND s.jednostka_id = '18651'

Are you sure ids are not number.
Difficult to read your post.
Re: Problem with a bracket [message #262748 is a reply to message #262742] Tue, 28 August 2007 04:00 Go to previous messageGo to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
The problem is with IN in this line
WHERE (s.srodek_id IN (SELECT srodek_id FROM ST.ST_SRODEK_V
still it seems that query is ok but the error occurs? I don't know what is the cause of this.
Re: Problem with a bracket [message #262750 is a reply to message #262742] Tue, 28 August 2007 04:05 Go to previous messageGo to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
Ok they are numbers I improved the query but still the same error.
Re: Problem with a bracket [message #262751 is a reply to message #262750] Tue, 28 August 2007 04:07 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Remove ORDER BY clause from a subquery.
Re: Problem with a bracket [message #262754 is a reply to message #262750] Tue, 28 August 2007 04:17 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Is this your EXACT query? or have you 'simplified' it?

what is the point of this?
TO_NUMBER(NVL(SUBSTR('01-STY-07',1,4),r.rok)) 

(and others like it) other than to cause an Ora-01722 error?
What is the point of taking a substring of a literal? What is the point of performing an NVL on a string literal that is not null. How's about you tell us what you have actually done. If the query is what you have actually done, then you have more problems than unbalanced parentheses.

Edited to add code tags

[Updated on: Tue, 28 August 2007 04:18]

Report message to a moderator

Re: Problem with a bracket [message #262758 is a reply to message #262742] Tue, 28 August 2007 04:21 Go to previous messageGo to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
Ok about NVL I did not write this part of code...Razz Thank you for removing ORDER BY advice, I'm grateful! Cool
Re: Problem with a bracket [message #262764 is a reply to message #262758] Tue, 28 August 2007 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, follow the guidelines.

Regards
Michel
Re: Problem with a bracket [message #262798 is a reply to message #262758] Tue, 28 August 2007 05:22 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Thank you for removing ORDER BY advice, I'm grateful!

That was possible because I understood your query (Slavs languages are quite similar, aren't they?); those guys from France, Scotland, Bangladesh, ... - they have no idea what you are selecting. So you see why it is important to study foreign languages, as well as read Forum Guides where available.
Previous Topic: Could you, please, explain this query? (merged & renamed by LF)
Next Topic: vsize(sysdate)
Goto Forum:
  


Current Time: Tue Dec 06 02:13:14 CST 2016

Total time taken to generate the page: 0.11820 seconds