sql query cntrlr1225 - 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

Transact-SQL (2005)

sql query cntrlr1225

Author

Topic

vipin_jha123
Starting Member

31 Posts

Posted - 2011-04-21 : 02:28:41

I am trying to execute below query

Select gam.sol_id, sol.sol_desc, gam.cif_id, gam.foracid, gam.acct_name,
(Select Sum(dctd_acli.tran_amt) From tbaadm.dctd_acli
Where tbaadm.dctd_acli.TRAN_TYPE = 'C') "Total Cash Txn", (Select
Sum(dctd_acli.tran_amt) From tbaadm.dctd_acli
Where tbaadm.dctd_acli.TRAN_TYPE = 'C' And tbaadm.dctd_acli.PART_TRAN_TYPE =
'C') "Deposit", (Select Sum(dctd_acli.tran_amt) From tbaadm.dctd_acli
Where tbaadm.dctd_acli.TRAN_TYPE = 'C' And tbaadm.dctd_acli.PART_TRAN_TYPE =
'D') "Withdrawal", SERT.salary_credit_amt
From tbaadm.gam Inner Join
tbaadm.sol On gam.sol_id = sol.sol_id And GAM.bank_id = SOL.bank_id Inner Join
tbaadm.sst On SST.sol_id = GAM.sol_id And SST.bank_id = GAM.bank_id Inner Join
tbaadm.dctd_acli On GAM.acid = dctd_acli.acid And
GAM.bank_id = dctd_acli.bank_id Inner Join
tbaadm.SeRT On SeRT.acid = GAM.acid And GAM.bank_id = SeRT.bank_id And
GAM.del_flg = 'N' And GAM.entity_cre_flg = 'Y' And SST.del_flg = 'N' And
SST.entity_cre_flg = 'Y'

when i am executing bold part then i am getting data
but when i am executing whole query i m not gettinmg dfata

please help me in that cases

tyhanks in advance

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-21 : 03:19:20

Without data, its hard to find out real problem. Still as per your description, problem will be in your join statements.

--------------------------
http://connectsql.blogspot.com/

vipin_jha123
Starting Member

31 Posts

Posted - 2011-04-21 : 05:19:46

I worked on the query..
ERROR:-"ORA-00904: "TABLE3"."WITHDRAWAL": invalid identifier".

Select gam.sol_id, sol.sol_desc, gam.cif_id, gam.foracid,
gam.acct_name,sum(table1.Total_Cash_Txn) as "Total_Cash_Txn" ,
sum(table2.Deposit) as "Deposit",
sum(table3.Withdrawal) as "Withdrawal",SERT.salary_credit_amt

From tbaadm.gam Inner Join tbaadm.sert on
SERT.acid = GAM.acid and GAM.bank_id = SERT.bank_id inner join

tbaadm.sol On gam.sol_id = sol.sol_id And GAM.bank_id = SOL.bank_id Inner Join
tbaadm.sst On SST.sol_id = GAM.sol_id And SST.bank_id = GAM.bank_id Inner Join
tbaadm.dctd_acli On GAM.acid = dctd_acli.acid And
GAM.bank_id = dctd_acli.bank_id Inner Join

(select dctd_acli.acid ,sum(tran_amt) as "Total_Cash_Txn" from tbaadm.dctd_acli
where dctd_acli.tran_type='C' group by dctd_acli.acid)table1 on gam.acid=table1.acid inner join

(select dctd_acli.acid ,sum(tran_amt) as "Deposite" from tbaadm.dctd_acli
where dctd_acli.tran_type='C' and dctd_acli.PART_TRAN_TYPE ='C' group by dctd_acli.acid)
table2 on gam.acid=table2.acid inner join

(select dctd_acli.acid ,sum(tran_amt) as "Withdrawal" from tbaadm.dctd_acli
where dctd_acli.tran_type='C' and tbaadm.dctd_acli.PART_TRAN_TYPE ='D' group by dctd_acli.acid)
table3 on gam.acid=table3.acid

where GAM.del_flg = 'N' And GAM.entity_cre_flg = 'Y' And SST.del_flg = 'N' And
SST.entity_cre_flg = 'Y'
group by gam.sol_id, sol.sol_desc, gam.cif_id, gam.foracid, gam.acct_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources