Union Character Data with Numeric data

SribbyKaligotd · ‎10-31-2007

Hi All,

I am trying to create a XML output by running a Teradata SQL . For this I need to have multiple Union sets in the query so that the order is maintained.
I need to have a lot of commented XML code in quotes just used as dummy select in my query. But down the blocks where the data fetch is required I am facing an issue.

I am trying to convert a numeric value fetched from the Database using the Cast function.
Eg:

Select XMLText from(
Select 1 XML Order , XMLText from (select cast(' ' as Varchar(3000)) XMLText ;) a
Union ALL
Select 2 XMLORder, cast (sum(units) as Varchar(30) XMLText

It throws an error 3617: FORMAT 'YYYY-MM-DD' does not match the datatype.

Please Help.

dnoeth · ‎11-02-2007

I don't know about your case, but for any Set operations the first select determines the datatype and format of that column.

You should check if there's a Date column in your first select and cast it to Varchar.

Dieter