CODEWITHSUNDEEP
sql-serversql-server-2008-r2crystal-reports
user3844217
user3844217

Reputation: 39

how to write a sql command to convert two sql columns and include a calculation in same query

Following is my code :

SELECT sum_date, sum_accname, sum_description,
CASE WHEN debit NOT LIKE '%[^.0-9]%'
     THEN CAST(debit as DECIMAL(9,2)) 
     ELSE NULL 
   END  AS debit,
CASE WHEN credit NOT LIKE '%[^.0-9]%'  
     THEN CAST(credit as DECIMAL(9,2)) 
     ELSE NULL 
   END  AS credit
FROM sum_balance

while viewing the report it shows an error : Error converting data type varchar to numeric. And i need sum of credit and debit column in the same query. Tried with the above code if i include only one column in the query for conversion its working bt adding another column in conversion it shows the error. I can't figure out the problem

Upvotes: 0

Views: 67

Answers (1)

rory.ap
rory.ap

Reputation: 35280

The problem is that your debit and credit columns are text and thus can contain anything. You're attempting to limit it to only numeric values with NOT LIKE '%[^.0-9]%' but that's not enough because you could have a value like 12.3.6.7 which cannot convert to a decimal.

There is no way in SQL Server that I'm aware of using LIKE to achieve what you're trying to achieve, because LIKE does not support the full range of regex operations -- in fact, it's quite limited. In my opinion, you're torturing the database design by trying to multi-purpose those fields. If you're looking to report on numeric data, then store them in numeric fields. That assumes, of course, you have control over the schema.

Upvotes: 1

Related Questions