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!

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

Jobs

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!

Resources

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