SQL select
SELECT obj.object_type
FROM apps.opt_log_issue log, discov.opt_changed_database_objects obj
WHERE
--(UPPER(log.resolution(+)) like '%'||obj.object_name||'%'
-- and trunc(log.resolution_date(+)) - trunc(to_date(substr(obj.last_ddl_time,1,11),'dd-mon-yy')) <=5
-- and trunc(log.resolution_date(+)) - trunc(to_date(substr(obj.last_ddl_time,1,11),'dd-mon-yy')) >=0)
OR
(UPPER(log.resolution(+)) like '%'||obj.object_name||'%'
and log.resolution_date(+) is null)
/
Software/Hardware used:
Software/Hardware used:
ASKED:
November 28, 2008 11:09 PM
UPDATED:
December 1, 2008 8:15 PM
Answer Wiki:
You cannot have an outer join as operand of an OR logical operator.
For example, if you have a where clause like this:
select ...
from ...
<b>where <condition_a> <i>OR</i> <condition_b></b>
You will get ORA-01719 if either condition_a or condition_b contains an outer join.
The action suggested by the documentation is:
"If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select where (B))", so in your case, I would try:
<pre>SELECT obj.object_type
FROM apps.opt_log_issue log, discov.opt_changed_database_objects obj
WHERE
(UPPER(log.resolution(+)) like '%'||obj.object_name||'%'
and trunc(log.resolution_date(+)) - trunc(to_date(substr(obj.last_ddl_time,1,11),'dd-mon-yy')) <=5
and trunc(log.resolution_date(+)) - trunc(to_date(substr(obj.last_ddl_time,1,11),'dd-mon-yy')) >=0)
AND NOT
(UPPER(log.resolution(+)) like '%'||obj.object_name||'%'
and log.resolution_date(+) is null)
UNION ALL
SELECT obj.object_type
FROM apps.opt_log_issue log, discov.opt_changed_database_objects obj
WHERE
UPPER(log.resolution(+)) like '%'||obj.object_name||'%'
and log.resolution_date(+) is null</pre>
If you want to provide more information, please do it in the discuss section.
To see all answers submitted to the Answer Wiki: View Answer History.
Comments from From QAQA:
I got the error blow for the above statement if I remove the ‘–’ WHY?.
7 (UPPER(log.resolution(+)) like ‘%’||obj.object_name||’%’
8* and log.resolution_date is null)
SQL> /
and trunc(log.resolution_date(+)) – trunc(to_date(substr(obj.last_ddl_time,1,11),’dd-mon-yy’
ERROR at line 5:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN
Hi:
Thank you fo r your code but i got the same error.
Hi Sir:
Just in case I didn’t explain clearly, I want to tell you what I want to accomplish here. The following code worked find, but now I want to add something so that I can skip the date checking if the resolution_date field is blank.
SELECT obj.object_type
FROM apps.opt_log_issue log, discov.opt_changed_database_objects obj
WHERE (UPPER(log.resolution(+)) like ‘%’||obj.object_name||’%’
and trunc(log.resolution_date(+)) – trunc(to_date(substr(obj.last_ddl_time,1,11),’dd-mon-yy’)) <=5
and trunc(log.resolution_date(+)) – trunc(to_date(substr(obj.last_ddl_time,1,11),’dd-mon-yy’)) >=0)
Thank you for your help.
Are you sure the suggested code gives you the same error ?
I find it really strange, because the query doesn’t include ORs or INs, and those are the only operators that will cause ORA-01719 errors.
What is the error’s line number with the new code ?