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 name survey name npts depth azimuth inclination Well-01 survey_01 6 10,20,30,40,50,60 0,8.05,8.05,10.73,5.80,10.46 0.00,0.96,1.10,1.20,1.47,1.56
And this is what I want to achieve
well name survey name npts depth azimuth inclination Well-01 survey_01 6 10 0 0 Well-01 survey_01 6 20 8.05 0.96 Well-01 survey_01 6 30 8.05 1.10 Well-01 survey_01 6 40 10.73 1.20 Well-01 survey_01 6 50 5.80 1.47 Well-01 survey_01 6 60 10.46 1.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?
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.
Thanks Ramesh. After updating teiid, I am able to bring the required result. Thanks for you support as always.