RE: SQL Statement

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

Paul,

As has been mentioned previously, SELECT only works in an interactive SQL
session. If the results of the SELECT are to be returned as a result set,
he'll need to declare and open a cursor and then return the results.
Something like this:

DECLARE MY_RESULTS CURSOR FOR
SELECT xxxx               <-- SELECT statement goes here

OPEN MY_RESULTS;
SET RESULT SETS CURSOR MY_RESULTS;

Hope that helps!
Richard Casey



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of pnelson@xxxxxxxxxx
Sent: Tuesday, March 22, 2005 11:30 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL Statement


I believe that this is supposed to be returned as a result set and end up
in a report as part of a cost estimating system.


Paul Nelson
Arbor Solutions, Inc.
708-670-6978  Cell
pnelson@xxxxxxxxxx





Colin Williams <colin.williams@xxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/22/2005 10:20 AM
Please respond to Midrange Systems Technical Discussion

        To:     "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
        cc:
        Subject:        RE: SQL Statement


Paul,

Im no SQL stored procedure expert, but where is the output from the select
going.

I would expect to see either an the results to be written to a file, moved
into some host variables, or returned in a resultset.

Eg

CREATE PROCEDURE GetCusName()
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR FOR SELECT cusnam FROM customer ORDER BY cusnam;
OPEN c1;
SET RESULT SETS CURSOR c1;
END;

The docs are in the SQL Reference and

Stored Procedures and Triggers on DB2 Universal Database for iSeries
redbook

the SQL Reference

cheers
Colin.W

http://as400blog.blogspot.com

Extension   5800
Direct dial   0870 429 5800


-----Original Message-----
From: pnelson@xxxxxxxxxx [mailto:pnelson@xxxxxxxxxx]
Sent: 22 March 2005 15:46
To: midrange-l@xxxxxxxxxxxx
Subject: Fw: SQL Statement


Any SQL gurus out there? The statement below generates the following
message when the user tries to compile it as a stored procedure. I'm at a
loss

MSG ID  SEV  RECORD  TEXT
SQL0029  30      65  Position 10 INTO clause missing from embedded
statement.

TIA

Paul Nelson
Arbor Solutions, Inc.
708-670-6978  Cell
pnelson@xxxxxxxxxx

----- Forwarded by Paul Nelson/arbsol.com/US on 03/22/2005 09:45 AM -----


"SQLServer Admin" <SAdmin@xxxxxxxxxxxx>
03/22/2005 09:40 AM

        To:     <pnelson@xxxxxxxxxx>
        cc:
        Subject:        SQL Statement


Paul,
Here it is
SELECT DISTINCT HC.HCJOB2, Right(HC.HCUSYC,3)  TRADE, HC.HCITEM,
A2.A2DESC,HC.HCUVND
, C5.C5VRNM, HC.HCUDSC, L1.L1LDSC
, SUM(CASE WHEN HC.HCUCCD IN (6,10) THEN 1 ELSE
(CASE WHEN HU.HUUORQ IS NULL THEN HC.HCQNTY ELSE HC.HCQNTY * HU.HUUORQ
END) END) AS [SumofTotalQty]
, HC.HCUNPR, SUM(HC.HCUAMT) AS Amt, AZ.AZPRJM, HC.HCPLAN, HC.HCELEV,
AZ.AZTAXR, L1.L1NTAX, SUM(HC.HCUAMT * AZ.AZTAXR * 0.01) AS Tax ,
L1.L1DESC,
HC.HCUCCD, SUM((CASE WHEN HU.HUUORQ IS NULL THEN HC.HCQNTY
ELSE HC.HCQNTY * HU.HUUORQ END) * HC.HCUNPR) AS SumTotAmt
FROM CK1FLS.HCUSOL HC
INNER JOIN CK1FLS.A2JBMS A2 ON HC.HCJOB2 = A2.A2JOBN
INNER JOIN CK1FLS.AZPROJ AZ ON A2.A2PROJ = AZ.AZPRJM
LEFT JOIN .CK1FLS.HUUSIM HU ON
HC.HCUEXT = HU.HUUEXT
AND HC.HCSALN = HU.HUUSEQ
AND HC.HCUUNT = HU.HUUUNT
AND HC.HCUPRM = HU.HUUPRM
LEFT JOIN CK1FLS.L1ITEM L1 ON HC.HCITEM = L1.L1ITNO
A2.A2PROJ = EC.Project
 and HC.HCUUNT = EC.UNIT
 and HC.HCSALN = EC.SALN
 and HC.HCSWO = EC.SWO
 and HC.HCUEXT = EC.[OPTION]
 and HC.HCBCO = EC.BCO
 and HC.HCUSYC = EC.COSTCODE
 and HC.HCUCCD = EC.COSTTYPE
 and HC.HCLINE = EC.LINE
WHERE HC.HCJOB2 = JobNumber  AND HC.HCITEM <> ''
GROUP BY HC.HCJOB2, RIGHT(HC.HCUSYC,3), HC.HCITEM, A2.A2DESC,
, HC.HCUVND, C5.C5VRNM, HC.HCUDSC, L1.L1LDSC, HC.HCUNPR, AZ.AZPRJM
, HC.HCPLAN, HC.HCELEV, AZ.AZTAXR, L1.L1NTAX, L1.L1DESC, HC.HCUCCD,
EC.ZTDESC
ORDER BY HC.HCJOB2, TRADE, HC.HCITEM