Results 1 to 6 of 6

Thread: SQL help

  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378

    Unanswered: SQL help

    Hi,

    db2 v10 on z/OS


    Query:
    SELECT DATE_BUSINESS,COUNT(*) AS COUNT,
    SUM(DECIMAL(SUBSTR(LOG_DATA,21,9))) AS TOTALAMT
    FROM TABLE_NAME


    The output:

    +-----------------------------------------------------+------
    | DATE_BUSINESS | COUNT | TOTALAMT |
    +-----------------------------------------------------+------
    | 2017-05-01 | 195 | 54842369 |
    +-----------------------------------------------------+------

    The total amount displayed is 54842369. The last two digits are pennies.

    How can I display the amount output in following format?
    548,423.69


    Thank you.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    Some clarification. LOG_DATA is VARCHAR.

    SUM(DECIMAL(SUBSTR(LOG_DATA,21,9))) returns 54842369. (with decimal point at the end)

    What I need is to move the decimal point to the left so that the amount displayed is 548423.69

    Thanks

  3. #3
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Specify the decimal definition like:

    SUM(DECIMAL(SUBSTR(LOG_DATA,21,9),12,2))

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    Dave, here is what I get when using SUM(DECIMAL(SUBSTR(LOG_DATA,21,9),12,2))

    54842369.00


    What I actually need is 548423.69 (548423 dollars and 69 cents)

    Thanks

  5. #5
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    then how about something like:

    SUM(DECIMAL(SUBSTR(LOG_DATA,21,7) || '.' || SUBSTR(LOG_DATA,28,2) ,12,2))

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    It works. Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •