| Author |
 Topic  |
|
|
pipay
Starting Member
Philippines
5 Posts |
 Posted - 02/28/2013 : 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
India
47023 Posts |
Posted - 02/28/2013 : 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/
|
 |
|
|
pipay
Starting Member
Philippines
5 Posts |
Posted - 02/28/2013 : 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/28/2013 : 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/
|
|
|
|
pipay
Starting Member
Philippines
5 Posts |
Posted - 02/28/2013 : 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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 02/28/2013 : 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/
|
|
|
|
pipay
Starting Member
Philippines
5 Posts |
Posted - 03/01/2013 : 03:49:56
|
| oh no?? i don't know what to do anymore. Please advise |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 03/01/2013 : 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/
|
|
|
|
pipay
Starting Member
Philippines
5 Posts |
Posted - 03/01/2013 : 04:41:34
|
thanks.. ill get back once i figured it out
|
|
|
| |
Topic |
|
|
|
How to put percent symbol (%) in a query???
