Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-05-2006
01:30 AM

12-05-2006
01:30 AM

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????

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-05-2006
05:12 AM

12-05-2006
05:12 AM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-05-2006
05:13 PM

12-05-2006
05:13 PM

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-05-2006
10:20 PM

12-05-2006
10:20 PM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-06-2006
07:01 PM

12-06-2006
07:01 PM

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??

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??

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-06-2006
07:23 PM

12-06-2006
07:23 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-12-2006
12:23 AM

12-12-2006
12:23 AM

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..