7 Replies Latest reply on Dec 9, 2010 9:57 PM by jalen

    How to retrieve many rows using TEXTTABLE

    jalen

      We want to use a "texttable" to retrieve many rows:

       

      SELECT
        *
      FROM
        TEXTTABLE('row1_col1,row1_col2,row2_col1,row2_col2' COLUMNS rol1 string, rol2 string) AS test

       

      But it can only retrieve one row: ['row1_col1', 'row1_col2'] instead of ['row1_col1', 'row1_col2'], ['row2_col1', 'row2_col2'].

       

      We try to add '\n' in the select clause, but it doesn't work.

        • 1. Re: How to retrieve many rows using TEXTTABLE
          shawkins

          Jalen,

           

          You do need to add a new line in the literal value.  However SQL does not support the same escape characters as Java, so \n will only work if the text string is in Java.  Otherwise it will need a newline directly:

           

          SELECT * FROM TEXTTABLE('row1_col1,row1_col2

          row2_col1,row2_col2' COLUMNS rol1 string, rol2 string) AS test

           

          It could be an enhancement request to support a function or an escape notation (like Postgres) for entering strings with Java/C style escaping using.

           

          Steve

          • 2. Re: How to retrieve many rows using TEXTTABLE
            jalen

            Steven,

             

            We tested the procedures you write before in Designer, but it didn't work and only returned one row.

             

            SELECT
              *
            FROM
              TEXTTABLE('CIS_1,CIS_ARTESIA,CustomerInformationSystemArtesia
            CIS_2,CIS_DR,CustomerInformationSystemLocallyDR' COLUMNS CIS_ID string, CIS_NAME string, CIS_DESCRIPTION string) AS CI

             

            I noticed that in VDB for the model file *.xmi:

            <helper xsi:type="transformation:SqlTransformation" xmi:uuid="mmuuid:df4cd403-bf15-4bc3-a908-3b2753173abf">
            <nested xsi:type="transformation:SqlTransformation" xmi:uuid="mmuuid:97debc2b-1c12-4c4d-832c-c531148237ee" selectSql="SELECT * FROM TEXTTABLE('CIS_1,CIS_ARTESIA,CustomerInformationSystemArtesia CIS_2,CIS_DR,CustomerInformationSystemLocallyDR' COLUMNS CIS_ID string, CIS_NAME string, CIS_DESCRIPTION string) AS CI"/>

            </helper>

            The new line has been replaced with a space.

             

            • 3. Re: How to retrieve many rows using TEXTTABLE
              shawkins

              Hi Jalen,

               

              If the new line is being replaced by a space that should be opened as an issue against Designer.  If you issued that query with a newline directly against Teiid you should get multiple rows.

               

              I'll go ahead and log an enhancement for Teiid to support an escaping function so that you'll be able to enter text with \n \t, etc.

               

              Steve

              1 of 1 people found this helpful
              • 4. Re: How to retrieve many rows using TEXTTABLE
                jalen

                Thank you, Steven.

                • 5. Re: How to retrieve many rows using TEXTTABLE
                  rareddy

                  Logged and fixed under https://jira.jboss.org/browse/TEIID-1385  in Teiid verion 7.3

                  • 6. Re: How to retrieve many rows using TEXTTABLE
                    shawkins

                    The teiid issue adds an unescape function.

                     

                    https://issues.jboss.org/browse/TEIIDDES-735 covers the designer issue of not preserving the newlines.  I also added a workaround example using the char function along the lines of:

                     

                    SELECT
                      *
                    FROM
                      TEXTTABLE('CIS_1,CIS_ARTESIA,CustomerInformationSystemArtesia' || CHAR(10) || 'CIS_2,CIS_DR,CustomerInformationSystemLocallyDR' COLUMNS CIS_ID string, CIS_NAME string, CIS_DESCRIPTION string) AS CI

                    • 7. Re: How to retrieve many rows using TEXTTABLE
                      jalen

                      Thank you, Steven.

                       

                      Your answer helps us a lot!