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
 

 

Retrieving unique occurance

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

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Thu Aug 08, 2013 7:57 pm    Post subject: Retrieving unique occurance
Reply with quote

Hi All ,

Greetings!!

I want to design a query where a unique row can be retrieved with varying inputs in where clause for example.

A Table is there with its name as Name_Element. Following are the columns.

Identifier - Nme_Element
1 - TEST
2 - TEST
3 - TEST
1 - TEST2
4 - TEST2
1 - TEST3
3 - TEST3
4 - TEST3

From the above values , i want to retrieve a unique identifier which is common for TEST,TEST2 and TEST3 which is 1 as per the above values.

Currently I am doing this with the help of cobol . I am just curious to know if this can be done via DB2 also.

Appreciate yor suggestion on this.

Regards,
Shweta.
Back to top
View user's profile Send private message

chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Aug 08, 2013 8:19 pm    Post subject:
Reply with quote

Hi,

Assuming your table will have only 3 Nme_Element
Try below query.this is untested
Code:

SELECT Identifier,COUNT(Nme_Element )
FROM TABLE
GROUBY BY Identifier
HAVING COUNT(Nme_Element ) = 3
Back to top
View user's profile Send private message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Thu Aug 08, 2013 8:33 pm    Post subject: Reply to: Retrieving unique occurance
Reply with quote

Hi Chandan ,

Can you pls explain how in the query it is extracting the common identifier between the nme_elements.

It would be helpful if you can provide me a slighlty tuned query.

Regards,
Shweta.
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: Thu Aug 08, 2013 9:15 pm    Post subject:
Reply with quote

Hello,

What does "slightly tuned" mean to you? I do not understand the question.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Thu Aug 08, 2013 10:19 pm    Post subject:
Reply with quote

Hi Shweta,
Does this query giving you desired results? Or you facing any issues?
I am just taking those identifiers which has three occurrences..

I am also on same boat as Dick icon_biggrin.gif what you mean by slightly tuned query?

Regards,
Chandan
Back to top
View user's profile Send private message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Fri Aug 09, 2013 12:27 am    Post subject: Reply to: Retrieving unique occurance
Reply with quote

Hi Chandan,

By tuned I meant if you can provide me the query using the example mentioned above. I am not able to understand how this query would work.

Hence could not execute it.

Regards,
Shweta.
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: Fri Aug 09, 2013 1:37 am    Post subject:
Reply with quote

Hello,

What you want is not "tuning".

To learn about the given code, suggest you run it in your test environment with a small number of rows to be processed.
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Mon Aug 12, 2013 12:12 pm    Post subject: Reply to: Retrieving unique occurance
Reply with quote

Hi Shweta,

Have you tried executing this query? What problems your arefacing with this query?

Regards,
Chandan
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 Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Unique sort situation.... dbecker SYNCSORT 6 Wed Apr 27, 2016 8:53 pm
No new posts Unique Record Count anandhan87 DFSORT/ICETOOL 1 Tue Feb 09, 2016 4:06 pm
No new posts unique 8 character token Pedro CLIST & REXX 9 Fri May 08, 2015 10:59 pm
No new posts Count unique values in a column based... Deepakgoyal2005 JCL & VSAM 4 Fri Jul 25, 2014 5:49 pm


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