Hi - I have a UNION'ed query which needs to include a NULL field as BLOB field. Here's a simplified version of the SQL :-

select per.id as per_id, per.name, per.portrait from per
union
select auxper.master_per_id as per_id, auxper.name, cast(null as blob) as portrait from auxper

The problem occurs with the "cast(null as blob) as portrait" :-

ElevateDB Error #700 An error was found in the statement at line 1 and column 1726 (Invalid expression "portrait" found, BLOB expressions not allowed)

Please can you tell me how to accomplish the above?

Many thanks!

Hedley Muscroft
Pioneer Software

Hedley,

> Please can you tell me how to accomplish the above?

Union all instead of union should work:

select per.id as per_id, per.name, per.portrait from per
union all
select auxper.master_per_id as per_id, auxper.name, cast(null as blob)
as portrait from auxper

Regards Uli

Well blow me down - it does indeed work... although I'm not sure I understand why UNION ALL should work while UNION doesn't?!

Thanks Uli.

Hedley

>Well blow me down - it does indeed work... although I'm not sure I understand why UNION ALL should work while UNION doesn't?!

Surprises me as well. A wild guess as to the reason UNION ALL simply adds all of the rows without any checking. A UNION checks to make sure there are no duplicates. If you then think a BLOB can be a couple of gig in size I suspect that Tim didn't want complaints about the speed of the SQL due to the "small" amount of time required for the comparison.

Just a guess though.

Roy

Hedley,

> Well blow me down - it does indeed work... although I'm not sure I understand why UNION ALL should work while UNION doesn't?!

I don't know why this work either. I just remembered a similar problem I
had some time ago.

Uli

Hedley,

<< Well blow me down - it does indeed work... although I'm not sure I
understand why UNION ALL should work while UNION doesn't?! >>

Duplicate removal - EDB uses indexes for duplicate checking/removal, and you
can't use BLOB columns with indexes.

Tim Young
Elevate Software
www.elevatesoft.com