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.
| Author |
Topic |
|
marileng
Starting Member
28 Posts |
 Posted - 2002-03-20 : 00:18:47
|
| Here is my code :I want to get the result of may_balance - dr_cr and put in a column name balance Anybody help me on this? select derived2.seg3_code, may_balance=abs(sum(derived2.home_current_balance)), dr_cr =isnull((select dr.balance from (select coalesce(sum(a.balance),0,0) as balance from gltrxdet a, gltrx b where (b.date_applied>(730758 - 1) and b.date_applied< (730758 + 1)) and a.journal_ctrl_num=b.journal_ctrl_num group by a.seg3_code)dr),0) , balance= may_balance - dr_cr, from (select a.balance_date as balance_date,a.account_code as account_code, a.seg1_code as seg1_code,a.seg3_code as seg3_code,a.home_current_balance as home_current_balance,b.description as description from glbal a,glseg3 b ,(select max(balance_date) as gbalance_date ,account_code as glaccnt from glbal where seg1_code <> 100) group by account_code) may where a.seg3_code=b.seg_code and a.balance_date=gbalance_date and a.account_code=glaccnt) derived2group by derived2.seg3_code |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-03-20 : 07:22:33
|
| may_balance and dr-cr balances are alias names for 2 columns in your result set....you can't use the alias names as components to be worked upon in your query...they really are just display names....change your query tobe may_balance = abs(sum(xxxx, etc))), dr_cr=isnull((select uuuu, etc)), balance = abs(sum(xxxx, etc))) - isnull((select uuuu, etc))and this should work....xxxx,uuuu above should be replace by your calcs....painful in terms of having the code repeated, but not sure if it is painful in terms of performance. |
 |
|
|
marileng
Starting Member
28 Posts |
Posted - 2002-03-20 : 21:19:36
|
| I thought there's a way not to repeat my calculations.Thanks, anyway. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-21 : 00:03:39
|
| I think you can put your calcs in as a sub-query - join on the seg3_code field and use only the calc'ed columns in your select clause.I'd be willing to try but1. I think you're missing some brackets?2. can't see what derived2 is? (or is that you're calcs?)--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
help