Convert Currency Values into Words in ODBC

The sample SQL statement below demonstrates how to convert currency values in words.


 

SELECT MAX(txn.TRANID) [SO Number],

       SUM(Abs(txnlines.NET_AMOUNT)) [Total Amount],

       ( CASE

           WHEN SUM(Abs(txnlines.NET_AMOUNT)) = 0 THEN 'ZERO'

           ELSE To_char(To_date(To_char(Trunc(SUM(Abs(txnlines.NET_AMOUNT)), 0))

                        , 'J'),

                'JSP')

                 || ' '

                 || ( CASE

                        WHEN Length(To_char(Regexp_replace(

                                            SUM(Abs(txnlines.NET_AMOUNT)),

                                            '^[0-9]+\.',

                                            '')))

                             = 1 THEN To_char(Regexp_replace(

                                              SUM(Abs(txnlines.NET_AMOUNT)),

                                              '^[0-9]+\.', ''))

                                       || '0/100'

                        ELSE To_char(Regexp_replace(

                                     SUM(Abs(txnlines.NET_AMOUNT)),

                                     '^[0-9]+\.',

                                     ''))

                              || '/100'

                      END )

                 || ' '

                 || MAX(curr.NAME)

                 || '(s) Only'

         END ) [Amount in Words]

FROM   Administrator.CURRENCIES curr,

       Administrator.TRANSACTION_LINES txnlines

       INNER JOIN Administrator.TRANSACTIONS txn

         ON txn.TRANSACTION_ID = txnlines.TRANSACTION_ID

WHERE  txn.TRANSACTION_TYPE = 'Sales Order'

       AND txnlines.TAX_ITEM_ID IS NOT NULL

       AND curr.CURRENCY_ID = txn.CURRENCY_ID

GROUP  BY Administrator.TRANSACTIONS.TRANID;

 
 
The SQL above will generate this result: