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 |
|
cirugio
Yak Posting Veteran
90 Posts |
 Posted - 2010-09-20 : 10:56:33
|
| I am joining 2 files and need to display the balances by account across acct type and also create a column for each recordto store a field called affiliate. Therefore, I used the case statement to do this. Currently the relationship is 1 to many. I am just trying to get it to be 1 record per account. The issue I have is that when I try to SUM the field affiliate (nvarchar), I try to convert it to a smallint, but ran into a issue, when one of the values contains for example, the value '05XX'. I still need to display this. Is there any other way, outside of using the SUM which will do this for me? I can't add it to the select statement, because otherwise it need to also be added to the group by statement, which again will give me multiple records. Thoughts?SELECT b.account_num,--eop.AFFILIATE , SUM(CASE WHEN a.DESC = 'Assets' AND b.AFFILIATE <> 'N_A' THEN b.TOTAL_AMT ELSE 0 END) bal1_AF, SUM (CASE WHEN acct.DESC ='Assets' and b.AFFiLIATE = 'N_A' THEN b.TOTAL_AMT ELSE 0 END) bal1, SUM (case when b.affiliate = 'N_A' then 0 else CONVERT(smallint,b.affiliate) end) affiliate FROM balance bLEFT OUTER JOIN ACCOUNT acctON b.account = a.ACCOUNTgroup by EOP.account_num |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-09-20 : 11:01:19
|
| IT's not a good idea to store numbers in a varchar field.can you redesign at all?If not is there a *definite* pattern that this value can hold?Doing stuff like this runs the risk that at any time your query will fall over when an unexpected value is presented.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
cirugio
Yak Posting Veteran
90 Posts |
Posted - 2010-09-20 : 12:52:03
|
| Unfortunately, it can't be redesigned. This is how the data is coming over from the vendor. |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-09-20 : 14:05:48
|
Unfortunately, it can't be redesigned. This is how the data is coming over from the vendor. [/quote]How are you getting data into the tables? It would be nice to scrub the data first with an ETL tool of some kind. It would also be nice if the vendor did not give you garbage data; ask them about that.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-09-20 : 14:30:27
|
quote:
Originally posted by cirugio
Unfortunately, it can't be redesigned. This is how the data is coming over from the vendor.
Excuse me?What does that mean?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|
|
|
|
summing a NCHARVAR value