1

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

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
Chris
  • 2,447
  • 1
  • 21
  • 27

1 Answers1

1

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.)

Dave Costa
  • 47,262
  • 8
  • 56
  • 72