×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

sql statement error when trying to substract

sql statement error when trying to substract

sql statement error when trying to substract

(OP)
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

CODE

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

CODE

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

CODE

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

RE: sql statement error when trying to substract

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close