I am getting error “Brighthouse other specific error: type not supported” when I try to output a date-casted constant into an outfile.

This is a query that illustrates the point, although the issue is not specific to the fx table I’m using, happens with all tables I’ve tried.  Same query works with “as unsigned”.  Also fails if you cast a date into a date (not that you’d want to, but thought I’d see if it worked).  Everything works if you do a normal select rather than an “into outfile”.

select Cast(20100714 as Date) as COBDate
into outfile ‘f:/foo.csv’
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”’
LINES TERMINATED BY ‘\n’
from fx f
limit 10;

Any help much appreciated.

skubeedooo,

Is ‘20100714’ a literal string value or the name of a column?  I assume it’s a string so selecting it from a table and adding the limit have no meaning.

That said, I’ve tried it several times in different forms and either write one record (with no table name in the statement) or no records (with any table name).  In no case did I receive an error.

I have found that placing the entire SQL statement prior to the ‘INTO OUTFILE’ clause works best.  Also, there is no need to add the ‘LINES TERMINATED BY’ clause.

I suggest selecting and casting an actual CHAR column from a table, reordering your statement as mentioned above, and removing the line termination clause.

Let us know how that works for you.

I had trimmed the query down to its bare essentials to help to see what was going wrong..anyway, i tried your suggestion but it doesn’t work either.

This gives same error.  I have casted without problem, and have done into outfile with no problem, it’s just the combination of casting a constant and doing an into outfile at the same time.  Incidentally this query works when you do an insert into table.

I may be missing something here, but why the need for a cast, if you are just putting the result into a text format file?

Wouldn’t something like this work just as well?:

Geoffrey

Yeah, maybe I can.  I assumed that the FIELDS ENCLOSED BY ‘\”’ would make the date be enclosed by quotes, and then upon reload the quotes would make the date look like a string, and then infobright would not be able to understand that it was a date not a string. 

How clever is the loader?  Can it figure out that a quote-enclosed date is still a date?

yes, in my tests, it can.

Geoffrey