4 Replies Latest reply on Apr 4, 2018 7:49 AM by Abdul Ahad

    comma separated string to multiple rows

    Abdul Ahad Newbie

      Hi

       

      I have a VDB connection for oracle database. One of the source tables contains comma separated values which I want to split in individual rows.

       

      namevalue
      A
      0.0,413.0,421.0,431.0,441.0,451.0,461.0,471.0,481.0,491.0,501.0,506.0,511.0,521.0
      B
      671.0,681.0,691.0,701.0,711.0,721.0,731.0,741.0,751.0,761.0,771.0,781.0,791.0,801.0,811.0,821.0,846.0
      name
      value
      A0.0
      A413.0
      A421.0
      .........

       

      How to write a VDB view to my required result?

        • 1. Re: comma separated string to multiple rows
          Ramesh Reddy Master

          Use TextTable but define the new row delimiter as comma (,) in its use. Read about texttable here TEXTTABLE · Teiid Documentation

          • 2. Re: comma separated string to multiple rows
            Abdul Ahad Newbie

            Thanks Ramesh for helping me but I am stuck at one point may be you can guide me.

             

            This is how my data looks like in Oracle table

             

                 

            well namesurvey namenptsdepthazimuthinclination
            Well-01survey_01610,20,30,40,50,600,8.05,8.05,10.73,5.80,10.460.00,0.96,1.10,1.20,1.47,1.56

             

            And this is what I want to achieve

                                                                                              

            well namesurvey namenptsdepthazimuthinclination
            Well-01survey_0161000
            Well-01survey_016208.050.96
            Well-01survey_016308.051.10
            Well-01survey_0164010.731.20
            Well-01survey_016505.801.47
            Well-01survey_0166010.461.56

                                 

             

            I am able to use texttable on one csv value column as nested table but how can I build relationship between other two csv columns using texttables maintaining the data integrity?

            • 3. Re: comma separated string to multiple rows
              Ramesh Reddy Master

              A SQL like below *should* work

               

              SELECT MyOrclTbl.wellname, MyOrclTbl.surveyname, MyOrclTbl.npts, depthTable.depth, azimuthTable.azimuth,inclinationTable.inclination
              FROM 
              (select 'Well-01' as wellname, 
                 'survey_01' as surveyname, 
                 6 as npts, 
                 '10,20,30,40,50,60' depth, 
                 '0,8.05,8.05,10.73,5.80,10.46' azimuth, 
                 '0.00,0.96,1.10,1.20,1.47,1.56' as inclination
              ) as MyOrclTbl, 
              TEXTTABLE (MyOrclTbl.depth COLUMNS depth integer, id for ordinality ROW DELIMITER ',' DELIMITER '-') as depthTable
              LEFT JOIN TEXTTABLE (MyOrclTbl.azimuth COLUMNS azimuth double, id for ordinality ROW DELIMITER ',' DELIMITER '-') as azimuthTable ON depthTable.id = azimuthTable.id
              LEFT JOIN TEXTTABLE (MyOrclTbl.inclination COLUMNS inclination double, id for ordinality ROW DELIMITER ',' DELIMITER '-') as inclinationTable ON depthTable.id = inclinationTable.id

               

              Where you would replace the "MyOrclTbl" with your actual table reference. I tested with Teiid 10.2 this brings back results as you expected.

               

              HTH

               

              Ramesh..

              • 4. Re: comma separated string to multiple rows
                Abdul Ahad Newbie

                Thanks Ramesh. After updating teiid, I am able to bring the required result. Thanks for you support as always.