Hi,
I'm having a problem with a query I've built. The query's purpose is to give a balance sheet of provident funds securities' holdings.
all works well but I needed to add the percentage of each security in relation of it's fund.
So I had to build a subquery that gives that divide the security to the total value of the fund (the bold item in the select statement).
But it doesn't work because it returns a result for each and every fund so the query fails (at most one record can be returned...).
I've tried to pass tb3_Accounts.tb3_LeadingFundCode from the main query as an alias to the subquery but that doesn't work, as well.
Anyone has an idea what am I doing wrong?
Thanks,
Code:
SELECT
tb5_Fund.tb5_FundName,
tb3_Accounts.tb3_LeadingFundCode as FundID,
DataIn.tb1_securitynum,
DataIn.tb1_descSecurity,
DataIn.tb1_Balance,
DataIn.tb1_FaceValue,
DataIn.tb1_ISIN AS ISIN,
tb20_InvestTool.tb20_InvestToolNum,
tb10_Afik.tb10_AfikIdEntity,
DataIn.tb1_Balance / (SELECT Sum(DataIn.tb1_Balance) AS SumOftb1_Balance
FROM tb5_Fund INNER JOIN (tb3_Accounts INNER JOIN DataIn ON tb3_Accounts.tb3_AccountNumber = DataIn.tb1_accountNumber) ON tb5_Fund.tb5_FundCode = tb3_Accounts.tb3_LeadingFundCode
GROUP BY tb5_Fund.tb5_FundCode, tb5_Fund.tb5_FundName, tb3_Accounts.tb3_LeadingFundCode
HAVING (((tb5_Fund.tb5_FundCode)= tb3_Accounts.tb3_LeadingFundCode))))
FROM
tb5_Fund INNER JOIN (tb20_InvestTool INNER JOIN
(tb16_currency INNER JOIN (tb11_AfikDerivative RIGHT JOIN (tb33_IRSSecurityot RIGHT JOIN (tb12_Nose INNER JOIN (tb9_ErpEntity INNER JOIN (tb32_DerivativeSec RIGHT JOIN (tb31_ForwardSecurityot RIGHT JOIN (tb17_CurrencyLink INNER JOIN (tb10_Afik INNER JOIN (tb15_Securities RIGHT JOIN (Megama RIGHT JOIN (tb3_Accounts INNER JOIN DataIn ON tb3_Accounts.tb3_PortfolioNumber = DataIn.tb1_codeFund) ON Megama.tb2_Megama_code = DataIn.tb1_securitynum) ON tb15_Securities.tb15_SecurityID = DataIn.tb1_securitynum) ON (tb10_Afik.tb10_AfikID = DataIn.tb1_afik) AND (tb10_Afik.tb10_Erp = DataIn.tb1_ERP)) ON (tb17_CurrencyLink.tb17_ErpCode = DataIn.tb1_ERP) AND (tb17_CurrencyLink.tb17_ErpCurrencyCode = DataIn.tb1_CurrencyCode)) ON tb31_ForwardSecurityot.tb31_SecurityNum = tb15_Securities.tb15_SecurityID) ON tb32_DerivativeSec.tb32_SecurityNum = tb15_Securities.tb15_SecurityID) ON (tb9_ErpEntity.tb9_ErpID = DataIn.tb1_ERP) AND (tb9_ErpEntity.tb9_EntityID = DataIn.tb1_Entity)) ON tb12_Nose.tb12_noseID = tb10_Afik.tb10_NoseID) ON tb33_IRSSecurityot.tb33_SecurityId = tb15_Securities.tb15_SecurityID) ON tb11_AfikDerivative.tb11_DerivativeID = tb32_DerivativeSec.tb32_DerivativeID) ON tb16_currency.tb16_CurrencyID = tb17_CurrencyLink.tb17_CurrencyID) ON tb20_InvestTool.tb20_InvestToolNum = tb10_Afik.tb10_InvestTool) ON tb5_Fund.tb5_FundCode = tb3_Accounts.tb3_LeadingFundCode
WHERE (((tb15_Securities.tb15_IsActiveRegister)=Yes) AND ((DataIn.tb1_Entity)=1))