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
 

 

Need to select Rows having one coloumn with duplicate entry.

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

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Tue Mar 02, 2010 6:32 pm    Post subject: Need to select Rows having one coloumn with duplicate entry.
Reply with quote

Hi following is my output requirement:

i have table with following coloumn names: CIX-Number(9),CODEX(Char-1,CODENo(Number-12).
of this three CIX may have same entry.
I need to have an output file showing all three coloums for same CIX and CODEX shld be 'C','L','D'. Meaning For CIN with mulitiple same entry it shld satify above three CODEX.

Hope I am clear.
may i get some help..
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Mar 02, 2010 6:34 pm    Post subject:
Reply with quote

Hello Krunal,

Would you please rephrase ur problem . Also provide sample input and output
Back to top
View user's profile Send private message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Tue Mar 02, 2010 6:37 pm    Post subject:
Reply with quote

Hi,
I need to read table A: (CINX,CODEX,CODENO)
My outtput shld be in following manner
CINX Codex Codeno
098765432 C 123456
098765432 L 87654322
098765432 D 345679059606
837465212 L 23894857
837465212 C 654321
837465212 D 586987266354

hope this provide u some idea.

waiting for your reply
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Mar 02, 2010 6:45 pm    Post subject:
Reply with quote

Krunal,

Again not clear for 098765432 order of CODEX is C , L & D but for 837465212 its L,C,D.
Back to top
View user's profile Send private message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Tue Mar 02, 2010 6:52 pm    Post subject:
Reply with quote

Hi guptae,

The above mentioned can be in any order.
We need a Customer which using all three products with code L,C,D..

A customer will have same CIX number but different CODEX number.

There will be customer who can use only 2 of 3 mentioned products but we need only those using all three products.

Hope i am clear...
U can revert.

Waiting for your response
Back to top
View user's profile Send private message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Tue Mar 02, 2010 8:37 pm    Post subject:
Reply with quote

I tried lots of query but not able to get desired output.

Any possiblity available?

Waiting for reply icon_eek.gif
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Mar 02, 2010 8:40 pm    Post subject:
Reply with quote

Krunal,
You can try the following. This will give list of customers using all codex.
If you want all the details of the customer. You can use this as subquery in the where clause for cinx.

Select a.cinx from your_table A, your_table B, your_table C
Where A.CINX = B.CINX and
B.CINX = C.CINX and
A.Codex = 'C' and
B.Codex = 'L' and
C.Codex = 'D';
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Tue Mar 02, 2010 8:50 pm    Post subject: Reply to: Need to select Rows having one coloumn with duplic
Reply with quote

Quote:
I tried lots of query but not able to get desired output.
Any possiblity available?
Waiting for reply icon_eek.gif

You might be shocked as much as You want...

your previous post ( with the same wording ) was deleted because
to solicit for answers on forums is considered ... bad manners

are You aware that people reply on their own time and at no charge ?
if You feel that Your issue deserves more effort than we can provide
Your organization should plan on getting a paid for consultancy
Back to top
View user's profile Send private message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Tue Mar 02, 2010 9:17 pm    Post subject:
Reply with quote

Hi Srihari Gonugunta,

I also need codex and codeno in my output..
If i try as follows:
Select a.cinx ,a.codex,b.codex,c.codex,a.codeno from your_table A, your_table B, your_table C
Where A.CINX = B.CINX and
B.CINX = C.CINX and
A.Codex = 'C' and
B.Codex = 'L' and
C.Codex = 'Z';
it is displaying output as follows which is repetative,
-------- ------- ------- ------- -------------
3618896 C L Z 7762003877
3618896 C L Z 7762003877
3618896 C L Z 7762003877
3618896 C L Z 7762003877
3618896 C L Z 9061002076
3618896 C L Z 9061002076
3618896 C L Z 9061002076
3618896 C L Z 9061002076
3618896 C L Z 7762003877
3618896 C L Z 7762003877
3618896 C L Z 7762003877
3618896 C L Z 7762003877
3618896 C L Z 9061002076
3618896 C L Z 9061002076
3618896 C L Z 9061002076
3618896 C L Z 9061002076

it shld be in above format

CINX Codex Codeno
098765432 C 123456
098765432 L 87654322
098765432 Z 345679059606
837465212 L 23894857
837465212 C 654321
837465212 Z 586987266354

May you guide...where i am going wrong
Back to top
View user's profile Send private message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Tue Mar 02, 2010 9:19 pm    Post subject:
Reply with quote

Output-1 mentioned above is the sample of my query to the table...

Output-2 is the sample output required for my calculation..

Hope i am precise.
Back to top
View user's profile Send private message
krunalbafna
Warnings : 1

Active User


Joined: 18 Jan 2010
Posts: 143
Location: Pune

PostPosted: Tue Mar 02, 2010 9:21 pm    Post subject:
Reply with quote

Hi enrico-sorichetti,
I used shock as my emotions. It was not intended to solicit for answers. I am aware of the usefulness of this site and the people giving there due time.

Greatly respect all the members. If by my means i have hurt someone i am sorry
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Mar 02, 2010 9:21 pm    Post subject:
Reply with quote

Krunal,
You can try this.

Code:
Select * from your_table where cinx in
(Select a.cinx from your_table A, your_table B, your_table C
Where A.CINX = B.CINX and
B.CINX = C.CINX and
A.Codex = 'C' and
B.Codex = 'L' and
C.Codex = 'D');
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Automation of data entry using Rexx i... zingy.rajeev CLIST & REXX 4 Mon Aug 22, 2016 4:13 pm


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