Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Outer join and OR operator

Outer join and OR operator

Outer join and OR operator

We have an application (written in ASP) that displays several columns from several tables in different HTML inputs.  One of the HTML inputs is for employee names.  The actual fullname of the employee is divided into 3 columns LAST_NAME, FIRST_NAME, and MIDDLE_INTIAL but displayed as a single value in the client interface.

One of the functional requirements is for the user to be able to search (select records) on any given input including this one.  I know that in the select statment we can do (assuming that user is passing smith as the name to search on):

Where upper(personnel.last_name) like upper('smith%')
or upper(personnel.first_name) like upper('smith%')

The problem is the select statement includes several outer joins and we get a ORA-01719 error "outer join operator (+) not allowed in operand of OR or IN"

In other database systems I've used (called INFO) you can create a "redefined" column that actually references multiple columns dynamically to determine its value.  For example, I could create a FULL_NAME column as FISRT_NAME+" "+MIDDLE_INITIAL+" "+LAST_NAME

If I inserted JOHN H DOE into FIRST_NAME,MIDDLE_INITIAL,LAST_NAME the FULL_NAME would be automaticlly be populated.  It would also react to updates to any of the columns that defined it.

Is this something that Oracle can do?  If not, is there some way of including an OR with an outer join?


RE: Outer join and OR operator

You could have a trigger on your table that, when a row was inserted or updated, populated your FULL_NAME column in the way you want.

I do this for telephone numbers -- removing ( and ) and - characters in a hidden column to make searches easier.

Cargill's Corporate Web Site

RE: Outer join and OR operator

   In addition to Mike's idea above, you can consider
using unions of select statements to get around the
inability to use OR and (+) in the same select stmt.


oracle, vb, some javascript

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close