SQL Union Rounding

[Former Member]

Forum,

We're having a slight issue with SAP rounding the results of one of the columns when a query is unionised.

If we run the SQL below in SQL Server the column Std Cost is shown to 4 decimal places. If the code is run from inside B1 the Std Cost in shown at two decimal places and rounded. Im trying to get the results to show as 4 decimal places. Weu2019ve tried casting/converting the column in all 3 select statements to to show as 4 decimal places with no joys.

If you break up the statement and run each of the select statements in turn from inside B1 the std cost is shown to 4 decimal places. When all 3 are unionised together the results jump back to been shown as 2 decimal places.

Do you know how we can get the results to show as 4 decimals when unionised?

Select t3.[ItemCode],

T3.[ItemName],

T0.[PlannedQty],

T0.[Warehouse] as 'Whs',

T1.QAlloc 'Allocated',

T0.[U_bedNo],'Prod' as 'Status',

T0.[duedate],

T0.[Cmpltqty] as 'Completed Qty',

T0.[PlannedQty] - T0.[CmpltQty] as 'Remainnig QTY',

T0.[U_contract],

T4.[Price] as 'Std Cost',

((T0.[PlannedQty] - T0.[CmpltQty]) * T4.[Price]) as 'Std Total'

FROM [dbo].[OWOR] T0

INNER JOIN OITM T3 ON T0.[ItemCode] = T3.[ItemCode]

INNER JOIN ITM1 T4 on T3.ItemCode = T4.ItemCode

LEFT OUTER Join ALP_ST.dbo.vwbinstock T1 ON T1.DocEntry = T0.DocEntry

WHERE T0.[Status] = 'R' AND T4.[PriceList] = 1

UNION ALL

Select BS.ItemCode 'Code',

OITM.ItemName 'ItemName',

BS.QActualFree 'Qty',

BD.WhsCode 'Whs',

BS.QAlloc 'Allocated',

BD.RackName 'Bin',

'Stock' as 'Status',

NULL,

NULL,

NULL,

NULL,

ITM1.Price,

((BS.QActualFree + BS.QAlloc) * ITM1.Price)

from ALP_ST.dbo.vwbinstock BS

inner join alp_st.dbo.vwBinFullDetails BD ON BS.BinIDX = BD.Code

inner join OITM ON BS.ItemCode = OITM.ItemCode

Inner join ITM1 on OITM.ItemCode = ITM1.ItemCode

Where BS.BinType = -1

And BS.ActualStock >= 1

And ITM1.PriceList = 1

UNION ALL

select BS.ItemCode,

OITM.ItemName,

BS.QActualfree,

BD.WhsCode,

BS.QAlloc,

BD2.FullDesc,'Stock TSD' as 'Status',

NULL,

NULL,

NULL,

NULL,

ITM1.Price,

NULL

from ALP_ST.dbo.vwbinstock BS

inner join alp_st.dbo.vwBinFullDetails BD ON BS.BinIDX = BD.Code

inner join alp_st.dbo.vwBinFullDetails BD2 ON BS.TempSetDown= BD2.Code

inner join OITM ON BS.ItemCode = OITM.ItemCode

inner join ITM1 on OITM.ItemCode = ITM1.ItemCode

where bintype = 22

AND BS.TempSetDown IS NOT NULL

AND BS.ActualStock >= 1

AND ITM1.PriceList = 1

[Former Member]

Resolved by changing the 'Decimals in Query' under Admin > System Intialisation > General Settings > Display.