Re: SQL need help

Outer join?

Guang

On Thu, 3 Jul 2003, Sinardy Xing wrote:

> Hi,
>
>
> I have two tables
>
> 1. ALL_CUSTOMER
> CustNo: 1 to 100
> OtherDetail: bla bla bla
>
> 2. ORDER
> CustNo
> TotalOrder
> DateOrder
>
>
> I want to display like this
>
>
>
> CustomerNo 1Total Order For This month = 101
>
> or
>
> CustomerNo 1 Total Order For This month = 0
>
> My SQL statement is like this
>
>
> SELECT a.custno, sum(b.total_order)
> from all_customer a, order b
> where b.custno (+) = a.custno
> and to_char(dateorder, 'month') = 'JUNE' (PROBLEM is HERE)
> group by a.custno
>
>
>
> When I run this statement for those customer that never order anything, my statement return No row returns
>
> How to get something like this
>
> CustomerNo 1 Total Order For This month = 0
>
>
>
> I tried this
>
> nvl(to_char(dateorder, 'month'), 'JUNE') = 'JUNE'
>
>
> Thanks
>
> Sinardy
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Sinardy Xing
> INET: SinardyXing_at_bkgcomsvc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  INET: gmei_at_incyte.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).