IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Fetching unique records


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Mon Apr 21, 2008 8:15 pm
Reply with quote

Hi,

Please find the details below. Thanks for taking a look.

TABLE:
Col1 Col2 Col3
1 a i
3 c i
2 b i
2 b ii
1 a ii
3 c i
4 b iii
5 d i

OUTPUT NEEDED:
Col1 Col2 Col3
1 a i
1 a ii
2 b i
2 b ii

REJECTED RECORDS:
Col1 Col2 Col3
3 c i
3 c i
4 b iii
5 d i

Requirement:
I need unique records with key being col1 and col2 and only the records which have more than one distinct value in col3.

Note: GROUP BY doesnt work with WHERE.

Thanks,
Soundarr
Back to top
View user's profile Send private message
pavankumarhassaji

New User


Joined: 20 Aug 2007
Posts: 11
Location: Canada

PostPosted: Mon Apr 21, 2008 8:32 pm
Reply with quote

Hi soundarr,

As per your question, SELECT DISTINCT Col1, Col2, Col3 will work. Try it.

Thanks,
Pavan Hassaji.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Apr 21, 2008 8:47 pm
Reply with quote

Hello,

Quote:
As per your question, SELECT DISTINCT Col1, Col2, Col3 will work. Try it.
Did you try this before posting? This query does not provide the required output.

Quote:
GROUP BY doesnt work with WHERE.
I believe it does when coded correctly. . . It wouldn't solve this requirement, though.

For your requirement to find rows that only have multiple col3 values for the same col1/2 values, more than a simple query may be needed.
Back to top
View user's profile Send private message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Mon Apr 21, 2008 9:15 pm
Reply with quote

Hi scherrer,

If GROUP BY works with WHERE, i can find a solution. But I get an error saying "DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD WHERE".

Can you please guide me here or provide me any link.

Regards,
Soundarr
Back to top
View user's profile Send private message
Gnanas N

Active Member


Joined: 06 Sep 2007
Posts: 792
Location: Chennai, India

PostPosted: Mon Apr 21, 2008 9:18 pm
Reply with quote

Could you please show your query?
Back to top
View user's profile Send private message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Mon Apr 21, 2008 9:23 pm
Reply with quote

SELECT A,B,C FROM TB132
GROUP BY A,B
HAVING COUNT(*)>1
WHERE COUNT(DISTINCT C)>1;
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Apr 21, 2008 9:46 pm
Reply with quote

try this one ..

Code:


SELECT,A.COL1,A.COL2,A.COL3 FROM TB132 A
,TB132 B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3
AND EXISTS ( SELECT COL1 FROM TB132 GROUP BY COL1 HAVING
COUNT(COL1) > 1)
ORDER BY A.COL1,A.COL2,A.COL3;
Back to top
View user's profile Send private message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Mon Apr 21, 2008 9:49 pm
Reply with quote

GROUP BY works with WHERE. Sorry for the wrong Note.

Apologies,
Soundarr
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Apr 21, 2008 10:25 pm
Reply with quote

Hello,

Quote:
GROUP BY works with WHERE. Sorry for the wrong Note.
Not a problem icon_smile.gif

Do you have a solution for your requirement or is something else needed?
Back to top
View user's profile Send private message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Mon Apr 21, 2008 10:30 pm
Reply with quote

No solution yet...:-)

WHERE works with GROUP BY but not "WHERE COUNT(DISTINCT C)>1".

Regards,
Soundarr
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Mon Apr 21, 2008 11:04 pm
Reply with quote

Hello,

Quote:
WHERE works with GROUP BY but not "WHERE COUNT(DISTINCT C)>1".
Yup.

Did you try the code ashimer posted? I believe it will be close, but does not take into consideration the col2 part of the requirement.
Back to top
View user's profile Send private message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Tue Apr 22, 2008 6:17 pm
Reply with quote

Hi Ashimer,

Thanks much!

But even this works:

Code:
SELECT A.COL1,A.COL2,A.COL3 FROM TB132 A
,TB132 B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3
;


Correct me if this isnt as good as the first version.

Thanks Scherrer!

I also would like to know if there is any keyword(within WHERE clause) to find if a field contains a given value. For example, i want to find if any record in the table has value "1" for col1. I dont want to use EXISTS..:-)

Regards,
Soundarr
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 22, 2008 6:20 pm
Reply with quote

hi soundarr,

use LIKE '%1%' in where clause or else after retrieving the data use INPECT verb available in cobol ..


thanks,
ashimer
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 22, 2008 6:29 pm
Reply with quote

soundarr you are right .. even without exists it will give the desired o/p but make sure you include DISTINCT in your select ..



Code:


SELECT DISTINCT A.COL1,A.COL2,A.COL3 FROM TB132 A
,TB132 B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3
;

Back to top
View user's profile Send private message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Tue Apr 22, 2008 6:46 pm
Reply with quote

Hi Ashimer,

Even without distinct, i am seeing the desired output. They are all unique. Pls correct if i am incorrect.

Regarding my second query, I wasnt clear enough. I know abt LIKE but i dint want that. And i donno how to explain that...:-)

Thanks a lot!
Soundarr
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Apr 22, 2008 7:07 pm
Reply with quote

Hi Soundarr,

try adding one more row with values say ..

Col1 Col2 Col3
-----------------
2 b iii

this time you wont get the desired o/p without DISTINCT ...

thanks,
ashimer
Back to top
View user's profile Send private message
soundarr

New User


Joined: 17 Jan 2008
Posts: 25
Location: Chennai

PostPosted: Wed Apr 23, 2008 5:15 pm
Reply with quote

Hi Ashimer,

Distinct matters! I actually had another predicate in the WHERE clause which made me get the desired result. Thanks a lot!

Regards,
Soundarr
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top