I'm using Teiid 9.3.7 with Wildfly 10 due to Teiid Designer version compatibility. I'm trying to build a view out of a flat file. There are two different types of entries in the file. Each type of entry has a key attribute that I need in order to join with other tables, but the position of the key attribute are different for each type of entries. The first number(4) will tell me which kind of entry is that row. I imported the data source and parse each row the same way, so I can catch the key attribute from both types of entries but as different columns. The transformation of the view looks like this
SELECT A.TYPE, A.column_1, A.ID, A.column_2, A.KEY_ATTRIB_1, A.column_3, A.KEY_ATTRIB_2, A.column_4 FROM (EXEC CorrSourceModel.getTextFiles('my_input.txt')) AS f, TEXTTABLE(f.file COLUMNS TYPE string WIDTH 4, column_1 string WIDTH 32, ID string WIDTH 9, column_2 string WIDTH 2466, KEY_ATTRIB_1 string WIDTH 25, column_3 string WIDTH 1445, KEY_ATTRIB_2 string WIDTH 13, column_4 string WIDTH 2854) AS A
So in the view, I can see both key attributes in their column fine. The final virtual view schema I need to build is the original line of the row, the key attribute of that row, and additional attributes I join from another data source with that key attribute. So in order to do that, I need to test the first attribute (4-digit number) so I know witch column is my key attribute for the row. It's like I need a IF... ELSE statement.
If there a way to accomplish that? Thanks.
See SELECTOR in this document [1] that should let you pick the lines. You can have two TEXTTABLE constructs one for each SELECTOR type and then join them if needed to get a combined result.
[1] http://teiid.github.io/teiid-documents/master/content/reference/r_texttable.html