Re: REQ: Help Populating a Zero using NVL

CreepyHamster <kb4632_at_mts.net> wrote:

>We are trying to pull out a report of result codes from 2 tables, and
>we have run into a problem when there are zero entries for a specific
>day, with a specific Result Code.
>
>I am using the following query:
>
> SELECT celd.subcode,arct.CODE_DESCRIPTION,nvl(COUNT(*),'0') AS
>result_count
> FROM cgrd_event_log_detail celd,cgrd_action_result_codes arct
> WHERE date_time >= TO_DATE('2004-04-13','YYYY-MM-DD')
> AND date_time < TO_DATE('2004-04-14','YYYY-MM-DD')
> AND celd.callbase IN ('bsld031')
> AND celd.subcode = arct.subcode
> AND (celd.code_table = arct.code_table
> OR arct.code_table = 'system')
> GROUP BY celd.subcode,arct.CODE_DESCRIPTION
> ORDER BY celd.subcode
>
>and it returns something like this (TRUNCATED):
>
>925 Modem 693
>926 SIT Vacant 3
>929 SIT Intercept 638
>930 SIT Unknown 160
>932 Abandoned 105
>940 PassThru RNA 60
>941 PassThru Busy 13
>944 PassThru Ans Mach 956
>945 PassThru Modem 13
>
>However, there is are codes 927 and 928, which happened to have no
>results for that day. How can I modify the above query to return a
>zero for those codes. This is needed to populate an Excel Sheet from
>PERL.
>
>Any help would be greatly appreciated

With some missing values in the linked table, you need to use a LEFT OUTER JOIN to get all the results..

See the docs, as the syntax can be different depending on version. Received on Tue Apr 20 2004 - 12:40:42 CDT