Facing issue during calculation and Convert

Ok, If you are converting varchar to decimal then need to check all values must be convertible to decimal, you should check for null and "" values as well,

First of all

declare @credit_amount varchar(10)= '100'

select CASE WHEN isnull(@credit_amount,0) != ''  THEN CONVERT(varchar(10), CONVERT(decimal(7,2), isnull(@credit_amount,0)) * 0.0299) ELSE '0.00' END AS TexValue

Dear Jignesh, 

Thank you for your reply i tried as per you query but still i am getting error i tried to convert nvarchar to number still same.

 

    CASE WHEN credit_amount != '' THEN CONVERT(decimal(7,2), credit_amount * 0.0299)
         WHEN credit_amount = '' THEN CONVERT(decimal(7,2), credit_amount)  ELSE '0.00' END AS TexValue,

 

Error

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

Completion time: 2024-01-21T11:02:04.2879684+03:00

Hello Feroz,

Your CASE WHEN statement is wrong, you need to add a column to check the condition

CASE WHEN credit_amount != ''  THEN credit_amount * 0.0299 ELSE '0.00' END AS TexValue,

Example, please refer below query and change according to your requirement

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

It looks like you are trying to conditionally calculate values based on whether a condition is met in your SQL query. However, there are a couple of issues in your provided SQL snippet.

1. The condition '' = '' will always evaluate to true, so the ELSE part of your CASE statement will never be executed.

2. In your THEN and ELSE clauses, you are using string literals ('0.00') instead of numerical values. If you want to perform mathematical operations, you should use numeric values without quotes.

Here's a corrected version of your query:

CASE WHEN THEN credit_amount * 0.0299 ELSE 0.00 END AS TexValue,
CASE WHEN THEN credit_amount / 1.15 ELSE 0.00 END AS TXTVAT,

Please accept the answer if you find the same is useful!