Need Help with query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • lenygold via DBMonster.com
    #1

    Need Help with query

    One column table:

    ALL_SUM
    --------------------------------------------------------------------
    73237155+732402 40+73243230+732 49335

    73237155+732402 40+73246345

    73237155+732402 40+73246345+732 49335
    ............... ............... ............... ............... ........

    I need to sum every 2 digits prior '+' sign plus last 2 digits:

    For row 1: 55 + 40 + 30 + 35
    For row 2: 55 + 40 + 45
    For row 3: 55 + 40 + 45 + 35
    Any idea how to do this?
    This is realy urgent.
    Thank's in advance.

    --
    Message posted via DBMonster.com
    http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200804/1


    Tags: None
    • Lennart
      #2
      Re: Need Help with query

      On Apr 5, 3:28 am, "lenygold via DBMonster.com" <u41482@uwewrot e:
      One column table:
      >
      ALL_SUM
      --------------------------------------------------------------------
      73237155+732402 40+73243230+732 49335
      >
      73237155+732402 40+73246345
      >
      73237155+732402 40+73246345+732 49335
      ............... ............... ............... ............... .......
      >
      I need to sum every 2 digits prior '+' sign plus last 2 digits:
      >
      For row 1: 55 + 40 + 30 + 35
      For row 2: 55 + 40 + 45
      For row 3: 55 + 40 + 45 + 35
      Any idea how to do this?
      This is realy urgent.
      Thank's in advance.
      >
      See Knut's post for a function that will be of help here:

      http://tinyurl.com/3hp487.

      CREATE FUNCTION elements ( string varchar(100) )
      RETURNS TABLE ( ordinal INTEGER, index INTEGER )
      LANGUAGE SQL
      DETERMINISTIC
      NO EXTERNAL ACTION
      CONTAINS SQL
      RETURN
      WITH t(ordinal, index) AS
      ( VALUES ( 0, 0 )
      UNION ALL
      SELECT ordinal+1, COALESCE(NULLIF (
      LOCATE('+', string, index+1), 0), LENGTH(string)
      +1)
      FROM t
      -- to prevent a warning condition for infinite recursion
      WHERE ordinal < 500 AND
      LOCATE('+', string, index+1) <0 )
      SELECT ordinal, index
      FROM t

      You will have to modify it slightly to fit your problem, Given that
      the solution is easy:

      db2 "create table T (all_sum varchar(50) not null primary key)"
      db2 "insert into T values ('73237155+7324 0240+73243230+7 3249335')"
      db2 "insert into T values ('73237155+7324 0240+73246345') "

      db2 "select all_sum, sum(int(substr( all_sum, index-2 ,2))) from T,
      TABLE (elements( T.all_sum )) x where index 0 group by all_sum"

      ALL_SUM 2
      -------------------------------------------------- -----------
      73237155+732402 40+73243230+732 49335 125
      73237155+732402 40+73246345 95

      2 record(s) selected.

      Note that the sum is not correct since the last part of all_sum is not
      taken into concideration, but you get the idea.

      A word of caution, if there are duplicate all_sum (I added the primary
      key to ensure that there is not) you will get strange results

      HTH
      /Lennart

        Comment

        • Lennart
          #3
          Re: Need Help with query

          On Apr 5, 5:54 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
          On Apr 5, 3:28 am, "lenygold via DBMonster.com" <u41482@uwewrot e:
          >
          >
          >
          One column table:
          >
          ALL_SUM
          --------------------------------------------------------------------
          73237155+732402 40+73243230+732 49335
          >
          73237155+732402 40+73246345
          >
          73237155+732402 40+73246345+732 49335
          ............... ............... ............... ............... .......
          >
          I need to sum every 2 digits prior '+' sign plus last 2 digits:
          >
          For row 1: 55 + 40 + 30 + 35
          For row 2: 55 + 40 + 45
          For row 3: 55 + 40 + 45 + 35
          Any idea how to do this?
          This is realy urgent.
          Thank's in advance.
          >
          See Knut's post for a function that will be of help here:
          >
          http://tinyurl.com/3hp487.
          >
          CREATE FUNCTION elements ( string varchar(100) )
          RETURNS TABLE ( ordinal INTEGER, index INTEGER )
          LANGUAGE SQL
          DETERMINISTIC
          NO EXTERNAL ACTION
          CONTAINS SQL
          RETURN
          WITH t(ordinal, index) AS
          ( VALUES ( 0, 0 )
          UNION ALL
          SELECT ordinal+1, COALESCE(NULLIF (
          LOCATE('+', string, index+1), 0), LENGTH(string)
          +1)
          FROM t
          -- to prevent a warning condition for infinite recursion
          WHERE ordinal < 500 AND
          LOCATE('+', string, index+1) <0 )
          SELECT ordinal, index
          FROM t
          >
          You will have to modify it slightly to fit your problem, Given that
          the solution is easy:
          >
          db2 "create table T (all_sum varchar(50) not null primary key)"
          db2 "insert into T values ('73237155+7324 0240+73243230+7 3249335')"
          db2 "insert into T values ('73237155+7324 0240+73246345') "
          >
          db2 "select all_sum, sum(int(substr( all_sum, index-2 ,2))) from T,
          TABLE (elements( T.all_sum )) x where index 0 group by all_sum"
          >
          ALL_SUM 2
          -------------------------------------------------- -----------
          73237155+732402 40+73243230+732 49335 125
          73237155+732402 40+73246345 95
          >
          2 record(s) selected.
          >
          Note that the sum is not correct since the last part of all_sum is not
          taken into concideration, but you get the idea.
          >
          A word of caution, if there are duplicate all_sum (I added the primary
          key to ensure that there is not) you will get strange results
          >
          HTH
          /Lennart
          On second thought, you can use the function as is by adding a '+' at
          the end of all_sum as in:

          db2 "select all_sum, sum(int(substr( all_sum, index-2 ,2))) from T,
          TABLE (elements( rtrim(T.all_sum ) || '+')) x where ordinal 0 group
          by all_sum"

          ALL_SUM 2
          -------------------------------------------------- -----------
          73237155+732402 40+73243230+732 49335 160
          73237155+732402 40+73246345 140
          73237155+732402 40+73246345+732 49335 175

          /L

            Comment

            • lenygold via DBMonster.com
              #4
              Re: Need Help with query

              Thank You Lennart. It is working perfect.

              Lennart wrote:
              One column table:
              >>
              >[quoted text clipped - 65 lines]
              >HTH
              >/Lennart
              >
              >On second thought, you can use the function as is by adding a '+' at
              >the end of all_sum as in:
              >
              >db2 "select all_sum, sum(int(substr( all_sum, index-2 ,2))) from T,
              >TABLE (elements( rtrim(T.all_sum ) || '+')) x where ordinal 0 group
              >by all_sum"
              >
              >ALL_SUM 2
              >-------------------------------------------------- -----------
              >73237155+73240 240+73243230+73 249335 160
              >73237155+73240 240+73246345 140
              >73237155+73240 240+73246345+73 249335 175
              >
              >/L
              --
              Message posted via http://www.dbmonster.com

                Comment

                Previous template Next
                Working...