I have a query that performs multiple aggregations which are totally wrong. What I need to do is relate accounts with balances to account information (ie. name, address, etc.). Within its own table, the balances are fine, but when I perform an outer join and match only account #'s the quantities show the same value of -626,547,021??? I don't understand the significance of this number.
Query1.Account_Number,
XSUM(Query3.Account_Balance) as Account_Balance,
from
(select distinct
Table.Account_Number,
Table.Branch_Code,
Table.Client_Number,
(((((Table.Branch_Code || '-') || Table.Account_Number) || '-') || Table.Account_Type) || Table..Check_Digit) as AccountNum,
from
Namespace.Table.
where
(Table.Branch_Code between '000000001' and '0000008989') and
(Table2.Address_Type ="AA"))
)Query1
full outer join
(select
(((((Table3.Branch_Code || '-') || Table3.Account_Number) || '-') || Table3.Account_Type) || Table3.Check_Digit) as AccountNum,
Table3.Client_Number,
Table3.Branch_Code,
Table3.Account_Number,
Table3.Account_Type,
xsum(Query3.Account_Balance) for ((((((Table3.Branch_Code || '-') || Table3.Account_Numebr) || '-') || Table3.Account_Type) || '-') || Table3.Check_Digit)) as Account_Balance
from
Namespace.Table3.
group by
((((((Table3.Branch_Code || '-') || Table3.Account_Number) || '-') || Table3.Account_Type) || '-') || Table3.Check_Digit),
Table3.CLIENT_NUMBER,
Table3.BRANCH_CODE,
Table3.Account_NUMBER,
Table3.Account_Type,
Table3.Check_Digit
) Query3
on (Query1.AccountNum=Query2.AccountNum)
Thanks.
Hello my friend,
fun and games with SQL eh ?
The problem is this:
The numbers are changing because the first part of your query does not have a 1 to 1 row match for the second query, so when you join them, your XSUM() function has more account_balance values to add up, because of the multiplicity. I would predict that if you run the following block of SQL and look closely, you'll find that "AccountNum" is repeating for some rows.
select distinct
Table.Account_Number,
Table.Branch_Code,
Table.Client_Number,
(((((Table.Branch_Code || '-') || Table.Account_Number) || '-') || Table.Account_Type) || Table..Check_Digit) as AccountNum,
from
Namespace.Table.
where
(Table.Branch_Code between '000000001' and '0000008989') and
(Table2.Address_Type ="AA"))
If you look closely at this SQL and get the same multiplicity in both the first query and the second (assumably 1 row per AccountNum), I think you'll be much happier.
btw ... use INNER JOIN instead of FULL OUTER JOIN ;)
good luck!
CognosDave,
Thanks for the tip! In the back of my mind, I thought this was the case. I think it would be better if I execute the query by aggregating all accounts THEN perform the join.
CCSlice
Fun and games in SQL... is not a gold medal! But the boss will you give a shining star! ;D
I'm happy to help buddy ! ;)