Re: Tough (?) SELECT statement

If you've got Oracle 8i or above, then you can easily do this with analytic functions:

SELECT DISTINCT

       nameid,
       FIRST_VALUE(address1) OVER (PARTITION BY nameid ORDER BY
                                   decode(address_type, 'Home', 1, 2))
as home1,
       FIRST_VALUE(address2) OVER (PARTITION BY nameid ORDER BY
                                   decode(address_type, 'Home', 1, 2))
as home2,
       FIRST_VALUE(address1) OVER (PARTITION BY nameid ORDER BY
                                   decode(address_type, 'Mail', 1,
'Home', 2, 3)) as mail1,
       FIRST_VALUE(address2) OVER (PARTITION BY nameid ORDER BY
                                   decode(address_type, 'Mail', 1,

Regards, Andy

nchimera_at_yahoo.com (Chimera) 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.
Received on Thu Oct 31 2002 - 12:59:06 CET