Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem with SQL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Tue Mar 11, 2008 11:18 pm    Post subject: Problem with SQL
Reply with quote

Hi,
Iam trying to execute the following SQL:

Code:
SELECT SUBSTR('45',1,2) DIVISION,                                     
       SUBSTR(D.DEPT,1,7) DEPARTMENT,                     
       COUNT(*) NUMBER_OF_EMPLOYEES                       
           FROM BUSINESS A,                           
                OFFICE   B                             
         WHERE A.JOIN_DATE    = B.JOIN_DATE
           AND A.SUPERVISOR  = B.SUPERVISOR
           AND A.GENDER   = 'MALE'                         
           AND B.EMPLOYEE_ID IN                                   
           ( SELECT EMPL_ID FROM DEPARTMENT_INFO C,         
                                 BUSINESS_INFO   D         
             WHERE                                                   
                 C.LAST_NAME = D.LAST_NAME               
             AND C.FIRST_NAME= D.FIRST_NAME
             AND D.DIVISION  = 45                               
             AND D.DEPT   IN ('PPMCG11','PPMCG13','PPMCG15',
                                       'PPMCP11','PPMCT08','PPMCT09')
             GROUP BY D.DEPT);                             


I do understand that "SUBSTR(D.DEPT,1,7) DEPARTMENT" should not be a part of the select statement since the associated table is not in the FROM statement but when I add the associated table in the FROM list, it still fails.

I want my output to look like:

Code:

+--------+-----------+-------------------+
|DIVISION|DEPARTMENT |NUMBER_OF_EMPLOYEES|
+--------+-----------+-------------------+
|45      |PPMCG15    |             5     |
|45      |PPMCP11    |            10     |
|45      |PPMCT09    |             5     |
+--------+-----------+-------------------+


Can someone please help me out with this ?

Thanks in advance !
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Mar 11, 2008 11:21 pm    Post subject:
Reply with quote

It would help a lot if you would show what you are getting along with any error messages!
Back to top
View user's profile Send private message
tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Wed Mar 12, 2008 12:15 am    Post subject: Reply to: Problem with SQL
Reply with quote

The error message that am getting is:

Code:
DSNT408I SQLCODE = -206, ERROR:  D.DEPT IS NOT A COLUMN OF AN     
         INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM 
         CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER   
DSNT418I SQLSTATE   = 42703 SQLSTATE RETURN CODE                           
DSNT415I SQLERRP    = DSNXORSO SQL PROCEDURE DETECTING ERROR               
DSNT416I SQLERRD    = -225 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION       
DSNT416I SQLERRD    = X'FFFFFF1F'  X'00000000'  X'00000000'  X'FFFFFFFF'   
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION               
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Mar 12, 2008 12:22 am    Post subject:
Reply with quote

This might help.

FROM BUSINESS A,
OFFICE B ,
BUSINESS_INFO D
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us