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.

[Rhino]

"Brian Tkatch" <Ma***********@ ThePentagon.com > wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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

[Tonkuma]

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

[Knut Stolze]

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

[Brian Tkatch]

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

[Brian Tkatch]

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.

[Knut Stolze]

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

[Knut Stolze]

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

[Brian Tkatch]

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

[Knut Stolze]

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
<correlatio n-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_Srs Id()
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