Re: NVL & outerjoin question
From: Sean <sean_bu_at_yahoo.com>
Date: 20 Nov 2001 19:01:09 -0800
Message-ID: <999ced66.0111201901.e08283f_at_posting.google.com>
> 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
Received on Wed Nov 21 2001 - 04:01:09 CET
Date: 20 Nov 2001 19:01:09 -0800
Message-ID: <999ced66.0111201901.e08283f_at_posting.google.com>
Here you go:
sbu/sbu_at_o817> select * from product_a ;
PRODUCT_NR DESCRIPTIO
---------- ----------
080090 tanktop
sbu/sbu_at_o817> select * from Actuals_b;
PRODUCT_NR COU SHIPMENTS
---------- --- ---------
080090 GB 6 080090 GB 6 080090 NL 9 080090 SW 0
sbu/sbu_at_o817> select * from Country_c;
COU COUNTRY_NA
--- ----------
NL NNNLLL
GB GGGBBB
SW SSSWWW
sbu/sbu_at_o817> select c.Country_code, a.Product_nr, a.Description, sum(b.Shipments)
2 from
3 Product_a a, Actuals_b b, Country_c c
4 where a.product_nr=b.product_nr and b.country_code=c.country_code
5 group by c.Country_code, a.Product_nr, a.Description
6 /
COU PRODUCT_NR DESCRIPTIO SUM(B.SHIPMENTS)
--- ---------- ---------- ---------------- GB 080090 tanktop 12 NL 080090 tanktop 9 SW 080090 tanktop 0 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
Received on Wed Nov 21 2001 - 04:01:09 CET