Two queries almost identical give different results see below:
Why is this query getting the results I want:
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from actuals_yr1 a1, hyper_accounts ha
where a1.account = ha.account
and a1.account_type = 'F'
and a1.entity = '10010-003'
and a1.funding_type in ('G10010','G10050')
group by a1.entity, a1.funding_type, a1.account,ha.parent
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from actuals_yr1 a, hyper_accounts ha
where a.account = ha.account
and a.entity = '10010-003'
group by a.entity,ha.parent
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
<code>select
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
from
(
select
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from actuals_yr1 a1, hyper_accounts ha
where a1.account = ha.account
and a1.bien = '2025'
and a1.account_type = 'F'
and a1.entity = '10010-003'
and a1.funding_type in ('G10010','G10050')
group by a1.entity, a1.funding_type, a1.account,ha.parent
)
z
left join
(
select
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from actuals_yr1 a, hyper_accounts ha
where a.account = ha.account
and a.bien = '2025'
and a.account_type = 'E'
and a.entity = '10010-003'
group by a.entity,ha.parent
) x
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
;
</code>
select
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
from
(
select
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from actuals_yr1 a1, hyper_accounts ha
where a1.account = ha.account
and a1.bien = '2025'
and a1.account_type = 'F'
and a1.entity = '10010-003'
and a1.funding_type in ('G10010','G10050')
group by a1.entity, a1.funding_type, a1.account,ha.parent
)
z
left join
(
select
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from actuals_yr1 a, hyper_accounts ha
where a.account = ha.account
and a.bien = '2025'
and a.account_type = 'E'
and a.entity = '10010-003'
group by a.entity,ha.parent
) x
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
;
Results:
10010-003 G10010 911990 5574605 10010-003 E11 F11 5640568
10010-003 G10010 912990 2777174 10010-003 E12 F12 2810041
10010-003 G10050 900990 98830 null null F00 null
But this query against the same data does not?
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from actuals_yr1 a1, hyper_accounts ha
where a1.account = ha.account
and a1.account_type = 'F'
group by a1.entity, a1.funding_type, a1.account,ha.parent
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from actuals_yr1 a, hyper_accounts ha
where a.account = ha.account
group by a.entity,ha.parent
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
<code>select
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
from
(
select
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from actuals_yr1 a1, hyper_accounts ha
where a1.account = ha.account
and a1.bien = '2025'
and a1.account_type = 'F'
group by a1.entity, a1.funding_type, a1.account,ha.parent
)
z
left join
(
select
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from actuals_yr1 a, hyper_accounts ha
where a.account = ha.account
and a.bien = '2025'
and a.account_type = 'E'
group by a.entity,ha.parent
) x
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
;
</code>
select
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
from
(
select
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from actuals_yr1 a1, hyper_accounts ha
where a1.account = ha.account
and a1.bien = '2025'
and a1.account_type = 'F'
group by a1.entity, a1.funding_type, a1.account,ha.parent
)
z
left join
(
select
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from actuals_yr1 a, hyper_accounts ha
where a.account = ha.account
and a.bien = '2025'
and a.account_type = 'E'
group by a.entity,ha.parent
) x
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
;
Results:
10010-003 G10010 911990 5574605 10010-003 E11 F11 5640568
10010-003 G10010 912990 2777174 10010-003 E12 F12 2810041
10020-057 G10020 900990 2127 10020-057 E00 F00 2127
10020-057 G10020 911990 14352 10020-057 E11 F11 14352
10020-057 G10020 930990 1971 10020-057 E30 F30 1971