My Oracle Support Banner

Wrong Results from UNION ALL Used Within WITH Section Using XMLTABLE (Doc ID 1589301.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

A UNION ALL of select statements from a WITH section, where the WITH section selects from XMLTABLE, is not returning the expected result.

Below is an example query:

with
xmldata as (
select t.izo, t.red_izo, t.zar_pln, t.misto, t.ulice, t.psc, t.rok
from (select xmltype('<?xml version="1.0"
encoding="UTF-8"?><ciselnik_stredni_skola.xml><stredni_skola.xml
kod="000010189" redizo="" nazev="Strední odborné uciliste strojírenské"
nazev_obce="Vsetín" ulice="Jasenická 1487" psc="75501"
rok_maturity="1999"/></ciselnik_stredni_skola.xml>') as object_value from
dual),
xmltable(
'/ciselnik_stredni_skola.xml/stredni_skola.xml'
passing object_value
columns izo varchar2(9) path '@kod',
red_izo varchar2(9) path '@redizo',
zar_pln varchar2(255) path '@nazev',
misto varchar2(255) path '@nazev_obce',
ulice varchar2(255) path '@ulice',
psc number(5) path '@psc',
rok number(4) path '@rok_maturity') t
),
convdata as (
select izo, zar_pln, misto, ulice, psc, rok
from xmldata
union all
select red_izo as izo, zar_pln, misto, ulice, psc, rok
from xmldata
where red_izo is not null
)
select count(*)
from convdata;

which is returning 'no row selected' while each separated statement is returning rows.

 

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.