Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to put percent symbol (%) in a query???

Author  Topic 

pipay
Starting Member

5 Posts

Posted - 2013-02-28 : 06:01:00
Below is a line in my script along with its output. I want to achieve an output that i want below.. How will i do that? please help..

query:
case when (T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)<0 then(T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)*1 else (T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)*-1 end

results:

58.065
32.589

Output that i want:

58.07%
32.57%

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 06:13:30
if you want to do this in sql you need to convert to varchar and append % symbol

ie like

cast(cast(case when (T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)<0 then(T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)*1 else (T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)*-1 end as decimal(5,2)) as varchar(10)) + '%'

It would be much easier at front end application languages to do this by using format functions available

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pipay
Starting Member

5 Posts

Posted - 2013-02-28 : 09:56:34
Thanks for your reply.. give it a try but then below message appear. What will i do??

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 10:05:50
whats was actual datatype of fields? do they have any non numeric data too in them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pipay
Starting Member

5 Posts

Posted - 2013-02-28 : 20:48:59
NO. values are numeric and null only but i used nullif so i guess there are no more non numeric on it.. but i have union..

this is my whole script


SELECT T0.[DocDate], T0.[DocNum],
T0.[CardCode], T0.[CardName], T2.groupcode as 'Group Code', T3.Slpname as 'Sales Employee',
T1.[ItemCode], T1.[Dscription] as 'Description',
T1.[unitMsr] as 'UOM' ,T1.[Quantity],
T1.[PriceBefDi] as ' Sales Price',
T1.[LineTotal] as ' Sales Amount',
T1.[StockPrice] as ' Item Cost',
T1.[Quantity]*T1.[StockPrice] as 'Cost of Sales',
T1.[GrssProfit] as 'Gross Profit',
T1.[GrssProfit]/NULLIF(T1.[LineTotal],0)*100 as 'GP Rate ',
T0.[DiscPrcnt] as ' Discount %' ,
T0.[DiscSum], T0.[Comments],
T0.U_SInstruction
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
LEFT OUTER JOIN OCRD T2 ON T2.CardCode = T0.CardCode
LEFT OUTER JOIN OSLP T3 ON T2.SlpCode = T3.SlpCode

UNION

SELECT T2.[DocDate], T2.[DocNum],
T2.[CardCode], T2.[CardName], T4.Groupcode as 'Group Code', T5.Slpname as 'Sales Employee',
T3.[ItemCode], T3.[Dscription] as 'Description',
T3.[unitMsr] as 'UOM', T3.[Quantity]*-1,
T3.[PriceBefDi]*-1 as 'Sales Price',
T3.[LineTotal]*-1 as ' Sales Amount',
T3.[StockPrice]*-1 as ' Item Cost',
(T3.[Quantity]*T3.[StockPrice])*-1 as 'Gross Profit' ,
case when t3.grssprofit <0 then (t3.grssprofit*1) else (t3.grssprofit*-1) end,
case when (T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)<0 then(T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)*1 else (T3.[GrssProfit]/NULLIF(T3.[LineTotal],0)*100)*-1 end
as 'GP Rate ',
T2.[DiscPrcnt] as ' Discount %',
T2.[DiscSum], T2.[Comments],
T2.U_SInstruction
FROM ORIN T2 INNER JOIN RIN1 T3 ON T2.DocEntry = T3.DocEntry
LEFT OUTER JOIN OCRD T4 ON T4.CardCode = T2.CardCode
LEFT OUTER JOIN OSLP T5 ON T5.SlpCode = T4.SlpCode
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:10:47
NULLIF should not be a problem as casting NULL will return NULL only and wont throw any exception. I think reason being presence of some other Non numeric characters in one or more of the involved fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pipay
Starting Member

5 Posts

Posted - 2013-03-01 : 03:49:56
oh no?? i don't know what to do anymore. Please advise
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-01 : 03:53:44
as suggested check any fields which is not of numeric type and used in calculation to see if it contains non numeric data

use a separate query like

SELECT COUNT(*)
FROM Table
WHERE Column LIKE '%[^0-9]%'


Repeat it for each column involved in calculation and any one having count >0 has some non numeric data. then either delete them from table or add a WHERE condition to exclude them above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pipay
Starting Member

5 Posts

Posted - 2013-03-01 : 04:41:34
thanks.. ill get back once i figured it out

Go to Top of Page
   

- Advertisement -