OraFAQ Forum: SQL & PL/SQL » Filter Data for uniqueness

Home » SQL & PL/SQL » SQL & PL/SQL » Filter Data for uniqueness

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Filter Data for uniqueness [message #421400]

Tue, 08 September 2009 02:24

hiteshkanjaria
Messages: 8
Registered: September 2008

Junior Member

Hi,

I ahve two table called TAB1 and TAB2.
TAB1 has data:
T11 T12 T13
01 IN HN
02 IN HN
03 IN HN
04 US EU
05 US EU
06 UK EK

TAB2 has data:
T21 T22 T23
CO 'IN 0' A0
CO 'IN 1' A1
CO 'IN 2' A2
CO 'IN 3' A3
CO 'IT 0' I0
CO 'IT 1' I1
CO 'IT 2' I2
CO 'US 0' S0
CO 'US 1' S1
CO 'UK 3' S3

Now I want to write a query in which I am comparing T12 and first 2 character of T22 column and retrieving value of T23. So if last character of T22 column is 3 then retrive that value of T23 else look for value 2 and then for 1 and 0.
But I wrote a query using DECODE which retrieves all the 4 values of T23 for every record from TAB1 with T12=IN.
Can you suggest some idea so i can get only single value if exist.

[Updated on: Tue, 08 September 2009 02:27]

Report message to a moderator

Re: Filter Data for uniqueness [message #421403 is a reply to message #421400]

Tue, 08 September 2009 02:32

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Report message to a moderator

Re: Filter Data for uniqueness [message #421408 is a reply to message #421403]

Tue, 08 September 2009 02:52

sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai

Member

Explain it in some more detailed manner 
SUBSTR within in DECODE will help u i guess

[Updated on: Tue, 08 September 2009 02:52]

Report message to a moderator

Re: Filter Data for uniqueness [message #421409 is a reply to message #421408]

Tue, 08 September 2009 02:54

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Who is this "u" person you keep talking about?

Report message to a moderator

Re: Filter Data for uniqueness [message #421412 is a reply to message #421408]

Tue, 08 September 2009 03:18

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

@sen_sam86
Welcome to the forum. It is really great that you are trying to help, unfortunately, at the moment, you are throwing out random guesses as to how to solve an issue, which is currently unclear. Wait for the test case to get posted and actually try to test your assumptions before answering.
Regards

Report message to a moderator

Re: Filter Data for uniqueness [message #421470 is a reply to message #421400]

Tue, 08 September 2009 08:43

Bill B
Messages: 1971
Registered: December 2004

Senior Member

also what ThomasG meant is please do not use cell phone abbreviations. Many of the people on this forum are non english speakers and they do not know what you mean when you say "u"

Report message to a moderator

Previous Topic:	null and empty string not being the same in object?
Next Topic:	passing FILE ID in PL SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 06:20:49 CST 2025