Re: Tough (?) SELECT statement

maybe this will solve your problem:
hadd- home address
wadd-work address

select id, hadd, nvl((select wadd from addr a2 where a2.id = a1.id),hadd) wadd
from addr a1
/

"Chimera" <nchimera_at_yahoo.com> wrote in message news:7afde90.0210300724.461d5aab_at_posting.google.com...
> I'd like some help composing a select statment to perform the
> following:
>
> I have an ADDRESS table with the standard ADDRESS1, AADDRESS2, etc
> columns as well as ADDRESS_TYPE and NAMEID columns. A person (NAMEID)
> can have multiple entries (rows), differentiated by the ADDRESS_TYPE
> value- 'Home', 'Work', 'Mail', etc.
> To simplify, the 'Home' address always exists, but the others may not.
> The mental block I am having is to be able to select (in one row) both
> the 'Home' and 'Mail' addresses- but if the 'Mail' address does not
> exist then retrun the 'Home' address in it's place. SOmething like:
>
> SELECT HOME.ADDRESS1, HOME.ADDRESS2, MAIL.ADDRESS1,MAIL.ADDRESS2 FROM
> ADDRESS "MAIL", ADDRESS "HOME"
> WHERE MAIL.NAMEID=some_name_id AND HOME.NAMEID=some_name_id
> AND MAIL.ADDRESS_TYPE='Mail' AND HOME.ADDRESS_TYPE='Home'
>
> the problem is that if ther is no Mail address, nothing is returned
> for that NAMEID. I've tried a bunch of things (subquery with an NVL(),
> decode().. ) but I'm stumped- maybe it can't be done with one
> statement (or maybe this is trivial for someone out there) ?
>
>
> Thanks.

-- 
Serwis Usenet w portalu Gazeta.pl -> http://www.gazeta.pl/usenet/