Re: need sql statement for complex address search

On Jul 30, 1:30 pm, sean nakasone <seannakas..._at_yahoo.com> wrote:
> we have a database that stores addresses. the address number is stored in
> a field called MY_NUMBER. Now if the address has two numbers i.e. 12-345,
> then the 12 will be in a field called MY_PREFIX. I need to come up with 1
> SQL statement (and it can't be PL/SQL) that will check if there's a hypen
> and if so, search in both the MY_PREFIX and MY_NUMBER fields. If there is
> no hyphen, then it should only search in the MY_NUMBER field. The reason
> it can't be a PL/SQL statement is because we are using an application that
> interfaces with oracle and that application will only accept one SQL
> statement and will not support PL/SQL.

You'll need to write a function which returns the associated address data based upon the address number you supply; I believe it's the only way you'll get this 'two-way' select you want. You can then call this function from a single SELECT statement and satisfy your application requirements.

David Fitzjarrell Received on Mon Jul 30 2007 - 13:59:29 CDT