Re: Union Query Long Datatypes and a "Placeholder" Corresponding Value

Tim Marshall schrieb:
> Oracle server 8.1.7.4.0
>
> I'm dealing with an application over which I have no real control to be
> able to change table structure other than to add my own columns. I am
> trying to create a union query of the sort:
>
> SELECT c1, c2 FROM ....
> UNION ALL
> SELECT c1, c2 FROM ....
>
> When c2 data type is long in both tables I am fine. However, I have one
> case where c2 is a long data type in one table and there is nothing
> corresponding in the second table (it does not have a long datatype
> column). Is there a way to handle this?
>
> I've tried:
>
> SELECT c1, c2 FROM ....
> UNION ALL
> SELECT c1, '' c2 FROM ....
>
> in an attempt to have '' stand in for the long data type, ie, what I
> call a "Placeholder" corresponding value in my title. This results in
> ora 01790 expression must have same datatype as corresponding expression.
>
> Is there a way to handle this? I could add a "dummy" long into the
> second table, but this would be a last resort.
>
> Thanks in advance.

You are not allowed to use long datatypes in select list of set operators http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/sql_elem.htm#45443 Metalink Note:1022030.6 states, the restriction is removed with 9.2.x, Documentation states the opposite
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements2a.htm#45443 in my test i got the ORA-00997.
In 10gR2 long columns in select list of union works , however according to documentation it should not.
There is probably a documentation bug,
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i45441 To achieve your goal on 10gR2 you could easily select c1,c2 from table_with_longs
union all
select c1, NULL as c2 from table_without_longs

Best regards

Maxim Received on Mon Jan 23 2006 - 04:58:20 CST