1 of 1 people found this helpful
> Can these patterns be made configurable?
No, this was done to match the default expectations of SQL, the java.sql code, and ensure a canonical form for pushdown support. There are parse/format functions that can be used though if more conversions are needed.
Thank you Ramesh and Steve.Here is the SQL query I am trying with -
SELECT A.id,A.date1 FROM (EXEC fixedwidthdate.getFiles('FixedWidthDate.txt')) AS F, TEXTTABLE(TO_CHARS(F.file,'UTF-8') COLUMNS id Integer width 2,parsedate(date1,'dd-mm-yyy') Date width 10 NO ROW DELIMITER) AS A
But I get this error on deploying the VDB -
org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "2,parsedate[*]([*]date1," at line 1, column 156.
Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...
I am trying to read a fixed width file which contains dates like 10-10-2014.
Can you point me to what could be wrong in the query.
Can you also post one single line of your file that you are trying to parse?
That texttable syntax is not correct. The only date format supported by default is the JDBC standard yyyy-MM-dd. If you need a different format, you would just parse the column as a string and then in the select clause put the appropriate parsedate/conversion function.