Home » SQL & PL/SQL » SQL & PL/SQL » Problem with OR
Problem with OR [message #262796] Tue, 28 August 2007 05:19 Go to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
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'))) /*OR ( s.zaklad_id = '1'
AND s.jednostka_id = '18651' AND s.rodzaj LIKE 'R'
AND s.klasyfikacja_id 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')
ORDER BY s.kst, s.numer, s.nazwa

In this query when OR is not in brackets everything works. But when it is like above, I get on st.st_srodek s ORA 01722 saying that it is invalid number. It is not a number but a table so what is it about? Please help.
Re: Problem with OR [message #262800 is a reply to message #262796] Tue, 28 August 2007 05:23 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
As I told you in another thread

TO_NUMBER(NVL(SUBSTR('01-STY-07',1,4),r.rok)) is the issue

TO_NUMBER(NVL(SUBSTR('01-STY-07',1,4),r.rok))
will equate to To_Number('01-S')
Which, yep you guessed it is not a valid number as well as the whole thing being a pretty daft bit of code. I'm not even looking at the structure of the logical operators, fix this expression to make sense.
Re: Problem with OR [message #262818 is a reply to message #262796] Tue, 28 August 2007 05:42 Go to previous messageGo to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
Thank you it helped... I didn't read your message careefully the last time, sorry... The query is generated automatically so i didn't write that... it came from forms:P
Re: Problem with OR [message #262855 is a reply to message #262818] Tue, 28 August 2007 06:50 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You didn't read mine either:

Michel Cadot wrote on Tue, 28 August 2007 10:57
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


Regards
Michel
Previous Topic: Refresh on MATERIALIZED VIEW
Next Topic: PL/SQL Crosstab
Goto Forum:
  


Current Time: Sat Dec 07 03:33:38 CST 2024