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

ORDERING by a FUNCTION() with simple-integer

P: n/a
An ORDER BY a simple-integer inside a FUNCTION, results in SQL0440N,
unless the FUNCTION expects an INTEGER as its parameter. For example:

DECLARE GLOBAL TEMPORARY TABLE A(A CHAR(1))
INSERT INTO SESSION.A VALUES ('a'), ('b')
CREATE FUNCTION A(A char(1)) RETURNS char(1) DETERMINISTIC NO EXTERNAL
ACTION RETURN A
SELECT A FROM SESSION.A ORDER BY A(1)
DROP FUNCTION A
DROP TABLE SESSION.A

Is there a way to pass a simple-integer inside a FUNCTION of an ORDER
BY clause?

B.

Dec 12 '05 #1
Share this Question
Share on Google+
20 Replies


P: n/a

"Brian Tkatch" <Ma***********@ThePentagon.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
An ORDER BY a simple-integer inside a FUNCTION, results in SQL0440N,
unless the FUNCTION expects an INTEGER as its parameter. For example:

DECLARE GLOBAL TEMPORARY TABLE A(A CHAR(1))
INSERT INTO SESSION.A VALUES ('a'), ('b')
CREATE FUNCTION A(A char(1)) RETURNS char(1) DETERMINISTIC NO EXTERNAL
ACTION RETURN A
SELECT A FROM SESSION.A ORDER BY A(1)
DROP FUNCTION A
DROP TABLE SESSION.A

Is there a way to pass a simple-integer inside a FUNCTION of an ORDER
BY clause?

I don't understand the SELECT in your function. What is "ORDER BY A(1)"
supposed to do? I wonder if that odd syntax is keeping your function from
working.... Does the function work better if you write 'ORDER BY A'?

Rhino
Dec 12 '05 #2

P: n/a
The reason of SQL0440N is that a parameter of your function is CHAR(1).
While you passed integer 1.
So, DB2 said there is no such function(Name is 'A' and parameter is
integer).

Dec 13 '05 #3

P: n/a
Brian Tkatch wrote:
An ORDER BY a simple-integer inside a FUNCTION, results in SQL0440N,
unless the FUNCTION expects an INTEGER as its parameter. For example:

DECLARE GLOBAL TEMPORARY TABLE A(A CHAR(1))
INSERT INTO SESSION.A VALUES ('a'), ('b')
CREATE FUNCTION A(A char(1)) RETURNS char(1) DETERMINISTIC NO EXTERNAL
ACTION RETURN A
SELECT A FROM SESSION.A ORDER BY A(1)
DROP FUNCTION A
DROP TABLE SESSION.A

Is there a way to pass a simple-integer inside a FUNCTION of an ORDER
BY clause?


What is that supposed to be doing? Remember: SQL (and the relational model
it is based on) is set-oriented. And the elements of sets have - per
definition - no ordering. So it doesn't make any sense to add an ORDER BY
inside the function as you're trying to do.

You can use an ORDER BY if you add something like a FETCH FIRST n ROWS ONLY
because the ordering does matter in this case. Otherwise, the only place
where an ORDER BY has an influence on the sorting of the rows returned to
the client is the outer-most full-select. Note that you can nest an ORDER
BY into sub-selects, but that will only have an effect if the outer-most
select uses an ORDER BY ORDER OF <correlation-name>

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 13 '05 #4

P: n/a
> What is "ORDER BY A(1)" supposed to do?

It is supposed to ORDER the rows based on the output of the FUNCTION,
rather than the actual COLUMN itself.

B.

Dec 13 '05 #5

P: n/a
ORDER BY accepts a simple-integer as a parameter, which references the
column-order in the select-list. Therefore, the 1 should be treated as
a COLUMN references and not a number.

Further, being the FUNCTION must be DETERMINISTIC to be in the ORDER BY
clause, passing a literal would be useless.

B.

Dec 13 '05 #6

P: n/a
Brian Tkatch wrote:
ORDER BY accepts a simple-integer as a parameter, which references the
column-order in the select-list. Therefore, the 1 should be treated as
a COLUMN references and not a number.
That's not the case. Have a look at the syntax diagram for the
<order-by-clause>: http://tinyurl.com/9vdcc

You will find that the numeric value is a "simple-integer" only. When you
use the function, you are automatically in the branch "sort-key-expression"
and that doesn't handle simple-integers at all.
Further, being the FUNCTION must be DETERMINISTIC to be in the ORDER BY
clause, passing a literal would be useless.


No, it doesn't have to. At least there is no such restriction in DB2.
However, a non-deterministic function might give you unpredictable results.
But also note that any function that accesses (some) special registers like
CURRENT SCHEMA must be defined as NOT DETERMINISTIC, even if it is
deterministic within the scope of the current query.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 13 '05 #7

P: n/a
Brian Tkatch wrote:
ORDER BY accepts a simple-integer as a parameter, which references the
column-order in the select-list. Therefore, the 1 should be treated as
a COLUMN references and not a number.


Personally, I think that allowing those simple integers in the ORDER BY
clause was one of the worst ideas of the standardization committee. It
leaves the relational model behind (or ahead, depending on your view), and
it is not applied consistently, for example you can't do the same in a
GROUP BY. I would not use such a construct because it is only the cause
for confusions like the one we're just discussion now.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 13 '05 #8

P: n/a
>What is that supposed to be doing?

ORDERing the output BY the output of the FUNCTION.
And the elements of sets have - per definition - no ordering.
....which is why databases added a clause called ORDER BY. So that
ordering is possible.
So it doesn't make any sense to add an ORDER BY inside the function as you're trying to do.
Being i want it ORDERed BY the output of the FUNCTION, it makes a great
deal of sense.
Otherwise, the only place where an ORDER BY has an influence on the
sorting of the rows returned to the client is the outer-most full-select.
It is on the outer-most full-select.
Note that you can nest an ORDER BY into sub-selects, but that will only have an effect if the outer-most
select uses an ORDER BY ORDER OF <correlation-name>


That's way too confusing.

As a workaround i added the FUNCTION to the output list itself, and
ORDERed BY that COLUMN.

B.

Dec 13 '05 #9

P: n/a
Brian Tkatch wrote:
Otherwise, the only place where an ORDER BY has an influence on the
sorting of the rows returned to the client is the outer-most full-select.
It is on the outer-most full-select.


Oops, I thought your ORDER BY was inside the function. Sorry!
Note that you can nest an ORDER BY into sub-selects, but that will only
have an effect if the outer-most select uses an ORDER BY ORDER OF
<correlation-name>


That's way too confusing.


Actually, it is quite obvious:

SELECT ...
FROM ( SELECT ...
FROM ...
WHERE ...
ORDER BY ... ) AS t(...)
WHERE ...
ORDER BY ORDER OF t
This comes in extremely handy for export tools that take a whole sub-select
as input and if you need to add some more logic. For example, in the
Spatial Extender export routines it is necessary to extract some
information about the coordinate system in addition to the actual data that
needs to be exported. So the logic gets the select statement of the data
that shall be exported and then does something like this with it:

SELECT <column-list>, <spatial-column>..ST_SrsId()
FROM ( <given-subselect> ) AS x(<column-list>)
ORDER BY ORDER OF x

That way, no additional scan is necessary to call the ST_SrsId() function
(it is done on the fly), and it is also possibly to retain the
user-intended ordering for the exported data.
As a workaround i added the FUNCTION to the output list itself, and
ORDERed BY that COLUMN.


You can simply give the function in the ORDER BY the parameter that it
expects, i.e. values of type CHAR(1) that are stored in the column:

SELECT a
FROM a
ORDER BY A(a)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 13 '05 #10

P: n/a
>You will find that the numeric value is a "simple-integer" only. When you
use the function, you are automatically in the branch "sort-key-expression"
and that doesn't handle simple-integers at all.
OK, i see it, thanx. I didn't realize db2 limited itself like that.

Basically, a FUNCTION cannot be used on a ORDER BY in a UNION query.
I'd have to CREATE a VIEW on the UNION first.
No, it doesn't have to.


I see. You are correct.

My misconception was based on when i made a simple FUNCTION:

CREATE FUNCTION A(A char(1)) RETURNS char(1) RETURN A

And got:

SQL0583N The use of routine "<schema>.A" is invalid because it is not
deterministic or has an external action. SQLSTATE=42845

So i assumed DETRMINISTIC was required.

B.

Dec 13 '05 #11

P: n/a
I like it, because it works well with UNIONs. In fact, that is
basically the only time i use it.

BTW, i think the worst decision was the JOIN of the FROM clause. Every
other part of SQL is intuitive to me. The join clause just seems
backwards. Probably because it is goal-oriented (the TABLEs are being
joined in the end) whereas i see SQL as process-oriented (the WHERE
clauses decided how the join is done).

B.

Dec 13 '05 #12

P: n/a
>Actually, it is quite obvious:

I appreciate the example, and i will bear it in mind should i ever need
to use it. Though, i still find it confusing. As in a "this is not
needed, why would i need to do this backwards thing" sort of way.
You can simply give the function in the ORDER BY the parameter that it
expects, i.e. values of type CHAR(1) that are stored in the column:


The actual case is a UNION query from two different TABLEs, so that is
not possible.

B.

Dec 13 '05 #13

P: n/a
Brian Tkatch wrote:
You will find that the numeric value is a "simple-integer" only. When you
use the function, you are automatically in the branch
"sort-key-expression" and that doesn't handle simple-integers at all.


OK, i see it, thanx. I didn't realize db2 limited itself like that.

Basically, a FUNCTION cannot be used on a ORDER BY in a UNION query.
I'd have to CREATE a VIEW on the UNION first.


Simple nest the union into a sub-select as this gives you the opportunity to
assign new column names:

SELECT ...
FROM ( SELECT ... FROM ...
UNION
SELECT ... FROM ... ) AS t(col1, col2, ...)
ORDER BY fnc(colX)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 13 '05 #14

P: n/a
Brian Tkatch wrote:
I like it, because it works well with UNIONs. In fact, that is
basically the only time i use it.

BTW, i think the worst decision was the JOIN of the FROM clause. Every
other part of SQL is intuitive to me. The join clause just seems
backwards. Probably because it is goal-oriented (the TABLEs are being
joined in the end) whereas i see SQL as process-oriented (the WHERE
clauses decided how the join is done).


A join condition describes how the join is to be done. And the join is done
in the FROM clause. I was taught that the join condition should always be
in the FROM clause as this is the place where it belongs. The WHERE clause
is just for restricting the rows from the (joined) table. This is a
different thing than the join condition, and both should not be mixed.

On the side: from an implementation perspective it is much easier if the
join condition is in the from clause as you can push-down the predicate(s)
right away. If the join conditions are in the WHERE clause, you have a
much harder time figuring this out. That's what I learned when we
implemented our own database system at school. Granted, DB2 uses a
cost-based optimizer so things are quite different anyways.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 13 '05 #15

P: n/a
>A join condition describes how the join is to be done. And the join is done
in the FROM clause. I was taught that the join condition should always be
in the FROM clause as this is the place where it belongs. The WHERE clause
is just for restricting the rows from the (joined) table. This is a
different thing than the join condition, and both should not be mixed.
Ah, but the FROM clause itself is what joins the TABLE, albeit
Cartesian. Everything past that *is* a restriction, to only get useful
records.
On the side: from an implementation perspective it is much easier if the
join condition is in the from clause as you can push-down the predicate(s)
right away. If the join conditions are in the WHERE clause, you have a
much harder time figuring this out.


That makes a lot of sense.

I like WHERE clauses though for readability as well. Then again, i
have little experience with the join clause anyway, so i see them as a
jumble, as opposed to recognizing a familiar friend.

B.

Dec 13 '05 #16

P: n/a
Thanx for the reply!

I still think it's backwards, but at least its doable.

B.

Dec 13 '05 #17

P: n/a
Brian Tkatch wrote:
A join condition describes how the join is to be done. And the join is
done
in the FROM clause. I was taught that the join condition should always be
in the FROM clause as this is the place where it belongs. The WHERE
clause
is just for restricting the rows from the (joined) table. This is a
different thing than the join condition, and both should not be mixed.


Ah, but the FROM clause itself is what joins the TABLE, albeit
Cartesian. Everything past that *is* a restriction, to only get useful
records.


Well, inner joins with a join condition don't give the cartesian product of
all rows that needs to be further restricted. Instead, they already
produce the "right" join. Semantically, both things give the same result.
But conceptually, we're talking apples and oranges here!

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 13 '05 #18

P: n/a
>> ...which is why databases added a clause called ORDER BY. So that ordering is possible. <<

NO. That is why the ORDER BY clause is defined for cursors, never on
SQL statements. Cursors are for getting Relational data out of sets
and into host (i.e. non-SQL, procedural) languages)
Being I want it ORDER BY-ed the output of the FUNCTION, it makes a great deal of sense. <<


Not in RDBMS and Standard SQL! Get **any** book on the basics of RDBMS
and read it. You have missed the foundations and are still thinking
in terms of sequential file structure. Learn to think in terms of sets
and not sequences.

Dec 13 '05 #19

P: n/a
Brian Tkatch wrote:
The actual case is a UNION query from two different TABLEs, so that is
not possible.

Sorry for coming late to the party....
ORDER BY in SQL is attached to SELECT.
UNION (ALL) is not a SELECT.
Ironically we had to actively "disable" ORDER BY on UNION to adhere to
these rules. DB2 was originally happily eating it.
The solution of pushing the UNION into a derived table has been
provided, so no harm is done.
I agree with Knut that the decision to standardize ORDER BY with
constant numbers to mean column positions was a bad idea. It originated
from the limitatiosn of products at the time to match the expression in
teh select list with an an identical expressions in the ORDER BY clause.
Using correlation names from the select list in the order by clause
would have been a much cleaner solution.. too late.

Obviosuly ORDER BY in to sort the result set of a cursor is a good idea.
Also obviosuly ORDER BY in an OLAP expression (which may benested inside
a query) is a useful thing.
So what about a nested ORDER BY?
Well Knut as brought up one important point: Orthogonality.
E.g. if a tool like "Query Patroller" needs to take a user submitted
query and store it away for later retrieval of teh result set having an
orthoginal language is beneficial. So for reasons of orthogonality alone
ORDER BY in subquery was deemed necessary.
But there is another usage:
Combination of ORDER BY with FETCH FIRST (aka. TOP/LIMIT).
What this gives us is a "truncating" select operator. Truncating based
on an ORDER is nothing bad.
The trick is that the ORDER does NOT leak. The derived table is NOT ordered.
There is abolsutley nothing wrong with that from a relational point of view.
Arguing that such things are evil is like arguing that any other scalar
operation thnt PLUS and MINUS are evil. There are logical extensions
with new operators which one can make without breaking the model: MULT,
DIV, MOD, .... same with relational.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 13 '05 #20

P: n/a
Thanx for the information. It is interesting.

B.

Dec 14 '05 #21

This discussion thread is closed

Replies have been disabled for this discussion.