Need to select Rows having one coloumn with duplicate entry.

krunalbafna · Active User Joined: 18 Jan 2010 Posts: 143 Location: Pune

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..

guptae · Posted: Tue Mar 02, 2010 6:34 pm

Hello Krunal,

Would you please rephrase ur problem . Also provide sample input and output

krunalbafna · Active User Joined: 18 Jan 2010 Posts: 143 Location: Pune

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

guptae · Posted: Tue Mar 02, 2010 6:45 pm

Krunal,

Again not clear for 098765432 order of CODEX is C , L & D but for 837465212 its L,C,D.

krunalbafna · Active User Joined: 18 Jan 2010 Posts: 143 Location: Pune

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

krunalbafna · Active User Joined: 18 Jan 2010 Posts: 143 Location: Pune

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

Any possiblity available?

Waiting for reply

Srihari Gonugunta · Posted: Tue Mar 02, 2010 8:40 pm

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';

enrico-sorichetti · Posted: Tue Mar 02, 2010 8:50 pm

krunalbafna · Active User Joined: 18 Jan 2010 Posts: 143 Location: Pune

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

krunalbafna · Active User Joined: 18 Jan 2010 Posts: 143 Location: Pune

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.

krunalbafna · Active User Joined: 18 Jan 2010 Posts: 143 Location: Pune

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

Srihari Gonugunta · Posted: Tue Mar 02, 2010 9:21 pm

Krunal,
You can try this.