无法将 ORDER BY 命令添加到存储过程

发布于 2024-11-07 07:53:05 字数 905 浏览 3 评论 0原文

我正在将我维护的某些软件中的存储过程从 SQL Server SQL 转换为 Informix SQL,但问题很多。

基本上我会逐行转换每个部分,直到转换整个内容。

我有以下 CREATE PROCEDURE

CREATE PROCEDURE ifxdbase:dc_buildSP (WorkID INT, CompNo smallint)
CREATE TEMP TABLE Items
(
   Code smallint,
   Qty int,
   Total int
);

INSERT INTO Items
SELECT 
   tblDetails.code, 
   tblDetails.quantity,
   tblHead.quantity
FROM
   tblHead
INNER JOIN tblDetails ON (tblDetails.compno = tblDetails.compno AND tblDetails.id_num = tblHead.id_num)
WHERE tblHead.compno = CompNo AND tblHead.id_num = WorkID;
--ORDER BY tblDetails.code;

DROP TABLE Items;
END PROCEDURE

就目前情况而言,这工作正常,但是当我取消注释行 --ORDER BY tblDetails.seqno; (并从上一行)我收到“-201 发生语法错误”错误。

基本上,tblHead 是一系列订单标题,tblDetails 是每个订单详细信息的表格。选择和加入数据工作正常,尝试对其进行排序失败。

排序应该适用于原始 SELECT、IIRC 中的任何内容,因此我看不出可能会出现什么问题,在这里......

I'm converting a stored procedure in some software I'm maintaining from SQL Server SQL to Informix SQL, and problems are abundant.

Basically I'm converting each section line-by-line until I have the whole thing converted.

I have the following CREATE PROCEDURE:

CREATE PROCEDURE ifxdbase:dc_buildSP (WorkID INT, CompNo smallint)
CREATE TEMP TABLE Items
(
   Code smallint,
   Qty int,
   Total int
);

INSERT INTO Items
SELECT 
   tblDetails.code, 
   tblDetails.quantity,
   tblHead.quantity
FROM
   tblHead
INNER JOIN tblDetails ON (tblDetails.compno = tblDetails.compno AND tblDetails.id_num = tblHead.id_num)
WHERE tblHead.compno = CompNo AND tblHead.id_num = WorkID;
--ORDER BY tblDetails.code;

DROP TABLE Items;
END PROCEDURE

As it stands, this works fine, but when I uncomment the line --ORDER BY tblDetails.seqno; (and remove the semicolon from the previous line) I get a "-201 A syntax error has occurred" error.

Basically tblHead is a series of order headers and tblDetails is a table of the details of each of those orders. Selecting and joining the data works fine, trying to order it fails.

Ordering should work with anything from the original SELECT, IIRC, so I can't see what could be going wrong, here...

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

薔薇婲 2024-11-14 07:53:05

此处:

.....并非所有条款和选项
SELECT 语句可用于
您可以在查询中使用
插入语句。以下选择
不支持子句和选项
Informix 在 INSERT 语句中:

首先进入温度

ORDER BY 和 UNION

因此 Informix 中的 INSERT 命令不支持 ORDER BY。

我现在没有要测试的东西,但你可以尝试这样的方法作为解决方法:

INSERT INTO Items
SELECT code, dQuantity, hQuantity
  FROM (
    SELECT 
     tblDetails.code, 
     tblDetails.quantity dQuantity,
     tblHead.quantity hQuantity
    FROM
     tblHead
    INNER JOIN tblDetails ON (tblDetails.compno = tblDetails.compno AND tblDetails.id_num = tblHead.id_num)
    WHERE tblHead.compno = CompNo AND tblHead.id_num = WorkID;
    ORDER BY tblDetails.code
  );

As stated here:

..... not all clauses and options of
the SELECT statement are available for
you to use in a query within an
INSERT statement. The following SELECT
clauses and options are not supported
by Informix in an INSERT statement:

FIRST and INTO TEMP

ORDER BY and UNION

so ORDER BY is not supported in the INSERT command in Informix.

I don't have something to test right now, but you could try something like this, as a workaround:

INSERT INTO Items
SELECT code, dQuantity, hQuantity
  FROM (
    SELECT 
     tblDetails.code, 
     tblDetails.quantity dQuantity,
     tblHead.quantity hQuantity
    FROM
     tblHead
    INNER JOIN tblDetails ON (tblDetails.compno = tblDetails.compno AND tblDetails.id_num = tblHead.id_num)
    WHERE tblHead.compno = CompNo AND tblHead.id_num = WorkID;
    ORDER BY tblDetails.code
  );
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文