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

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
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try making it a left outer join.

...
Left outer join SITE_INCOME si
...
Russ SuterSenior Software DeveloperCommented:
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.
slightwv (䄆 Netminder) 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!
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

sdstuberCommented:
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 SpecialistCommented:
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 SpecialistCommented:
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 SpecialistCommented:
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 SpecialistCommented:
I hate messing up so badly but the si. qualifier should be removed from the subquery :-(
sdstuberCommented:
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
sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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)
SQLSearcherAuthor Commented:
Thank you for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.