the new period table functions provide some great functionality to merge and split periods in different ways. But now I have a problem with a very typical problem: Having two or more tables with a key field and a period, I would like to get the common sequence of the periods of the same key over all the tables.
The general solution is easy: build the UNION of all (key, period) over all the tables and let the period function TD_SEQUENCED_COUNT work on the result. But when I try to use a UNION in the WITH part of the select, I get the syntax error "HASH BY/LOCAL ORDER not allowed on derived tables/views with set operations". Since HASH BY and LOCAL ORDER is mandatory with all period table functions, it seems that I cannot solve my problem with one easy step.
Do I really have to create a physical table with the result of the UNION as a first step and then use this table in the WITH part? Is there no way to define and use the UNION and the table function in the same stament? To me, it seems to be an unnecessary overhead to actually create, write, read and drop the additional table which is only a temporary result of the computation. And as I said in the beginning, combining periods from more than one table is a very common task in our environment.
i didn't notice that restriction before (but just found that DISTINCT is similar), it's really annoying :-(
I don't understand why it's not allowed as it always has to build a spool first to sort the data, you might do an enhancement request.
As a workaround i would suggest a temporary table, Volatile or Global Temp depends on your needs. And this might even have an advantage when you need the same WITH more than once within your session :-)