 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / December 2005ORDERING 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. > An ORDER BY a simple-integer inside a FUNCTION, results in SQL0440N, > unless the FUNCTION expects an INTEGER as its parameter. For example: [quoted text clipped - 9 lines] > 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 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). 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. > 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.  SignatureKnut Stolze DB2 Information Integration Development IBM Germany >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. >>You will find that the numeric value is a "simple-integer" only. When you >>use the function, you are automatically in the branch [quoted text clipped - 4 lines] > 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) SignatureKnut Stolze DB2 Information Integration Development IBM Germany Thanx for the reply! I still think it's backwards, but at least its doable. B. > 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. SignatureKnut Stolze DB2 Information Integration Development IBM Germany 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. > I like it, because it works well with UNIONs. In fact, that is > basically the only time i use it. [quoted text clipped - 4 lines] > 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. SignatureKnut Stolze DB2 Information Integration Development IBM Germany >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. >>A join condition describes how the join is to be done. And the join is >>done [quoted text clipped - 7 lines] > 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! SignatureKnut Stolze DB2 Information Integration Development IBM Germany > 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. > An ORDER BY a simple-integer inside a FUNCTION, results in SQL0440N, > unless the FUNCTION expects an INTEGER as its parameter. For example: [quoted text clipped - 9 lines] > 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> SignatureKnut Stolze DB2 Information Integration Development IBM Germany >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. >>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) SignatureKnut Stolze DB2 Information Integration Development IBM Germany >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. > 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 SignatureSerge Rielau DB2 SQL Compiler Development IBM Toronto Lab Thanx for the information. It is interesting. B. >> ...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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.