CODEWITHSUNDEEP
sqldb2
Thomas
Thomas

Reputation: 63

Trying to return values with two decimal places and comma

I've tried using a TRIM(VARCHAR_FORMAT( value ), '9,999,990.99') but it fails out... Any easy way to covert a result into a more readable format? I am looking to retrieve two decimal places and use proper 0,000 separation for results

Thanks very much for reading this. The values in YELLOW I would like to format better...

enter image description here

WITH X AS
(
SELECT
1 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME, T.BILL_NUMBER CURRENT_FB, T.WEIGHT CURRENT_WEIGHT,
TRIM(VARCHAR_FORMAT((T.CHARGES + T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END), '9,999,990.99') as CURRENT_REVENUE,
NULL PREVIOUS_FB, NULL PREVIOUS_WEIGHT, NULL PREVIOUS_REVENUE  
FROM TLORDER T
INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
WHERE 1 = 1
AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
AND T.EXTRA_STOPS <> 'Child'
AND T.BILL_NUMBER NOT IN ('0', 'NA')
AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 90 days AND CURRENT TIMESTAMP  -60 days

  UNION ALL

SELECT
2 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME, NULL CURRENT_FB, NULL CURRENT_WEIGHT, NULL CURRENT_REVENUE, T.BILL_NUMBER PREVIOUS_FB, T.WEIGHT PREVIOUS_WEIGHT,
TRIM(VARCHAR_FORMAT((T.CHARGES + T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END), '9,999,990.99') AS PREVIOUS_REVENUE
FROM TLORDER T
INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
WHERE 1 = 1
AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
AND T.EXTRA_STOPS <> 'Child'
AND T.BILL_NUMBER NOT IN ('0', 'NA')
AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 60 days AND CURRENT TIMESTAMP
)

SELECT
1 SORT, COMPANY, CLIENT_ID, CLIENT_NAME, 
COUNT(DISTINCT CURRENT_FB) CURRENT_FB,
COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB, 
ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
SUM(CURRENT_WEIGHT) CURRENT_WEIGHT,
SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
TRIM(VARCHAR_FORMAT(SUM(CURRENT_REVENUE),'9,999,990.99')) as CURRENT_REVENUE,
TRIM(VARCHAR_FORMAT(SUM(PREVIOUS_REVENUE),'9,999,990.99')) as PREVIOUS_REVENUE, 
ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2) REVENUE_DIFF
FROM X
GROUP BY COMPANY, CLIENT_ID, CLIENT_NAME

  UNION ALL

SELECT
2 SORT, COMPANY, 'TOTAL' CLIENT_ID, NULL CLIENT_NAME,
COUNT(DISTINCT CURRENT_FB) CURRENT_FB,
COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB, 
ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
SUM(CURRENT_WEIGHT) CURRENT_WEIGHT,
SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
TRIM(VARCHAR_FORMAT(SUM(CURRENT_REVENUE),'9,999,990.99')) AS CURRENT_REVENUE,
TRIM(VARCHAR_FORMAT(SUM(PREVIOUS_REVENUE),'9,999,990.99')) AS PREVIOUS REVENUE,
ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2) REVENUE_DIFF
FROM X
GROUP BY COMPANY

Upvotes: 0

Views: 449

Answers (1)

The Impaler
The Impaler

Reputation: 48850

Your issue seems to be related to some syntax error in the query, not to the formatting, that works well. For example:

select varchar_format(2696067.32697366,'9,999,990.99') 
from sysibm.sysdummy1;

Result:

1
------------
2,696,067.33

See running example at db<>fiddle.

Upvotes: 1

Related Questions