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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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???
 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
52326 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
52326 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
52326 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
52326 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  
 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.04 seconds. Powered By: Snitz Forums 2000