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

Need to select Rows having one coloumn with duplicate entry.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1208
Location: Bangalore,India

PostPosted: Tue Mar 02, 2010 6:34 pm
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
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: 1208
Location: Bangalore,India

PostPosted: Tue Mar 02, 2010 6:45 pm
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
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
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
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

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Mar 02, 2010 8:50 pm
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Duplicate several members of/in one l... JCL & VSAM 7
Search our Forums:

Back to Top