Solved

SQL - put $ in front of field

Posted on 2013-05-29
2
355 Views
Last Modified: 2013-05-29
I'm writing a case statement and I want either a $ to appear or % based on a value in a field.  But I get an error - "Error converting data type varchar to numeric.

How do I resolve?

SELECT Amount, 
CASE 
	WHEN id.DiscountType = 1 THEN 
		'$' + id.DiscountValue 
	ELSE 
		id.DiscountValue + '%' 
END AS DiscountValue2,
AppliedDate
From MyTable

Open in new window

0
Comment
Question by:CipherIS
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 39205178
SELECT Amount,
CASE
      WHEN id.DiscountType = 1 THEN
            '$' + cast(id.DiscountValue  as varchar )
      ELSE
            cast (id.DiscountValue as varchar)  + '%'
END AS DiscountValue2,
AppliedDate
From MyTable
0
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 250 total points
ID: 39205183
You can append a string to a numeric value. You'll have to convert DiscountValue, like so:
SELECT Amount, 
CASE 
	WHEN id.DiscountType = 1 THEN 
		'$' + CONVERT(varchar(20), id.DiscountValue)
	ELSE 
		CONVERT(varchar(20), id.DiscountValue) + '%' 
END AS DiscountValue2,
AppliedDate
From MyTable

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question