CODEWITHSUNDEEP
sqloracle-databaseoracle10ganalytic-functions
Chris
Chris

Reputation: 2477

Oracle Collect Over Analytical Function

Is it possible to use the 10g collect command as an analytical function by using OVER PARTITION or some other way?

e.g.

SELECT COLLECT(x) OVER (PARTITION BY y)
FROM table

Every time I try this there is a ora 3113 exception saying 'end-of-file on communication channel'

PS. I know I need to cast the result to make it useful, but for simplicities sake I'm leaving it out for now

Upvotes: 1

Views: 1335

Answers (1)

Dave Costa
Dave Costa

Reputation: 48131

COLLECT is not an analytical function, but can be used as a group function.

SELECT y, COLLECT(x) FROM table GROUP BY y

(Interesting, though: COLLECT is not listed as being an analytical function, but if I try to use it that way as in your example it appears to work at first, then stops after outputting some of the results and gives ORA-03113 as you said.)

Upvotes: 1

Related Questions