Cannot add an ORDER BY command to a stored procedure

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

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