| Author |
 Topic  |
|
|
cirugio
Yak Posting Veteran
88 Posts |
 Posted - 09/20/2010 : 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 record
to 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 b
LEFT OUTER JOIN ACCOUNT acct
ON b.account = a.ACCOUNT
group by EOP.account_num |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3448 Posts |
Posted - 09/20/2010 : 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 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
cirugio
Yak Posting Veteran
88 Posts |
Posted - 09/20/2010 : 12:52:03
|
| Unfortunately, it can't be redesigned. This is how the data is coming over from the vendor. |
|
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
Posted - 09/20/2010 : 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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
|
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 09/20/2010 : 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?
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
|
|
|
| |
Topic |
|
summing a NCHARVAR value
