Re: need sql statement for complex address search

sean nakasone 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.

Searches for what?
In what version of Oracle?

Is your intention a query such as this?

create table test (
aid NUMBER(3),
my_prefix VARCHAR2(20),
my_number VARCHAR2(20));

INSERT INTO test VALUES (1, '12','345');
INSERT INTO test VALUES (2, '12',NULL);
INSERT INTO test VALUES (3, NULL,'345');

SELECT aid
FROM test
WHERE (my_prefix || '-' || my_number) = '12-345' UNION ALL
SELECT aid
FROM test
WHERE my_number = '12-345';

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org