SQL select

65 pts.
Tags:
Oracle development
Oracle SQL
SELECT statement
SQL Query
SQL statements
WHERE statement
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) /

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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
    70,220 pointsBadges:
    report
  • QAQA
    Hi: Thank you fo r your code but i got the same error.
    65 pointsBadges:
    report
  • QAQA
    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.
    65 pointsBadges:
    report
  • carlosdl
    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 ?
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following