Solved

Distinct values from using Substring

Posted on 2014-11-24
2
138 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
2 Comments
 
LVL 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle report printing 2 pages in one page 2 58
Problems using Provider=OraOLEDB.Oracle via VBScript/Classic ASP 5 46
oracle 11g 23 50
help on oracle query 5 33
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now