?
Solved

Ms SQL Query correction need

Posted on 2012-09-06
5
Medium Priority
?
509 Views
Last Modified: 2012-09-07
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
0
Comment
Question by:JoeBlack1980
5 Comments
 
LVL 7

Assisted Solution

by:aplusexpert
aplusexpert earned 400 total points
ID: 38371997
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
 

Author Comment

by:JoeBlack1980
ID: 38372058
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 38372209
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
 
LVL 18

Accepted Solution

by:
vasto earned 1000 total points
ID: 38372891
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
 

Author Closing Comment

by:JoeBlack1980
ID: 38376095
thanks all for  the help
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

621 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