sql statement error when trying to substract

[evr72]

hello,

I have the SQL statement below, I am tryig to substract the value of the following fields INCHRBRKTOT - TOTHRS

This is my sql statement

SELECT
DAVLOGIC.MOROUTX4.DPTNO,
COUNT(DAVLOGIC.MOMASTX4.ORDNO) AS COUNTORDNO,
SUM(DAVLOGIC.MOMASTX4.HRREM) AS SUMHRREM,
DAVLOGIC.SCHHDCNT.HRSCHOVR,
(DAVLOGIC.SCHHDCNT.HRSCHOVR * COUNT(DAVLOGIC.MOMASTX4.ORDNO))+ SUM(DAVLOGIC.MOMASTX4.HRREM) AS TOTHRS,
(((MOND*WEEK1DYS*WEEK1HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM) AS INCHRBRK1,
(((TUESD * WEEK2DYS * WEEK2HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM) AS INCHRBRK2,
(((WEDN*
WEEK3DYS*
WEEK3HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM) AS INCHRBRK3,
(((THURSD*
WEEK4DYS*
WEEK4HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM) AS INCHRBRK4,
((((FRID*WEEK5DYS*WEEK5HPD)+(SATURD*WEEK5DYS*WEEK5HPD))*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM) AS INCHRBRK5,
((((MOND*WEEK1DYS*WEEK1HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((TUESD * WEEK2DYS * WEEK2HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((WEDN*
WEEK3DYS*
WEEK3HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((THURSD*
WEEK4DYS*
WEEK4HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
((((FRID*WEEK5DYS*WEEK5HPD)+(SATURD*WEEK5DYS*WEEK5HPD))*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)) AS INCHRBRKTOT

FROM DAVLOGIC.MOMASTX4
LEFT OUTER JOIN DAVLOGIC.MOROUTX4 ON DAVLOGIC.MOMASTX4.ORDNO = DAVLOGIC.MOROUTX4.ORDNO
LEFT OUTER JOIN DAVLOGIC.SCHHDCNT ON DAVLOGIC.MOROUTX4.DPTNO = DAVLOGIC.SCHHDCNT.DPTNUM
LEFT OUTER JOIN DAVLOGIC.SCHWKSPLT ON DAVLOGIC.SCHHDCNT.DPTNUM = DAVLOGIC.SCHWKSPLT.DPTNUM
WHERE (DAVLOGIC.MOROUTX4.DPTNO <>'')
GROUP BY DAVLOGIC.MOROUTX4.DPTNO, DAVLOGIC.SCHHDCNT.HRSCHOVR, (((MOND*WEEK1DYS*WEEK1HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM), (((TUESD * WEEK2DYS * WEEK2HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM), (((WEDN*
WEEK3DYS*
WEEK3HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM), (((THURSD*
WEEK4DYS*
WEEK4HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM), ((((FRID*WEEK5DYS*WEEK5HPD)+(SATURD*WEEK5DYS*WEEK5HPD))*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM), ((((MOND*WEEK1DYS*WEEK1HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((TUESD * WEEK2DYS * WEEK2HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((WEDN*
WEEK3DYS*
WEEK3HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((THURSD*
WEEK4DYS*
WEEK4HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
((((FRID*WEEK5DYS*WEEK5HPD)+(SATURD*WEEK5DYS*WEEK5HPD))*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM))
ORDER BY DAVLOGIC.MOROUTX4.DPTNO

This is what I am adding

(((((MOND*WEEK1DYS*WEEK1HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((TUESD * WEEK2DYS * WEEK2HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((WEDN*WEEK3DYS*WEEK3HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
(((THURSD*WEEK4DYS*WEEK4HPD)*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)+
((((FRID*WEEK5DYS*WEEK5HPD)+(SATURD*WEEK5DYS*WEEK5HPD))*DPTEFF) *DAVLOGIC.SCHWKSPLT.BRKTM)) - 
(DAVLOGIC.SCHHDCNT.HRSCHOVR * COUNT(DAVLOGIC.MOMASTX4.ORDNO))+ SUM(DAVLOGIC.MOMASTX4.HRREM))

I get the following error

SQL Error State:HY000, Native error Code: FFFFFF86, ODBC Error: IBM iSeries Access ODBC driver DB2 UDB SQL0122 - Column MOND or excression in select list not valid

not if I add each one of the above statements it woks but if I try to subtract I get the error

Any help is much appreciated.

Thanks in advance

 

[fredericofonseca]

I think that in this case you need to add that subtract to the group by also, not just the select

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[evr72]

when I group it i get SQL012- Use of finction COUONT not valid