Ms SQL Query correction need

Hi,

On My MSSQL 2008 server i am running the following part of query. ( i am not writing the whole query as it is unnecessary)

 (CASE WHEN Currency = 'USD' THEN PriceOfSale WHEN Currency = 'EUR' 
THEN PriceOfSale * EurVsUsdParite ELSE 0 END) AS USD 

Open in new window


I have 2 needs to be corrected

1 )  , the results come up as  ( random numbers are given )
0.1  or  5.7  , however i need it to display as 0,1 5,7  (comma instead of dot)

2) i need to add some code to display the data as
0,1 USD   or  5,7 USD , how can i do that.

Thanks in advance
JoeBlack1980Asked:
Who is Participating?
 
vastoCommented:
SELECT CASE WHEN Currency = 'USD'
                  THEN CONVERT(VARCHAR,cast(PriceOfSale  as money),1) + ' USD'
               WHEN Currency = 'EUR'
                  THEN CONVERT(VARCHAR,cast(PriceOfSale * EurVsUsdParite as money),1) + ' USD'
               ELSE 0 END as USD
FROM YourTable
0
 
aplusexpertCommented:
Try this query...

SELECT REPLACE(vw.USD, '.', ',') + ' ' + 'USD'
FROM
(
select CASE WHEN Currency = 'USD' 
			THEN PriceOfSale 
		   WHEN Currency = 'EUR' 
			THEN PriceOfSale * EurVsUsdParite 
		   ELSE 0 END as USD
		   from Expert_Query
		   ) as vw

Open in new window


Thanks.
0
 
JoeBlack1980Author Commented:
hi,

It works like as following however, do you suggest any speed problem with that use ?
 (SELECT    
REPLACE(( CASE WHEN Currency = 'USD' THEN PriceOfSale WHEN Currency = 'EUR' THEN PriceOfSale * EurVsUsdParite ELSE 0 END), '.', ',')
+ ' USD' AS vw) AS USD2

Also now it gives results like 2,100000 USD,
Is there any way to truncate the numbers to 2 digits after comma like 2,10
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to case and convert a bit:


SELECT REPLACE( CONVERT(decimal(20,2), vw.USD) , '.', ',') + ' ' + 'USD'
FROM
(
select CASE WHEN Currency = 'USD' 
			THEN PriceOfSale 
		   WHEN Currency = 'EUR' 
			THEN PriceOfSale * EurVsUsdParite 
		   ELSE 0 END as USD
		   from Expert_Query
		   ) as vw
                                            

Open in new window

0
 
JoeBlack1980Author Commented:
thanks all for  the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.