4 ms·
Enjoy. A CTE, Xpath, and Oracle-specific hierarchical bits for a data report I'm generating. My favorite part was the XML in a CLOB column where all the value
by tom_b 13y ago
Enjoy. A CTE, Xpath, and Oracle-specific hierarchical bits for a data report I'm generating. My favorite part was the XML in a CLOB column where all the values were stored in XML element attributes. I tend to build these monstrosities up in a very repl-like way. SQL actually led me to Lisp . . .
BTW, I can't imagine any of the below is legible, but I find myself enjoying these types of things.
with bgu as
(select
protocol_code,
application_code,
description,
lab,
proj."seqno",
proj."project",
proj."protocol",
'false' "nexttag",
'true' "materialtag"
from
protocol,
xmltable('for $a in /*
for $n at $nidx in //*/MATERIAL
return <result>
<project>{fn:data($n/@project)}</project>
<protocol>{fn:data($n/@protocol)} </protocol>
</result>'
passing xmltype(protocol.xml)
columns
"seqno" for ordinality,
"project" varchar2(100),
"protocol" varchar2(100)) proj
where
active = 'Y'
union
select
protocol_code,
application_code,
description,
lab,
proj."seqno",
proj."project",
proj."protocol",
'true' "nexttag",
'false' "materialtag"
from
protocol,
xmltable('for $a in /*
for $n at $nidx in //*/NEXT
return <result>
<project>{fn:data($n/@project)}</project>
<protocol>{fn:data($n/@protocol)}</protocol>
</result>'
passing xmltype(protocol.xml)
columns
"seqno" for ordinality,
"project" varchar2(100),
"protocol" varchar2(100)) proj
where
active='Y'
order by 1,2,4 )
select
*
from
(select distinct
protocol_code,
sys_connect_by_path(protocol_code,'||||'),
connect_by_isleaf "isleaf"
from bgu
where protocol_code like 'PROTNAME%'
connect by nocycle prior "protocol" = protocol_code)
where "isleaf"=1
- ajw0100 13y agoThis is a breeze. Don't make me paste some of the wacky SQL I've seen generated from Oracle's BI tools when they are being abused.
- swalsh 13y agoI'm just biting my time until someone makes a "Daily WTF" website for monster queries. My worst was this sync that took data from one database, and put it into another one. The thing though is the source database was a CRM system that is on its 3rd iteration. So there's a million random fields from old systems that have accumulated over the years that need to be checked.
- ajw0100 13y agohttp://oracle-wtf.blogspot.com/ http://oracle-wtf.blogspot.com/ This might have been what you're looking for if it was still updated. Still some funny stuff there though.
- jmelloy 13y agoI can't stand the XMLTable syntax. I like the idea of turning an xpath expression into a result set, but in practice it's a massive pain in the ass.
- tom_b 13y agoXML and Oracle drives me crazy in general. You wind up with neither a relational model nor a generally useful way of data storage/exchange. I am almost always working with legacy schemas, many generated by long-extinct tools or developers, and being a genuinely curious person, wandering down strange code paths to see if I can satisfy random reporting requests.