XML Jobs outputting decimals; when is it a 'comma' and when a 'dot'?
Hint Ref: 021310020019
Hint Date: 02/10/2013
Hint Details:
ISSUE:
A customer, who had their regional settings set to use a comma as the decimal separator within Space Manager, reported that they had a XML JOB with a single query with two separate 'SUM' sub-selects in it. However when outputted to the same text file by the job, one figure had a full stop as the decimal separator and the other a comma.
SOLUTION:
Investigation showed a very useful distinction between the two sub-queries; one was converted into a string within the SQL Query and the other was not.
SO:
When writing out, say a XML data file with a command like:
<Write xmlsafe="YES" xmlindent="NO">{NewTotal}</Write>
To get a comma as the decimal, just CAST the value like this:
cast(isnull((select sum(amount) from #TEMP),0) as decimal(10,2)) as NewTotal,
To get a full stop as the decimal, just STRING the result like this:
string(cast(isnull((select sum(amount) from #TEMP),0) as decimal(10,2))) as NewTotal,
The same applies if it is just a numerical data field. Say amount = 10.34:
cast(amount as decimal(12,2)) as newamount
would give 10,34
string(cast(amount as decimal(12,2))) as newamount,
would give 10.34
NOTE: Please note that if the number of decimal places is important, even if the figure has insufficient, then you must always use the STRING version of the above. Otherwise 10,20 will appear as 10,2. If you still need a comma as the decimal separator and a full stop is NOT used by the regional settings as the 1000's separator of the PC/Session, the following should do the trick:
replace(string(cast(amount as decimal(12,2))),'.',',') as newamount