I've some common table expression ('with' statements) that I'd like to add to all <select> elements in my XML. What's the best way to use CTEs without having to repeat them all over?
I was thinking, maybe I factor out and keep the CTEs as a snippet somewhere and refer to that from within the <select>. Something like this:
<canned>
with foo as (
select ...
), bar as (
select ...
)
</canned>
<select ...>
<!-- somehow expand above CTS here -->
select * from foo f join bar ...
</select>
<select ...>
<!-- somehow expand above CTS here -->
select * from foo f join bar ...
</select>
Any thoughts?
Thanks,
Jeenu