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
 

 

Fetching unique records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Fetching unique records
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    Post subject:
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

Site Director


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

PostPosted: Mon Apr 21, 2008 8:47 pm    Post subject:
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    Post subject:
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: 785
Location: Chennai, India

PostPosted: Mon Apr 21, 2008 9:18 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Mon Apr 21, 2008 10:25 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Mon Apr 21, 2008 11:04 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


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