Home » SQL & PL/SQL » SQL & PL/SQL » Filter Data for uniqueness
Filter Data for uniqueness [message #421400] Tue, 08 September 2009 02:24 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Filter Data for uniqueness [message #421408 is a reply to message #421403] Tue, 08 September 2009 02:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Who is this "u" person you keep talking about?
Re: Filter Data for uniqueness [message #421412 is a reply to message #421408] Tue, 08 September 2009 03:18 Go to previous messageGo to next message
pablolee
Messages: 2834
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

Re: Filter Data for uniqueness [message #421470 is a reply to message #421400] Tue, 08 September 2009 08:43 Go to previous message
Bill B
Messages: 1482
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"
Previous Topic: null and empty string not being the same in object?
Next Topic: passing FILE ID in PL SQL
Goto Forum:
  


Current Time: Sun Dec 04 02:57:00 CST 2016

Total time taken to generate the page: 0.15510 seconds