Wrong output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Wrong output

Author

Topic

vipin_jha123
Starting Member

31 Posts

Posted - 2011-03-17 : 02:39:31

Dear Sir ,
I have a question that the query given bbelow is running but showing no result(data).
when i am running the corelated query in partwise then it shows the data:-

Select SST.sol_id, SST.set_id, GAM.sol_id, SOL.sol_desc, GAM.foracid,
GAM.acct_name, GAM.cif_id, GAC.free_text_1, SMT.acct_status As
" SBA/CAA schemes", CAM.acct_status As "ODA/CCA schemes", GAM.acct_cls_flg As
" closed
status", GAM.frez_code, GAM.frez_reason_code As " freeze status",
GAM.acct_opn_date, dctd_acli.tran_amt, GAM.clr_bal_amt, (Select
Sum(dctd_acli.tran_amt) From TBAADM.dctd_acli
Where TBAADM.dctd_acli.PART_TRAN_TYPE = 'D') As "Total Dr Amt",
(Select Sum(dctd_acli.tran_amt) From TBAADM.dctd_acli
Where TBAADM.dctd_acli.PART_TRAN_TYPE = 'C') As "Total Cr Am",
(Select Count(dctd_acli.tran_id)
From TBAADM.dctd_acli) As "Tran Cnt", (Select Sum(dctd_acli.tran_amt)
From TBAADM.dctd_acli
Where TBAADM.dctd_acli.PART_TRAN_TYPE = 'C') As "Total Cash Deposit",
(Select Sum(dctd_acli.tran_amt) From TBAADM.dctd_acli
Where TBAADM.dctd_acli.PART_TRAN_TYPE = 'L') As "Total Clearing ",
(Select Sum(dctd_acli.tran_amt) From TBAADM.dctd_acli
Where TBAADM.dctd_acli.PART_TRAN_TYPE Not In ('C', 'L')) As "Others"
From TBAADM.SST, TBAADM.SOL, TBAADM.GAM, TBAADM.GAC, TBAADM.DCTD_ACLI,
TBAADM.EAB, TBAADM.CAM, TBAADM.SMT
Where GAM.acid = dctd_acli.acid And GAM.acid = SMT.acid And GAM.acid = CAM.acid
And GAM.acid = GAC.acid And GAM.bank_id = dctd_acli.bank_id And
GAM.bank_id = SMT.bank_id And GAM.bank_id = CAM.bank_id And
GAM.bank_id = GAC.bank_id And GAM.del_flg = 'N' And GAM.entity_cre_flg = 'Y'
And SST.del_flg = 'N' And SST.entity_cre_flg = 'Y' And EAB.tran_date_bal >
50000
Group By SST.sol_id, SST.set_id, GAM.sol_id, SOL.sol_desc, GAM.foracid,
GAM.acct_name, GAM.cif_id, GAC.free_text_1, SMT.acct_status, CAM.acct_status,
GAM.acct_cls_flg, GAM.frez_code, GAM.frez_reason_code, GAM.acct_opn_date,
dctd_acli.tran_amt, GAM.clr_bal_amt

Order By GAM.sol_id, EAB.eod_date

please help me in same.........

thanks in advance
Vipin jha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-03-17 : 08:31:06

Your query is fundamentally wrong. Here is your query rewritten in ANSI way and I performance tuned your aggregations.

SELECT		SST.sol_id,
		SST.set_id,
		GAM.sol_id,
		SOL.sol_desc,
		GAM.foracid,
		GAM.acct_name,
		GAM.cif_id,
		GAC.free_text_1,
		SMT.acct_status As [SBA/CAA schemes],
		CAM.acct_status As [ODA/CCA schemes],
		GAM.acct_cls_flg As [closed status],
		GAM.frez_code,
		GAM.frez_reason_code As [freeze status],
		GAM.acct_opn_date,
		dctd_acli.tran_amt,
		GAM.clr_bal_amt,
		SUM(CASE WHEN TBAADM.dctd_acli.PART_TRAN_TYPE = 'D' THEN dctd_acli.tran_amt ELSE 0 END) As [Total Dr Amt],
		SUM(CASE WHEN TBAADM.dctd_acli.PART_TRAN_TYPE = 'C' THEN dctd_acli.tran_amt ELSE 0 END) As [Total Cr Am],
		SUM(CASE WHEN dctd_acli.tran_id IS NULL THEN 0 ELSE 1 END) AS [Tran Cnt],
		SUM(CASE WHEN TBAADM.dctd_acli.PART_TRAN_TYPE = 'C' THEN dctd_acli.tran_amt ELSE 0 END) As [Total Cash Deposit],
		SUM(CASE WHEN TBAADM.dctd_acli.PART_TRAN_TYPE = 'L' THEN dctd_acli.tran_amt ELSE 0 END) As [Total Clearing],
		SUM(CASE WHEN TBAADM.dctd_acli.PART_TRAN_TYPE IN ('C', 'L') THEN 0 ELSE dctd_acli.tran_amt END) As Others
FROM		TbaAdm.GAM
INNER JOIN	TbaAdm.SMT ON SMT.acid = GAM.acid
			AND SMT.bank_id = GAM.bank_id
INNER JOIN	TbaAdm.SST ON SST.del_flg = 'N'				-- Missing reference to outer table
			And SST.entity_cre_flg = 'Y'
INNER JOIN	TbaAdm.SOL						-- Missing reference to outer table
INNER JOIN	TbaAdm.GAC ON GAC.acid = GAM.acid
			AND GAC.bank_id = GAM.bank_id
INNER JOIN	TbaAdm.DCTD_ACLI ON dctd_acli.acid = GAM.acid
			AND dctd_acli.bank_id = GAM.bank_id
INNER JOIN	TbaAdm.EAB ON EAB.tran_date_bal > 50000			-- Missing reference to outer table
INNER JOIN	TbaAdm.CAM ON CAM.acid = GAM.acid
			AND CAM.bank_id = GAM.bank_id
WHERE		GAM.del_flg = 'N'
		AND GAM.entity_cre_flg = 'Y'
GROUP BY	SST.sol_id,
		SST.set_id,
		GAM.sol_id,
		SOL.sol_desc,
		GAM.foracid,
		GAM.acct_name,
		GAM.cif_id,
		GAC.free_text_1,
		SMT.acct_status,
		CAM.acct_status,
		GAM.acct_cls_flg,
		GAM.frez_code,
		GAM.frez_reason_code,
		GAM.acct_opn_date,
		dctd_acli.tran_amt,
		GAM.clr_bal_amt
ORDER BY	GAM.sol_id,
		EAB.eod_date

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources