使用简单整数对FUNCTION（）进行排序 [英] ORDERING by a FUNCTION() with simple-integer

问题描述

在FUNCTION中的ORDER BY一个简单整数，导致SQL0440N，

，除非FUNCTION期望INTEGER作为其参数。例如：


DECLARE GLOBAL TEMPORARY表A（A CHAR（1））

INSERT INTO SESSION.A VALUES（ '' A ''），（ '' b ''）

创建函数A（A炭（1））返回炭（1）DETERMINISTIC NO EXTERNAL

ACTION返回

选择一个来自SESSION.A订购一个（1）

DROP FUNCTION A

DROP TABLE SESSION.A


是有一种方法可以在ORDER

BY子句的FUNCTION中传递一个简单整数吗？


B.

解决方案

" Brian Tkatch" <马*********** @ ThePentagon.com>在消息中写道

news：11 ********************* @ g43g2000cwa.googlegro ups.com ...

ORTER BY FUNCTION中的一个简单整数，导致SQL0440N，
除非FUNCTION期望INTEGER作为其参数。例如：

DECLARE GLOBAL TEMPORARY TABLE A（一个CHAR（1））
插入SESSION.A VALUES（''''），（''b''）
CREATE FUNCTION A（A炭（1））返回炭（1）DETERMINISTIC NO EXTERNAL
ACTION返回
选择FROM SESSION.A ORDER BY A（1）
DROP功能的
DROP TABLE SESSION.A

有没有办法在ORDER
BY子句的FUNCTION中传递一个简单整数？

我不理解你的函数中的SELECT。什么是订购A（1）

应该做什么？我想知道这种奇怪的语法是否能让你的函数保持在工作状态.......如果你写''ORDER BY A'，这个函数会更好吗？


Rhino

SQL0440N的原因是你的函数的参数是CHAR（1）。

当你传递整数1时。 br />
所以，DB2说没有这样的功能（Name是''A''，参数是

整数）。

布莱恩TKATCH写道：
通过简单的整数的函数内的顺序，结果在SQL0440N，
除非函数需要的整数。作为其参数。例如：

DECLARE GLOBAL TEMPORARY TABLE A（一个CHAR（1））
插入SESSION.A VALUES（''''），（''b''）
CREATE FUNCTION A（A炭（1））返回炭（1）DETERMINISTIC NO EXTERNAL
ACTION返回
选择FROM SESSION.A ORDER BY A（1）
DROP功能的
DROP TABLE SESSION.A

有没有办法在ORDER
BY子句的FUNCTION中传递一个简单整数？

应该做什么？请记住：SQL（以及它基于的关系模型

）是面向集合的。集合的元素有 -

定义 - 没有排序。所以在你想要的时候在函数中添加一个ORDER BY

是没有任何意义的。


你可以使用一个ORDER BY如果你只添加一个FETCH FIRST n ROWS ON $

，因为在这种情况下排序很重要。否则，唯一的地方

其中ORDER BY对返回的行的排序有影响

客户端是最外部的全选。请注意，您可以将ORDER

BY嵌套到子选择中，但只有在最外面的


-

Knut Stolze

DB2信息集成开发

IBM德国

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

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

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

这篇关于使用简单整数对FUNCTION（）进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！