-
1. Re: How to retrieve many rows using TEXTTABLE
shawkins Dec 8, 2010 10:24 AM (in response to jalen)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 Dec 8, 2010 10:46 AM (in response to shawkins)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 CII 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 Dec 8, 2010 11:10 AM (in response to jalen)1 of 1 people found this helpfulHi 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
-
4. Re: How to retrieve many rows using TEXTTABLE
jalen Dec 8, 2010 11:31 AM (in response to shawkins)Thank you, Steven.
-
5. Re: How to retrieve many rows using TEXTTABLE
rareddy Dec 8, 2010 4:36 PM (in response to jalen)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 Dec 9, 2010 11:04 AM (in response to rareddy)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 Dec 9, 2010 9:57 PM (in response to shawkins)Thank you, Steven.
Your answer helps us a lot!