ORA-22950 when selecting from view which includes 2 different XML documents
450441Sep 21 2006 — edited Sep 25 2006I have a view v1 as
SELECT tx_id, 'SM' tx_type, XMLELEMENT("SAMPLE_MISSING_MESSAGE",XMLFOREST(...)) xml_data
FROM sample_missing;
and view v2 as
SELECT tx_id, 'SR' tx_type, XMLELEMENT("SAMPLE_REJECTED_MESSAGE",XMLFOREST(...)) xml_data
FROM sample_rejected;
The xml_data has a different format for each view. I can select from each fine.
If I create a view v1_2 as
SELECT tx_id, tx_type, xml_data FROM v1
UNION
SELECT tx_id, tx_type, xml_data FROM v2
Then when I try to select from v1_2 I get ORA-22950: cannot order objects with without MAP or ORDER method.
But 1) I'm not trying to order by the xml_data column
2) there is currently no data in sample_rejected table
I'd really like to do this (on a larger scale, probably about a dozen tables involved). I wouldn't be sorting on the xml column, and I would be getting based on tx_type i.e. only one type of XML document at a time.
Any suggestions? I'm on 9.2.0.4.