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.
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.589Output 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 % symbolie likecast(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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 1Error converting data type varchar to numeric. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 scriptSELECT 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 UNIONSELECT 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
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 MVPhttp://visakhm.blogspot.com/ |
|
|
pipay
Starting Member
5 Posts |
Posted - 2013-03-01 : 03:49:56
|
oh no?? i don't know what to do anymore. Please advise |
|
|
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 datause a separate query likeSELECT COUNT(*)FROM TableWHERE 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 MVPhttp://visakhm.blogspot.com/ |
|
|
pipay
Starting Member
5 Posts |
Posted - 2013-03-01 : 04:41:34
|
thanks.. ill get back once i figured it out |
|
|
|
|
|
|
|