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 





IMPORTANT NOTICE
 
This message is confidential, intended only for the named recipient/s, and 
may contain information that is exempt from disclosure under applicable 
law or privilege.  If you are not the intended recipient/s, you are hereby 
on notice that the distribution or copying of this message is strictly 
prohibited.  If you receive this message in error, or are not the named 
recipient/s, please notify C.P. Morgan Communities, L.P. at the E-mail 
address above, delete this E-mail from your computer, and destroy any 
copies in any form.  Receipt by anyone other than the intended named 
recipient/s is not a waiver of any attorney-client, work-product, or other 
applicable privilege.
 
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail has been sent by a company of Bertram Group Ltd, whose registered 
office is 1 Broadland Business Park, Norwich, NR7 0WF. 
This message, and any attachments, are intended solely for the addressee and 
may contain privileged or confidential information.  If you are not the 
intended recipient, any disclosure, copying, distribution or any action taken 
or omitted to be taken in reliance on it, is prohibited and may be unlawful.  
If you believe that you have received this email in error, please contact the 
sender immediately. Opinions, conclusions and statements of intent in this 
e-mail are those of the sender and will not bind a Bertram Group Ltd company 
unless confirmed in writing by a director independently of this message. 
Although we have taken steps to ensure that this email and any attachments are 
free from any virus, we advise that in keeping with good computing practice the 
recipient should ensure they are actually virus free.