By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Can we use ORDER BY clause inside a SQL UDF

P: n/a
Hi,
Can I use ORDER BY clause in my sql UDF that returns a table and has
the folloing body. (trial_udf.sql)

drop function aa@
create function aa()
returns TABLE(empno varchar(20))
language SQL
return select empno from employee order by empno@
When I try to run this UDf with command
db2 -td@ -vf trial_udf.sql

DB2 gives an error saying

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20211N The specification ORDER BY or FETCH FIRST n ROWS ONLY is
invalid.
LINE NUMBER=4. SQLSTATE=428FJ

Can somebody help me get the empno list in some sorted order.
Thanks in advance
mailar

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ma****@gmail.com wrote:
Hi,
Can I use ORDER BY clause in my sql UDF that returns a table and has
the folloing body. (trial_udf.sql)

drop function aa@
create function aa()
returns TABLE(empno varchar(20))
language SQL
return select empno from employee order by empno@
When I try to run this UDf with command
db2 -td@ -vf trial_udf.sql

DB2 gives an error saying

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20211N The specification ORDER BY or FETCH FIRST n ROWS ONLY is
invalid.
LINE NUMBER=4. SQLSTATE=428FJ

Can somebody help me get the empno list in some sorted order.
Thanks in advance
mailar

Hi,

This limitation has been placed on purpose to prevent exactly your
expectation :-)

The table function returns a (derived) table. Tables are by their very
definition non ordered.
IF DB2 were to allow this clause in table functions and views a mere
SELECT * FROM TABLE(AA()) AS F would still be no warrenty for an ordered
result.

Having said that we (the DB2 SQL language folks) are debating whether it
may make sense to acknowledge that objects have "natural order" (similar
to "natural joins").
That would mean that you could type what you planned above and DB2 would
pick up the order, IF you also use the ORDER OF clause:
SELECT * FROM TABLE(AA()) AS F ORDER BY ORDER OF F
Ommiting the clause would cause the nested order by to be dropped.
None of this is commited to any future release. We are talking pie in
teh sky as of now.
Until then the best you can do is to either exand the order by columns
or add a row_number() over (ORDER BY ...) to the select list and expose
it as a column in the result.

Cheers
Serge
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.