Re: Tough (?) SELECT statement

hadd- home address
 wadd-work address

or something like
select id,hadd,decode(wadd,null,hadd,wadd) work_add from addr

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

"Poldek" <jgerio_at_hotmail.com> wrote in message news:app2ie$g5h$1_at_news.gazeta.pl...
> 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/
Received on Wed Oct 30 2002 - 19:30:43 CET