RE: SQL Statement

[pnelson@xxxxxxxxxx]

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 





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.

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