Outer join and OR operator

[ChrisQuick]

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?

Thanks
[sig][/sig]

 

[MikeLacey]

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. [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br> [/sig]

 

[listener22]

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. [sig]<p>Jim<br><a href= > </a><br>oracle, vb, some javascript[/sig]