Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sql sums and full outer join

user521233Mar 2 2016 — edited Mar 3 2016

I don't seem to know what I'm doing. I was hoping the following would return all records and sums but it doesn't seem to do a full outer join on the columns identified. I'm joining the same table to itself.

I know that there are records for both stat_type = 192 and stat_type = 191 but that they do not have common keys. how do I fix this?

  select sum(a.stat_value),sum(a.stat_overtime),sum(c.stat_value),sum(c.stat_overtime),a.home,trunc(a.entry_date),b.user_name,decode(a.SUPERVISION,0,'ITSTNS','ITSTS') from tbl_datax a, tbl_users b, tbl_datax c

   where b.user_id = a.entry_posted_by

   and a.stat_type = 192

   and c.home = a.home

      and trunc(c.entry_date) = trunc(a.entry_date)

      and c.entry_posted_by = a.entry_posted_by

      and c.stat_type = 191

      and decode(c.SUPERVISION,0,'ITSTNS','ITSTS') = decode(a.SUPERVISION,0,'ITSTNS','ITSTS')

  group by a.home,trunc(a.entry_date),b.user_name,decode(a.SUPERVISION,0,'ITSTNS','ITSTS');   

appreciate any assistance!

This post has been answered by CarlosDLG on Mar 3 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 31 2016
Added on Mar 2 2016
9 comments
1,715 views