Re: NVL & outerjoin question

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
Received on Wed Nov 21 2001 - 01:02:36 CET