| Problem with OR [message #262796] |
Tue, 28 August 2007 05:19  |
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  |
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 |
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 |
 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
