Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Join and Count

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

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Sat Apr 05, 2008 12:52 am    Post subject: Join and Count
Reply with quote

Hi,
i have 2 tables and i am facing problem clubbing them

table1
Code:
EMPNO        EMP_DETAILS
1111         AAAAA
2222         BBBBB
3333         CCCC
4444         DDDD
5555         EEEEE
6666         FFFFF

table2
Code:
PROJECT     PROJECT_DET   EMPNO GRADE_GIVEN
P1          PAAAAAAAA     1111           A
P1          PAAAAAAAA     3333           B
P1          PAAAAAAAA     4444           X
P1          PAAAAAAAA     2222           X
P2          PBBBBBBBB     2222           A
P2          PBBBBBBBB     1111           A
P2          PBBBBBBBB     3333           B

I need the employees details along with the project details where projects doesnt have a grade X

for eg:

Code:
BBBB P2 PBBBBBBBB 2222 A
AAAA P2 PBBBBBBBB 1111 A
CCCC P2 PBBBBBBBB 3333 B
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Apr 05, 2008 1:38 am    Post subject:
Reply with quote

Hello,

Why has the posted output been selected? It does not appear to be complete? Why is P1 AAAA not in the output?

Please review your inputs and output and re-post the "rules" you need to implement.

Also, please use the "Code" tag at the top of the reply panel to align your post - it makes it much more readable.

I've "Coded" your data and deleted the 'extra' posts in your topic.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Sat Apr 05, 2008 2:11 am    Post subject:
Reply with quote

My read is that the P1 project does have a grade X........I think that is why the count in the title.....
Back to top
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Sat Apr 05, 2008 7:55 am    Post subject:
Reply with quote

Hi,
i have 2 tables and i am facing problem clubbing them

table1
Code:

EMPNO        EMP_DETAILS
1111         AAAA
2222         BBBB
3333         CCCC
4444         DDDD
5555         EEEE
6666         FFFF 


table2

Code:

PROJECT     PROJECT_DET   EMPNO GRADE_GIVEN
P1          PAAAAAAAA     1111           A
P1          PAAAAAAAA     3333           B
P1          PAAAAAAAA     4444           X
P1          PAAAAAAAA     2222           X
P2          PBBBBBBBB     2222           A
P2          PBBBBBBBB     1111           A
P2          PBBBBBBBB     3333           B 


I need the employees details along with the project details where projects doesnt have a grade X .

for eg:

Code:
Code:

EMP_DETAILS  PROJECT         PROJECT_DET    EMPNO      GRADE_GIVEN
BBBB         P2              PBBBBBBBB      2222           A
AAAA         P2              PBBBBBBBB      1111           A
CCCC         P2              PBBBBBBBB      3333           B


i am not sure how we need to use COUNT to get the projects whichb doesnt have a grade X.
Back to top
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Sat Apr 05, 2008 8:35 am    Post subject:
Reply with quote

hi

Please note that Project P1 has given a grade X to one of EMP while P2 has grades A and B only.So,in my output,i have only P2 along with EMP_DETAILS

Regards
Back to top
View user's profile Send private message
Richa Jain

New User


Joined: 18 Mar 2008
Posts: 35
Location: Gurgaon

PostPosted: Sat Apr 05, 2008 9:40 am    Post subject:
Reply with quote

Code:

SELECT E.EMP_DETAILS, P.PROJECT , P.PROJECT_DET, E.EMPNO, P.GRADE_GIVEN FROM EMP_DETAILS E PROJECT P WHERE E.EMPNO = P.EMPNO AND PROJECT <> (SELECT PROJECT FROM PROJECT WHERE GRADE_GIVEN = X GROUP BY PROJECT)
Back to top
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Mon Apr 07, 2008 12:28 am    Post subject:
Reply with quote

Is it possible to use NOT EXISTS in my SQL query..Please guide
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2150
Location: At my coffee table

PostPosted: Mon Apr 07, 2008 12:32 am    Post subject:
Reply with quote

Unique wrote:
Is it possible to use NOT EXISTS in my SQL query..Please guide
What does your query look like with NOT EXISTS?
Back to top
View user's profile Send private message
Unique

New User


Joined: 10 Jul 2007
Posts: 24
Location: Blore

PostPosted: Mon Apr 07, 2008 12:44 am    Post subject:
Reply with quote

Quote:

SELECT E.EMP_DETAILS, P.PROJECT , P.PROJECT_DET, E.EMPNO, P.GRADE_GIVEN FROM EMP_DETAILS E PROJECT P WHERE E.EMPNO = P.EMPNO AND PROJECT <> (SELECT PROJECT FROM PROJECT WHERE GRADE_GIVEN = X GROUP BY PROJECT)


Is it possible to use "NOT EXISTS " in this
[/quote]
Back to top
View user's profile Send private message
Ajesh

New User


Joined: 03 Apr 2008
Posts: 6
Location: Chennai

PostPosted: Tue Apr 08, 2008 2:48 pm    Post subject: Query
Reply with quote

Please try this out.Hope this will solve.
SELECT T1.EMP_DETAILS,
T2.PROJECT,
T2.PROJECT_DET,
T1.EMPNO,
T2.GRADE_GIVEN
FROM EMP_DETAILS T1,
PROJECT T2
WHERE T1.EMPNO = T2.EMP_NO
AND T2.GRADE_GIVEN <> 'X'

We can use does not exist in your query.Also you can try
GRADE_GIVEN NOT IN ('X')

Thanks
Ajesh
Back to top
View user's profile Send private message
Ajesh

New User


Joined: 03 Apr 2008
Posts: 6
Location: Chennai

PostPosted: Tue Apr 08, 2008 2:50 pm    Post subject: Query
Reply with quote

Do you want the count of the result set.If so just reply back.I can help you in getting the count.

Thanks
Ajesh
Back to top
View user's profile Send private message
senthil_hari

New User


Joined: 27 Mar 2008
Posts: 7
Location: chennai

PostPosted: Wed Apr 09, 2008 4:08 pm    Post subject:
Reply with quote

hi ajesh,

pls tell me for getting the count of the result for your query.
Back to top
View user's profile Send private message
Ajesh

New User


Joined: 03 Apr 2008
Posts: 6
Location: Chennai

PostPosted: Wed Apr 09, 2008 4:14 pm    Post subject: Query
Reply with quote

Hi Senthil,

This query fetches the result set count.

SELECT COUNT(*)
FROM
(SELECT T1.EMP_DETAILS,
T2.PROJECT,
T2.PROJECT_DET,
T1.EMPNO,
T2.GRADE_GIVEN
FROM EMP_DETAILS T1,
PROJECT T2
WHERE T1.EMPNO = T2.EMP_NO
AND T2.GRADE_GIVEN <> 'X' )A


Regards
Ajesh
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 How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us