Complicated join returning redundant result set.

[shyamal]

SELECT ps_number "pf_number",
( (((1-sign(1-sign(ps_quantity))) * ABS(ps_clos_net_amount)) +
((1-sign(1+sign(ps_quantity))) * (-1 * ABS(ps_clos_net_amount))) +
((1-abs(sign(ps_quantity))) * (-1 * ABS(ps_clos_net_amount)))) * (CRCFXRate.fx_rate / SRCFXRate.fx_rate) ) "pf_cash_balance",
ps_clos_settle_code "mn_code" ,
(CRCFXRate.fx_rate / SRCFXRate.fx_rate) "mn_fx_rate"


FROM position t1(INDEX = XPKPosition),
WorkAIMRClient t2(INDEX = XPKWorkAIMRClient),
WorkAIMREndingFXRate CRCFXRate (INDEX =XPKWorkAIMREndingFXRate),
WorkAIMREndingFXRate SRCFXRate (INDEX =XPKWorkAIMREndingFXRate),
activity t5(INDEX = XPKActivity),
accttype t6(INDEX = XPKaccttype)
WHERE t1.ps_number = t2.pf_number
AND t1.ps_number = t5.ac_number
AND t5.ac_number = t2.pf_number
AND t1.ps_book_num = t5.ac_book_num
AND t1.ps_acct_type = t5.ac_acct_type
AND t1.ps_acct_type = t6.at_code
AND t5.ac_acct_type = t6.at_code
AND t1.ps_clos_trade_code = t5.ac_settle_code
AND t1.ps_clos_settle_code = SRCFXRate.fx_code
AND t1.ps_clos_settle_code = t5.ac_settle_code
AND CRCFXRate.fx_code = t2.pf_cl_crc
AND t5.ac_cancel_flag IS NULL
AND t6.at_not_held = 'Y'

This result should return one row but instead it is returning 14 rows all with same data. The row return, is accurate in its content however.

Thanks in advance
Shyamal

 

[MalcolmW]

That type of result set usually means the joining between the tables is incomplete. Without knowing more about the tables, it really isn't possible to suggest a solution.
To troubleshoot this, you could create a simple query
SELECT ps_number
FROM position t1
INNER JOIN WorkAIMRClient t2 ON t1.ps_number = t2.pf_number
WHERE ps_number = some value

which I assume should just return one record. Then start adding the other tables one by one (leave the SELECT statement alone), and testing to see if you still are getting one record. This will identify which join(s) is producing the cartesian product.

 

[listener22]

or, which one has a one to many relationship.