Can analytic functions be used in a CONNECT BY query? Are there limits?
This problem occurs in Oracle 11.1.0.6.0, 10.2 and 10.1.
Here is the presenting problem:
Starting with data like this:
CREATE TABLE enrollment
( name VARCHAR2 (10)
, coursenumber NUMBER
);
INSERT INTO enrollment (name, coursenumber) VALUES ('Ted', 101);
INSERT INTO enrollment (name, coursenumber) VALUES ('Ted', 102);
INSERT INTO enrollment (name, coursenumber) VALUES ('Ted', 103);
INSERT INTO enrollment (name, coursenumber) VALUES ('Mary', 102);
INSERT INTO enrollment (name, coursenumber) VALUES ('Mary', 104);
INSERT INTO enrollment (name, coursenumber) VALUES ('Hiro', 101);
INSERT INTO enrollment (name, coursenumber) VALUES ('Hiro', 104);
INSERT INTO enrollment (name, coursenumber) VALUES ('Hiro', 105);
COMMIT;
I'm trying to get cross-tab output like this:
NAME TXT
---------- ------------------------------
Hiro 101 104 105
Mary 102 104
Ted 101 102 103
without knowing before-hand what course numbers, or even how many course numbers, will be in the results.
My strategy was to use LPAD to make the course numbers always occupy 5 spaces.
If n "columns" needed to be left blank before the number, I wanted to add 5n extra spaces.
I tried this:
WITH universe AS
(
SELECT name
, coursenumber
, DENSE_RANK () OVER ( ORDER BY coursenumber) AS cnum
, ROW_NUMBER () OVER ( PARTITION BY name
ORDER BY coursenumber
) AS snum
FROM enrollment
)
SELECT name
, REPLACE ( SYS_CONNECT_BY_PATH ( LPAD ( TO_CHAR (coursenumber)
, 5 * (cnum - LAG (cnum, 1, 0)
OVER ( PARTITION BY name
ORDER BY coursenumber
) )
)
, ','
)
, ','
) AS txt
FROM universe
WHERE CONNECT_BY_ISLEAF = 1
START WITH snum = 1
CONNECT BY snum = PRIOR snum + 1
AND name = PRIOR name
ORDER BY name
;
but the txt column was always NULL.
I tried showing some of the intermediate calculations:
WITH universe AS
(
SELECT name
, coursenumber
, DENSE_RANK () OVER ( ORDER BY coursenumber) AS cnum
, ROW_NUMBER () OVER ( PARTITION BY name
ORDER BY coursenumber
) AS snum
FROM enrollment
)
SELECT name
, REPLACE ( SYS_CONNECT_BY_PATH ( LPAD ( TO_CHAR (coursenumber)
, 5 * (cnum - LAG (cnum, 1, 0)
OVER ( PARTITION BY name
ORDER BY coursenumber
) )
)
, ','
)
, ','
) AS txt
, coursenumber
, cnum
, LAG (cnum, 1, 0) OVER ( PARTITION BY name
ORDER BY coursenumber
) AS lag_cnum
FROM universe
-- WHERE CONNECT_BY_ISLEAF = 1
START WITH snum = 1
CONNECT BY snum = PRIOR snum + 1
AND name = PRIOR name
ORDER BY name
;
and they all seemed reasonable:
NAME TXT COURSENUMBER CNUM LAG_CNUM
---------- ------------------------------ ------------ ---------- ----------
Hiro 101 1 0
Hiro 104 4 1
Hiro 105 5 4
Mary 102 2 0
Mary 104 4 2
Ted 101 1 0
Ted 102 2 1
Ted 103 3 2
but txt was still NULL.
I got around the problem by computing the LAG in a sub-query (see [this thread|http://forums.oracle.com/forums/message.jspa?messageID=3875566#3875566]), but I'd like to know why LAG didn't work in the CONNECT BY query, or at least within SYS_CONNECT_BY_PATH.
I've had other problems before trying to use analytic functions in CONNECT BY queries. Sometimes, the presence of an analytic function woudl cause CONNECT BY to never work, sometimes it would destroy the order of the output. (Sorry, I don't have those examples handy now.)
Are there limitations on using analytic functions in a CONNECT BY query?
is there a work-around other than computing the analytic functions in a sub-query?
Thanks.