Re: SQL Statement

Hi Paul,

Don't know if it's the problem or not, but it looks like you've got an extra
comma in the GROUP BY clause, between

A2.A2DESC,  , HC.HCUVND,

Seems like an odd error message for that though.

Peter Dow
Dow Software Services, Inc.
909 793-9050 voice
909 793-4480 fax
951 522-3214 cell


----- Original Message ----- 
From: <pnelson@xxxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, March 22, 2005 7:46 AM
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.
>
> 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



-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 3/21/2005