How do I concatenate strings from a column into a single row in DB2

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mark83anthony@gmail.com
    #1

    How do I concatenate strings from a column into a single row in DB2

    How do I concatenate strings from a column into a single row?

    Whats the logic to create the function in DB2.

    Given is below.

    Color
    ------
    red
    orange
    blue
    green

    And return a resultset like this:

    Colors
    -------------------------
    red,orange,blue ,green
    Tags: None
    • mark83anthony@gmail.com
      #2
      Re: How do I concatenate strings from a column into a single row inDB2

      On Nov 19, 2:28 pm, mark83anth...@g mail.com wrote:
      How do I concatenate strings from a column into a single row?
      >
      Whats the logic to create the function in DB2.
      >
      Given is below.
      >
      Color
      ------
      red
      orange
      blue
      green
      >
      And return a resultset like this:
      >
      Colors
      -------------------------
      red,orange,blue ,green
      I will make it clear.

      The table name is Colors
      CREATE TABLE Colors
      (
      Color VARCHAR(32)
      )

      Values in the Table are
      Color
      ------
      red
      orange
      blue
      green


      My output should be when I do a Select * from Tabname, shd be

      red,orange,blue ,green


      Waiting for your reply

      Thanks in Advancd

        Comment

        • jefftyzzer
          #3
          Re: How do I concatenate strings from a column into a single row inDB2

          On Nov 19, 12:49 pm, mark83anth...@g mail.com wrote:
          On Nov 19, 2:28 pm, mark83anth...@g mail.com wrote:
          >
          >
          >
          How do I concatenate strings from a column into a single row?
          >
          Whats the logic to create the function in DB2.
          >
          Given is below.
          >
          Color
          ------
          red
          orange
          blue
          green
          >
          And return a resultset like this:
          >
          Colors
          -------------------------
          red,orange,blue ,green
          >
          I will make it clear.
          >
          The table name is Colors
          CREATE TABLE Colors
          (
              Color VARCHAR(32)
          )
          >
          Values in the Table are
          Color
          ------
          red
          orange
          blue
          green
          >
          My output should be when I do a Select * from Tabname, shd be
          >
          red,orange,blue ,green
          >
          Waiting for your reply
          >
          Thanks in Advancd
          Mark:

          Have a look at a posting to this newsgroup titled "concatenat ing
          historical records" from October '07. In it, I listed two ways I know
          of/have learned to do this.

          --Jeff

            Comment

            • mark83anthony@gmail.com
              #4
              Re: How do I concatenate strings from a column into a single row inDB2

              On Nov 19, 3:53 pm, jefftyzzer <jefftyz...@sbc global.netwrote :
              On Nov 19, 12:49 pm, mark83anth...@g mail.com wrote:
              >
              >
              >
              >
              >
              On Nov 19, 2:28 pm, mark83anth...@g mail.com wrote:
              >
              How do I concatenate strings from a column into a single row?
              >
              Whats the logic to create the function in DB2.
              >
              Given is below.
              >
              Color
              ------
              red
              orange
              blue
              green
              >
              And return a resultset like this:
              >
              Colors
              -------------------------
              red,orange,blue ,green
              >
              I will make it clear.
              >
              The table name is Colors
              CREATE TABLE Colors
              (
                  Color VARCHAR(32)
              )
              >
              Values in the Table are
              Color
              ------
              red
              orange
              blue
              green
              >
              My output should be when I do a Select * from Tabname, shd be
              >
              red,orange,blue ,green
              >
              Waiting for your reply
              >
              Thanks in Advancd
              >
              Mark:
              >
              Have a look at a posting to this newsgroup titled "concatenat ing
              historical records" from October '07. In it, I listed two ways I know
              of/have learned to do this.
              >
              --Jeff- Hide quoted text -
              >
              - Show quoted text -
              Hi,

              I tried doing this

              SELECT replace(replace (xml2clob(xmlag g(xmlelement(NA ME a,
              Color))),'<A>', ''),'</A>',' ')
              FROM Colors;

              It works fine with my DB2 9.5 client on my machine but does not work
              with Db2 Z OS 9.1.

              It gives me the following error message

              SQL0171N The data type, length or value of argument "1" of routine
              "REPLACE"
              is incorrect. SQLSTATE=42815

              SQL0171N The data type, length or value of argument "1" of routine
              "REPLACE" is incorrect.

              Explanation:

              The data type, length or value of argument "<n>" of routine "<name>"
              is
              incorrect.

              The statement cannot be processed.

              User response:

              Ensure the arguments of the routine conform to the rules of the
              routine.

              sqlcode: -171

              sqlstate: 42815

                Comment

                • jefftyzzer
                  #5
                  Re: How do I concatenate strings from a column into a single row inDB2

                  On Nov 19, 2:22 pm, mark83anth...@g mail.com wrote:
                  On Nov 19, 3:53 pm, jefftyzzer <jefftyz...@sbc global.netwrote :
                  >
                  >
                  >
                  On Nov 19, 12:49 pm, mark83anth...@g mail.com wrote:
                  >
                  On Nov 19, 2:28 pm, mark83anth...@g mail.com wrote:
                  >
                  How do I concatenate strings from a column into a single row?
                  >
                  Whats the logic to create the function in DB2.
                  >
                  Given is below.
                  >
                  Color
                  ------
                  red
                  orange
                  blue
                  green
                  >
                  And return a resultset like this:
                  >
                  Colors
                  -------------------------
                  red,orange,blue ,green
                  >
                  I will make it clear.
                  >
                  The table name is Colors
                  CREATE TABLE Colors
                  (
                      Color VARCHAR(32)
                  )
                  >
                  Values in the Table are
                  Color
                  ------
                  red
                  orange
                  blue
                  green
                  >
                  My output should be when I do a Select * from Tabname, shd be
                  >
                  red,orange,blue ,green
                  >
                  Waiting for your reply
                  >
                  Thanks in Advancd
                  >
                  Mark:
                  >
                  Have a look at a posting to this newsgroup titled "concatenat ing
                  historical records" from October '07. In it, I listed two ways I know
                  of/have learned to do this.
                  >
                  --Jeff- Hide quoted text -
                  >
                  - Show quoted text -
                  >
                  Hi,
                  >
                  I tried doing this
                  >
                  SELECT replace(replace (xml2clob(xmlag g(xmlelement(NA ME a,
                  Color))),'<A>', ''),'</A>',' ')
                  FROM Colors;
                  >
                  It works fine with my DB2 9.5 client on my machine but does not work
                  with Db2 Z OS 9.1.
                  >
                  It gives me the following error message
                  >
                  SQL0171N  The data type, length or value of argument "1" of routine
                  "REPLACE"
                  is incorrect.  SQLSTATE=42815
                  >
                  SQL0171N  The data type, length or value of argument "1" of routine
                  "REPLACE" is incorrect.
                  >
                  Explanation:
                  >
                  The data type, length or value of argument "<n>" of routine "<name>"
                  is
                  incorrect.
                  >
                  The statement cannot be processed.
                  >
                  User response:
                  >
                  Ensure the arguments of the routine conform to the rules of the
                  routine.
                  >
                   sqlcode: -171
                  >
                   sqlstate: 42815
                  Well, notwithstanding a simple fix for the problem you're having with
                  the XML function, assuming that DB2 9.1 for Z/OS supports recursive
                  common table expressions (CTEs), you could try the other technique
                  demonstrated in the earlier posting I mentioned.

                  --Jeff

                    Comment

                    • Tonkuma
                      #6
                      Re: How do I concatenate strings from a column into a single row inDB2

                      DB2 for z/OS doesn't support casting XML data type to another data
                      type.
                      (See Table 13. Supported casts between built-in data types
                      on Page 80 of "DB2 Version 9.1 for z/OS SQL Reference".)
                      Also, DB2 for z/OS doesn't support XML2CLOB function.

                        Comment

                        • Tonkuma
                          #7
                          Re: How do I concatenate strings from a column into a single row inDB2

                          I have a Table called Colors both in Db2 9.5 for LUW and DB2 9.1 on Z/OS
                          SELECT replace(replace (xmlagg(xmlelem ent(NAME a,NAME)),'<A>', ' ' ),'</A>',' ') FROM TECPG01.TEST111 1
                          >
                          It works fine with db2 on LUW but generates an error for Db2 on z/OS
                          saying that argument 1 of the routine REPLACE is wrong.
                          >
                          It was neccesary to add an XML2CLOB function on DB2 9.1 for LUW, like
                          this:
                          SELECT replace(replace (XML2CLOB(xmlag g(xmlelement(NA ME a,
                          color))),'<A>', ''), '</A>', ' ') FROM Colors

                          I got error message SQL0440N for
                          SELECT replace(replace (xmlagg(xmlelem ent(NAME a, color)),'<A>', ''),
                          '</A>', ' ') FROM Colors

                          SQL0440N No authorized routine named "REPLACE" of type "FUNCTION"
                          having compatible arguments was found.

                            Comment

                            Previous template Next
                            Working...