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

Retrieving unique occurance


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 275
Location: Mumbai

PostPosted: Thu Aug 08, 2013 8:19 pm
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
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

Moderator Emeritus


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

PostPosted: Thu Aug 08, 2013 9:15 pm
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: 275
Location: Mumbai

PostPosted: Thu Aug 08, 2013 10:19 pm
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
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

Moderator Emeritus


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

PostPosted: Fri Aug 09, 2013 1:37 am
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: 275
Location: Mumbai

PostPosted: Mon Aug 12, 2013 12:12 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Can we Insert duplicates in Primary U... DB2 2
No new posts SEPARATE UNIQUE and NON-UNIQUE in SEP... SYNCSORT 6
No new posts Retrieving IMS message processing reg... CLIST & REXX 7
No new posts Unique IMS DB identifier IMS DB/DC 1
No new posts retrieving envelope information out o... TSO/ISPF 6
Search our Forums:

Back to Top