SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 summing a NCHARVAR value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cirugio
Yak Posting Veteran

90 Posts

Posted - 09/20/2010 :  10:56:33  Show Profile  Reply with Quote
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
3451 Posts

Posted - 09/20/2010 :  11:01:19  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
Go to Top of Page

cirugio
Yak Posting Veteran

90 Posts

Posted - 09/20/2010 :  12:52:03  Show Profile  Reply with Quote
Unfortunately, it can't be redesigned. This is how the data is coming over from the vendor.
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 09/20/2010 :  14:05:48  Show Profile  Visit jcelko's Homepage  Reply with Quote

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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/20/2010 :  14:30:27  Show Profile  Reply with Quote
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





Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000