joint account issue

Database
Fan

joint account issue

I am having a lot of issue in getting accurate balances if a product is owned by more than one party. One way I thought of resolving the issue is to equally aportioned the balances. But I found s/t strange..pls refer below sql.

create table ptemp.PassbookPatyBalDec06
as
(
sel distinct a.paty_i,
abs(baln_a)/NumPaty as patyBal,
a.pdct_n,
a.acct_i /* need this field - otherwise get a diff result*/
from ptemp.passbookCustOtherAcctDec06 a
join ptemp.PassbookAcctBalDec06 b on a.acct_i = b.acct_i
join ptemp.NumPatyPassbookDec06 c on a.acct_i = c.acct_i /* use this table for numPaty */
)
with data
primary index (paty_i)
;
sel count (distinct paty_i),
sum (patyBal)
from ptemp.PassbookPatyBalDec06
; resulted in $44000 in sumPatyBal for sel count (distinct paty_i),
sum (patyBal)/1000000
from ptemp.PassbookPatyBalDec06

But if I create exactly the same temp table but without the acct_i field ie:

create table ptemp.PassbookPatyBalDec06
as
(
sel distinct a.paty_i,
abs(baln_a)/NumPaty as patyBal,
a.pdct_n,
from ptemp.passbookCustOtherAcctDec06 a
join ptemp.PassbookAcctBalDec06 b on a.acct_i = b.acct_i
join ptemp.NumPatyPassbookDec06 c on a.acct_i = c.acct_i /* use this table for numPaty */
)
with data
primary index (paty_i)
;
I get 43660.14 in sumPatyBal. I can't understand why you need to include the acct_i to get the correct results ie 44000????
6 REPLIES
Enthusiast

Re: joint account issue

Hi,

I hope my example below will help you understand the difference.

Create Table A
(I Integer, J integer, K integer,L Integer);

Insert into A values(1,22,22,2);
Insert into A values(1,11,11,1);

The below query maps to your query which includes acct_id field

Select distinct I,J/k,L
from A;

Output two rows :

I (J/K) L
1 1 1
1 1 2

The below query maps to your query which does not include acct_id field

Select distinct I,J/k
from A;

Output (Only one row):
I (J/K)
1 1

Naturally the sum for the second query will always be less than the first. DISTINCT and no of columns in the query makes the difference.
Fan

Re: joint account issue

Thanks Guru.

So it seems that if you want balance by customer (when we apportioned balances by number of customers in the event of joint account), we need to include the accountID (in your example field L) in the sql to ensure that the balance output matches that overall balance by the accountID. What if L is exactly the same integer ie the same accountID belonging to 2 different party so in your example it is 1. Should we still include accountID??

Thanks again!

Enthusiast

Re: joint account issue

Can you answer my question based on the answer it would be easy to reply to your question?

1. What result are you expecting?

2. What are the Primary Index of the above mentioned three tables?

3. Do we need to really use DISTINCT (Create Table query) in the above query which you mentioned?

Fan

Re: joint account issue

using your example:

I - identifier (not used for calc - in my example it is custID)
J- balance(in my example it would be Balance by a customer thus the req of using distinct)
K - identifier (in my example it would be the same accountNumber)
L - identifier (in my example it would be the same as productNumber ie to identify diff products).

Both of my sql uses I; J/K; and L. But the first sql also include K in the source table. Thus 2 diff source tables one containing K the other not - however both of these derived tables have been sourced from the same sources involving joining 3 different tables.

I expected both results having the same number of rows...

Essentially my question is that why do we need to include the field acct_i in the derived table if we wish to get patyBalance??
Teradata Employee

Re: joint account issue

The problem is the DISTINCT in the query creating your table. Do a simple SELECT COUNT(*) from both tables and you will see the number of rows differs. In other words, there is at least one party with multiple accounts which have exactly the same apportioned balance; if you use SELECT DISTINCT it eliminates these duplicate rows.
Fan

Re: joint account issue

Thank you Supreme Being and others. Perhaps for future database design, we need identifier for joint acct and paty Balances based on agreed business rules..