Solved

Distinct values from using Substring

Posted on 2014-11-24
2
145 Views
Last Modified: 2014-11-25
Hello,

Can someone help me get distinct values? I attached sample data, expected results and then the rules for each column.

Thank you
DIM-OWNER-11-24-14-2.xls
0
Comment
Question by:angel7170
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40463062
SELECT owner_nm,
         MAX(addr_1) addr_1,
         MAX(addr_2) addr_2,
         py_city,
         py_zip_cd
    FROM (SELECT py_nam_1 || py_nam_2 || py_nam_3 owner_nm,
                 CASE WHEN vt_text_type LIKE 'AI%' THEN py_addr_1 || vt_text END addr_1,
                 CASE WHEN vt_text_type LIKE 'AS%' THEN py_addr_2 || vt_text END addr_2,
                 py_city,
                 py_zip_cd
            FROM yourtable)
GROUP BY owner_nm, py_city, py_zip_cd
0
 

Author Closing Comment

by:angel7170
ID: 40464484
thank you very much. It worked as expected
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question