Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Distinct values from using Substring

Posted on 2014-11-24
2
Medium Priority
?
146 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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 recover a database from a user managed backup

670 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