473,320 Members | 1,853 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ORDERING by a FUNCTION() with simple-integer

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
20 2450

"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
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
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
> 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
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
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
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
>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
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
>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
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
>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
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
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
>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
Thanx for the reply!

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

B.

Dec 13 '05 #17
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
>> ...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
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
Thanx for the information. It is interesting.

B.

Dec 14 '05 #21

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jeff Thies | last post by:
I have 10 select boxes, they have select values of 1 to 10 (one of each value). I'd like to be able to change one of the select boxes and have the rest reorder themselves accordingly. I...
15
by: Kay Schluehr | last post by:
Here might be an interesting puzzle for people who like sorting algorithms ( and no I'm not a student anymore and the problem is not a students 'homework' but a particular question associated with...
6
by: Brendan.Collins | last post by:
Hi I have a javascript problem that has been annoying me for two days now and thought that a javascript expert might have the magic solution. I am populating a table dynamically from the...
2
by: D. Dante Lorenso | last post by:
First I created a function that selected the next available pin code from a table of pre-defined pin codes: CREATE FUNCTION "public"."get_next_pin_code" () RETURNS varchar AS' DECLARE...
9
by: Project2501a | last post by:
hey guys, Question about the internal workings of Access. How are controls added in the Form.Controls collection? I mean, in which order? the order place them on the form? is there a way to...
3
by: Ryan | last post by:
My project uses the /ORDER specifier to order functions as specified in file containing the decorated names of packaged functions (COMDATs). I am in the process of upgrading from VC6 to VC.NET 2003....
3
by: marc | last post by:
I want to create a strongly typed collection and use DictionaryBase I add a number of objects but when i loop through the list the data is ordered in a strange maner. How is this possible? Can...
23
by: illegal.prime | last post by:
Hi all, is there a key value collection in .Net that maintains the ordering in which I add items. I'm finding that I'm creating a number of classes that contain a name and then some object. I...
36
by: dspfun | last post by:
Hi! Is there any way in C that one can guarantee that instructions are executed in the same order as they are written in the program? For example, say I have the following: instruction1;...
20
by: Mr.SpOOn | last post by:
Hi, I need a structure to represent a set of integers. I also need to perform on this set some basic set operations, such as adding or removing elements, joining with other sets and checking for...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.