SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to put percent symbol (%) in a query???
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pipay
Starting Member

Philippines
5 Posts

Posted - 02/28/2013 :  06:01:00  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/28/2013 :  06:13:30  Show Profile  Reply with Quote
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

Philippines
5 Posts

Posted - 02/28/2013 :  09:56:34  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/28/2013 :  10:05:50  Show Profile  Reply with Quote
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

Philippines
5 Posts

Posted - 02/28/2013 :  20:48:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 02/28/2013 :  23:10:47  Show Profile  Reply with Quote
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

Philippines
5 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/01/2013 :  03:53:44  Show Profile  Reply with Quote
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

Philippines
5 Posts

Posted - 03/01/2013 :  04:41:34  Show Profile  Reply with Quote
thanks.. ill get back once i figured it out

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000