SQL Left select, to get a zero when no data is returned

SQLSearcher
SQLSearcher used Ask the Experts™
on
Hello Experts Exchange
I am using Oracle to select data from a database to get a sum of a value, but I want a record when no data is returned.

This is my query;
Select DH.DH_Position,L.LOC_NAME, DH.DH_Name,
sum(NVL(si.si_income1,0)) as Value
From LOCATION_GROUP_TYPES gt
inner join LOCATION_GROUPS g
on gt.LGT_ID = g.LG_LGT_ID
inner join locations l
on g.LG_LOC_ID = l.LOC_ID
Left join SITE_INCOME si
on si.si_loc_id = l.loc_id
inner join Department_Headings DH
on DH.DH_Position = 1
Where (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY') >= 2016)
and gt.LGT_REGION is not null
and si.si_year =  2016
and si.si_week Between 10 and 10 and gt.LGT_DESCRIPTION = 'NORTH WEST AND NORTH MIDLANDS' and L.LOC_NAME = 'CHORLTON' 
group by DH.DH_Position,L.LOC_NAME, DH.DH_Name

Open in new window


But the query does not work, how do I change so that if no data in the SITE_INCOME table I still get a record stating zero.

Regards

SQLSearcher
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Try making it a left outer join.

...
Left outer join SITE_INCOME si
...
Russ SuterSenior Software Developer

Commented:
Without knowing your data structure I can't be certain but...

What I'm seeing is that your LEFT JOIN for SITE_INCOME is dependent upon two INNER JOINS on LOCATION_GROUPS and locations. My guess is that your join is omitting the record you want because of one or both of those inner joins.

@slightwv - All LEFT joins are OUTER joins. The OUTER keyword is optional.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>The OUTER keyword is optional.

Thanks for the correction.  There is the one new thing I learned today...  I can stop learning for the day!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
When you do this in the WHERE clause

 AND si.si_year = 2016
 AND si.si_week BETWEEN 10 AND 10


you are effectively negating the OUTER JOIN, and turning it into an INNER JOIN.

The reason this happens is the WHERE clause is applied after the join conditions are resolved.

So, if you don't have an SI row from the join, you will get NULL values returned by the outer join,
But, your where clause will then reject those rows because NULL can never satisfy those conditions.

The same is true for conditions that compare results between tables in the where clause; but the explicit value comparisons make it more obvious
To fix this,  move those conditions from the WHERE into the ON conditions of the outer join
awking00Information Technology Specialist

Commented:
Try this -

Select DH.DH_Position,L.LOC_NAME, DH.DH_Name,
[b]sum(si.si_income1[/b]) as Value
From LOCATION_GROUP_TYPES gt
inner join LOCATION_GROUPS g
on gt.LGT_ID = g.LG_LGT_ID
inner join locations l
on g.LG_LOC_ID = l.LOC_ID
[b]left join
(select si_loc_id, nvl(si_income1,0) si_income1
 from site_income) si
[/b]on si.si_loc_id = l.loc_id
inner join Department_Headings DH
on DH.DH_Position = 1
Where (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY') >= 2016)
and gt.LGT_REGION is not null
and si.si_year =  2016
and si.si_week Between 10 and 10 and gt.LGT_DESCRIPTION = 'NORTH WEST AND NORTH MIDLANDS' and L.LOC_NAME = 'CHORLTON' 
group by DH.DH_Position,L.LOC_NAME, DH.DH_Name;

Open in new window

awking00Information Technology Specialist

Commented:
Apparently bold can not be embedded in code, which is what I attempted to do. I also didn't notice that the references to si.year and si.week would have to be included in the subquery aliased as si.
awking00Information Technology Specialist
Commented:
Without code brackets -
Select DH.DH_Position,L.LOC_NAME, DH.DH_Name,
sum(si.si_income1) as Value
From LOCATION_GROUP_TYPES gt
inner join LOCATION_GROUPS g
on gt.LGT_ID = g.LG_LGT_ID
inner join locations l
on g.LG_LOC_ID = l.LOC_ID
left join
(select si_loc_id, nvl(si_income1,0) si_income1
 from site_income
 where si.si_year = 2016
 and si.si_week between 10 and 10) si
on si.si_loc_id = l.loc_id
inner join Department_Headings DH
on DH.DH_Position = 1
Where (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY') >= 2016)
and gt.LGT_REGION is not null
and gt.LGT_DESCRIPTION = 'NORTH WEST AND NORTH MIDLANDS' and L.LOC_NAME = 'CHORLTON'
group by DH.DH_Position,L.LOC_NAME, DH.DH_Name;
awking00Information Technology Specialist

Commented:
I hate messing up so badly but the si. qualifier should be removed from the subquery :-(
Most Valuable Expert 2011
Top Expert 2012

Commented:
using the subquery method doesn't help

LEFT JOIN
(select si_loc_id, nvl(si_income1,0) si_income1

Open in new window

doesn't address the problem.
if there is no row, the NVL won't be executed, so a 0 won't be generated
Most Valuable Expert 2011
Top Expert 2012
Commented:
also,  don't apply NVL inside an aggregate,  instead, apply it to the result of the aggregate.
by applying it inside, you're forcing every row, null or not be be run through the aggregate not only is that needlessly inefficient, it also makes the aggregate do more work by processing rows that would have been null and hence skipped (never seen) by the aggregate.

So, if you want to push the year and week condidtions into the join, it should look something like this...

  SELECT dh.dh_position,
         l.loc_name,
         dh.dh_name,
         NVL(SUM(si.si_income1), 0) AS VALUE
    FROM location_group_types gt
         INNER JOIN location_groups g ON gt.lgt_id = g.lg_lgt_id
         INNER JOIN locations l ON g.lg_loc_id = l.loc_id
         LEFT JOIN site_income si
             ON si.si_loc_id = l.loc_id AND si.si_year = 2016 AND si.si_week BETWEEN 10 AND 10
         INNER JOIN department_headings dh ON dh.dh_position = 1
   WHERE     (loc_date_closed IS NULL OR TO_CHAR(loc_date_closed, 'YYYY') >= 2016)
         AND gt.lgt_region IS NOT NULL
         AND gt.lgt_description = 'NORTH WEST AND NORTH MIDLANDS'
         AND l.loc_name = 'CHORLTON'
GROUP BY dh.dh_position, l.loc_name, dh.dh_name
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
inner join Department_Headings DH on DH.DH_Position = 1

How many rows does this return?
   if only 1 then I would suggest using CROSS JOIN instead
   if it can return more than one it will multiply the SUM() results

Where (LOC_DATE_CLOSED is null or to_char(LOC_DATE_CLOSED,'YYYY') >= 2016)

I assume this column comes from Locations L and I assume it is a date
to_char(...'YYYY') returns a string
2016 is an integer

so you are converting a date to a string and comparing to an integer
that isn't efficient

this would be more efficient
Where (L.LOC_DATE_CLOSED is null or L.LOC_DATE_CLOSED >= to_date('2016-01-01','YYYY-MM-DD')

(if L.LOC_DATE_CLOSED is a timestamp then use to_timestamp()


and your original where clause directly references the left joined table,

and si.si_year =  2016
and si.si_week Between 10 and 10

so EVERY ROW MUST meet those 2 predicates, and hence NULL from that table is suppressed

To fix this either alter the where clause like this:

and ( (si.si_year =  2016 and si.si_week Between 10 and 10) OR si.si_year IS NULL)

OR (preferred) move those predicates into the join (this is shown in comments above)

Author

Commented:
Thank you for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial