ORDERING by a FUNCTION() with simple-integer

[Brian Tkatch]

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.

[Brian Tkatch]

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

[Brian Tkatch]

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.

[Brian Tkatch]

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

[Knut Stolze]

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

[Knut Stolze]

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

[Brian Tkatch]

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

[Brian Tkatch]

Thanx for the reply!

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

B.

[Knut Stolze]

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

[--CELKO--]

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

[Serge Rielau]

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