SQL Union question

[vetteguy]

I have a simple SQL statement in which I want to grab a date field, and if one does not exist force a date into a virtual field. Here is an example:

select
table1.id,
table1.beg_date virt1
from
table1
union
select
table1.id,
"08/09/2002" virt1
from
table1
where
beg_date is null

(that is a very simplified version, but you get the idea). Anyway, when I try to run it, I get the following error:

"Corresponding column types must be compatible for each UNION statement. Error in line 9 Near character position 1"

"This is an Informix database by the way"

Does anyone know of a way to do this? I'm not sure why the columns are incompatible, the DATE field is in the format of MM/DD/YYYY. Anyone have any suggestions? Thanks!

 

[SQL]

Try single quotes instead of the double quotes.

Also make sure you have a beg_date is not null in your where clause on the first part of the union.

Do the queries run by themselves when not UNIONed?

Sorry can't be more help. I ran this through SQL Server and it ran fine....I thought perhaps if your first select was picking up NULL's perhaps it was confusing Informix on what column type it should be.

 

[vetteguy]

Originally posted by: SQL
Try single quotes instead of the double quotes.

Also make sure you have a beg_date is not null in your where clause on the first part of the union.

Do the queries run by themselves when not UNIONed?

Sorry can't be more help. I ran this through SQL Server and it ran fine....I thought perhaps if your first select was picking up NULL's perhaps it was confusing Informix on what column type it should be.

Thanks for the ideas...I tried the single quotes and it didn't work. I will try the "is not null" but for testing I forced it to find one record that I know has a value for the date, and it still blew up.

 

[SQL]

Does informix have date conversion function? Such as: convert("8/1/02",datetime) or something like that?

 

[vetteguy]

Originally posted by: SQL
Does informix have date conversion function? Such as: convert("8/1/02",datetime) or something like that?

Not sure about that either. I have run into something similar before, but it was about 8 months ago and I don't remember how I solved it back then. D'oh.

 

[SQL]

Not sure about that either. I have run into something similar before, but it was about 8 months ago and I don't remember how I solved it back then. D'oh.

Don't feel bad, I can't remember half the time what I had for breakfast. 🙂

Anyways, good luck.