Re: NVL & outerjoin question

christianboivin1_at_hotmail.com (Chris) wrote in message news:<da20daf0.0111201602.4fac9a4_at_posting.google.com>...
> Hi Joris,
>
> you have to look at basic sql syntax !
>
> try this :
>
> select b.Country_code, a.Product_nr, a.Description, sum(nvl(b.Shipments,0))
> from
> Product a, Actuals b, Country c
> where a.Product_nr = b.Product_nr(+)
> and b.Country_code = c.Country_code(+)
> group by b.Country_code, a.Product_nr, a.Description
>
> hth
>
> Chris
>
> jlenior_at_multiweb.nl wrote in message news:<3bfaaabd.558239_at_news.soneraplaza.nl>...
> > Product a Actuals b Country c
> > Product_nr Product_nr Country_code
> > Description Country_code Country_name
> > Shipments
> >
> > Tables Product, Actuals, Country.
> > If a certain product has no shipments it will have no row in the
> > actuals table but I would like a query where it shows up as 0
> > For instance procduct 080090 has never shipped to SW (sweden) but did
> > ship to GB and NL.
> >
> > A select statement like this
> >
> > select c.Country_code, a.Product_nr, a.Description, sum(b.Shipments)
> > from
> > Product a, Actuals b, Country c
> > group by c.Country_code, a.Product_nr, a.Description
> >
> > Will give these results
> >
> > GB 080090 tanktop 12
> > NL 080090 tanktop 9
> >
> > I would like to see the following results
> >
> > GB 080090 tanktop 12
> > NL 080090 tanktop 9
> > SW 080090 tanktop 0
> >
> > Tried a lot of outer joins and use of the function NVL but so far have
> > not managed to do it.
> > Any help on this one ?
> >
> > Thanks, Joris

Hi,

I'm sorry but that didn't work, still no results for SW. The content of the tables would be
Product.
080090 Tanktop

Actuals

080090    GB      12
080090    NL      9

Country
GB      Great Britain
NL      Netherlands
SW      Sweden

And the query should give a result of 0 for SW regardles of the fact if there are actuals for this country.

Joris. Received on Wed Nov 21 2001 - 09:48:10 CET