If Mailing Address and PermAdr are same show one record else show both 2

[sxschech]

Would like to find out how I can show one row per person if the mailing address and the permanent address are the same, if they are different then show both.

[tt]
ID Name Addr Zip AdrType
123 Joe 34 Main St 92191 MA
123 Joe 34 Main St 92191 PR
234 Gil 65 West St 92192 MA
234 Gil 91 East Rd 94596 PR
598 May 83 First Ave 94596 PR
[/tt]

Output I typed a BO in the display below to indicate
that the address is the same for BOth. Not sure if
doing that is possible too?
[tt]
ID Name Addr Zip AdrType
123 Joe 34 Main St 92191 [red]BO[/red]
234 Gil 65 West St 92192 MA
234 Gil 91 East Rd 94596 PR
598 May 83 First Ave 94596 PR
[/tt]


Here is the SQL that someone had set up as part of a view, I extracted the releveant portion, since the actual view was designed to pick one record per person regardless.

SELECT entity_uid pidm,
                   address_type typ,
                   address_seq_no seqno,
                   street_line1 street1,
                   street_line2 street2,
                   street_line3 street3,
                   city,
                   county_code,
                   county_desc,
                   state_province state,
                   postal_code zip,
                   nation_desc nation,
                   ROW_NUMBER ()
                   OVER (PARTITION BY entity_uid
                         ORDER BY address_type, address_seq_no DESC)
                      AS rnum
              FROM mst_address
             WHERE address_type IN ('MA', 'PR')
                   AND address_status_ind IS NULL
                   AND SYSDATE BETWEEN NVL (address_start_date, SYSDATE - 1)
                                   AND NVL (address_end_date, SYSDATE + 1)

 

[SantaMufasa]

Sxschech,

There are probably several ways to give you the results you want, and here is one of them:

Select ID, Name, addr,zip, 'BO' AdrType
  From mst_address
 Group by ID, Name, addr,zip
Having count(*) > 1
union
Select ID, Name, addr,zip, AdrType
  From mst_address x
 where not exists (select 'whatever'
                     from mst_address
                    where ID||Name||addr||zip = x.ID||x.Name||x.addr||x.zip
                      and AdrType <> x. AdrType)
/

  ID NAME ADDR         ZIP   ADRTYP
---- ---- ------------ ----- ------
 123 Joe  34 Main St   92191 BO
 234 Gil  65 West St   92192 MA
 234 Gil  91 East Rd   94596 PR
 598 May  83 First Ave 94596 PR

4 rows selected.

Let us know of this resolves your need and if you have any questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Dagon]

Another option:

create table testaddr (id number, name varchar2(30), address1 varchar2(50), zip varchar2(30), address_type varchar2(2));

insert into testaddr values (123,    'Joe',     '34 Main St',   '92191', 'MA');
insert into testaddr values (123,    'Joe',     '34 Main St',   '92191', 'PR');
insert into testaddr values (123,    'Joe',     '34 Main St',   '92191', 'OT');
insert into testaddr values (234,    'Gil',     '65 West St',   '92192', 'MA');
insert into testaddr values (234,    'Gil',     '91 East Rd',   '94596', 'PR');
insert into testaddr values (598,    'May',     '83 First Ave', '94596', 'PR');

select id, name, address1, zip, case when rnk > 1 then 'B' else address_type end addr_type, rnk
from
(select id, name, address1, zip, address_type, rank() over (partition by id order by address1, zip) as rnk
from testaddr);

select id, name, address1, zip, case when prev_zip is not null then 'B' else address_type end address_type
from 
(select id, name, address1, zip, address_type, lead(zip) over (partition by id, address1, zip order by zip) next_zip,
lag(zip) over (partition by id, address1, zip order by zip) prev_zip
from testaddr)
where next_zip is null;

I've written it so it can cope with 2 or more duplicate addresses of different types.

 

[sxschech]

Thanks to you both. I got the queries to work. For some reason, I still have a big block when it comes to understanding and knowing how to use the exists and the over partition by stuff.

 

[SantaMufasa]

Sxschech,

You "big block" is very reasonable until you have a chance to learn how the concepts work. I'll pitch my explanations to you, and Dagon and others can add their pitches to mine, and you can have a chance to ask for clarifications.

As for the "EXISTS" and "NOT EXISTS" conditional operators: [ul][li]Think of the EXISTS operator as equivalent to asking "Is it TRUE that at least one row results from the query that follows?"[/li][li]Think of the NOT EXISTS operator as equivalent to asking "Is it TRUE that no rows result from the query that follows?"[/li][/ul]Let's now analyze my code from above, one SELECT at a time:

Select ID, Name, addr,zip, 'BO' AdrType
  From mst_address
 Group by ID, Name, addr,zip
Having count(*) > 1...

This code executes the following processing in this order:[ul][li](From mst_address): Read the rows from the mst_address table.[/li][li](Group by ID, Name, Addr, ZIP): Match all of the rows that have the same ID, Name, Addr, and ZIP. (Also, in a "clipboard", keep track of the COUNT of the number of rows that match in each separate GROUP.)[/li][li](Having count(*) >1): Produce a result set that includes one representative row for each matching group where the number of rows for the group exceeds 1. (In your data's cases, if there are more than one match, it means that it meets your criterion of "one address is appropriate for 'BOTH' the mailing address and the permanent address".)[/li][li]Display the ID, Name, addr,zip, and the literal 'BO' (aliased as 'AdrType') for each of the representative rows where the 'PA' and 'MA' addresses match for a person.[/li][/ul]The above code results in just the representative 'BO' rows.



Now the second set of SELECTS:

Select ID, Name, addr,zip, AdrType
  From mst_address x
 where not exists (select 'whatever'
                     from mst_address
                    where ID||Name||addr||zip = x.ID||x.Name||x.addr||x.zip
                      and AdrType <> x. AdrType)

This code executes the following processing in this order:[ul][li](From mst_address): Read the rows from the mst_address table, and alias the table for this query as 'x'.[/li][li](where not exists): Return each row from the outer SELECT if the following query does not return any rows:[/li][ul][li]Again, Read the rows from the mst_address table[/li][li]Concatenate together the ID, Name, addr, and ZIP expressions from the inner SELECT, and Concatenate together the ID, Name, addr, and ZIP expressions from the outer SELECT (X) and compare those two concatenations for equality.[/li][li]Compare the inner AdrType to the outer AdrType for inequality (i.e., are the address types different?)[/li][li]If either of the above, compound conditions are FALSE, "NOT EXISTS" is TRUE, meaning that your "BOTH" situation is FALSE for the current, outer SELECTed row. In this case, include the current, outer-SELECTed row in the result set, along with its AdrType value.[/li][/ul][li](...UNION...): Combine the "BO" (Both)-coded rows with the "MA"/"PA"-coded rows to produce the result set.[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

I hate it when I accidentally hit the [Submit] button instead of the [Edit Post] Button...I hope it is obvious that my "You" should have been "Your" in my post, above to Sxschech. Sorry.

Plus, in a few minutes, I'll post an explanation of Dagon's use of "over partition by" since it is past the end of Dagon's work day (in the UK).

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

First, regarding Oracle Analytic Functions, I recommend your Googling for that topic. There are several good links that offer more comprehensive explanations and examples than what I have time to post, below.

To explain the use of "over partition by", I'll use a code piece from Dagon's code, above:

select id, name, address1, zip, address_type, rank() over (partition by id order by address1, zip) as rnk
  from testaddr
/

  ID NAME ADDRESS1     ZIP   ADDRES        RNK
---- ---- ------------ ----- ------ ----------
 123 Joe  34 Main St   92191 MA              1
 123 Joe  34 Main St   92191 PR              1
 234 Gil  65 West St   92192 MA              1
 234 Gil  91 East Rd   94596 PR              2
 598 May  83 First Ave 94596 PR              1

5 rows selected.

Notice that the above code lists the original rows, with the addition of "RNK", which produces a rank number (e.g., 1, 2) of ADDRESS1 and ZIP within distinct ID numbers. If there is a tie between ADDRESS1/ZIP entries for an employee ID, then the rank ties at "1". If ADDRESS1/ZIP differs for an employee ID, then that results in a rank "1" and a rank "2".

The code that produces this "1"/"2" ranking is the Oracle Analytic Function "RANK() Function". To achieve the ranking, Oracle syntax has you specify the column(s) for grouping and the column(s) for sorting within each grouping. Here are some interpretations that might help you understand the syntax that Oracle uses following the specification of the RANK():[ul][li]"OVER": means "across" or "using" the specification(s) that follow (e.g., "(PARTITION BY...").[/li][li]"PARTITION BY": means "GROUP BY" (for the purposes of Analytic Functions).[/li][li]"ORDER BY": means "SORT BY" within the specified groupings.[/li][/ul]If you have additional questions on these topics, we'll be pleased to respond.



Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sxschech]

Thanks for the explanations SantaMufasa. As time permits, I'll try to explore this some more as it certainly seems useful for the types of results I get asked to do, but don't have the concept down to be able to take advantage of the possibilities.

Although I don't have the ability yet to write the code myself, I fortunately was able to determine that there was an implied "r" in you, thanks for clarifying just the same.

 

[Dagon]

As you probably realized, the rank() query wasn't the proposed solution. It was an earlier effort that didn't quite pan out, but I forgot to remove it from the script.

My actual solution was the lead/lag version. Lead and lag allow you to look ahead or behind you at records of the same type as you go through a set of records. So what I did in the second query is to partition the data on id, address1 and zip. I then looked to see if there was a record of the same type coming after the current record (lead). If there was such a record, I know it is a duplicate.

This would enable me to get the set of records with duplicates just by saying:

select id, name, address1, zip, address_type
from
(select id, name, address1, zip, address_type, lead(zip) over (partition by id, address1, zip order by zip) next_zip
from testaddr)
where next_zip is null;

However, you also wanted the address type to indicate whether there were multiple addresses or not. I couldn't do that with the query above as there is nothing about the duplicate records returned by the query which distinguishes them from the non-duplicate records.

To get round this, I also applied a lag function. This means that for the record at the end of the sequence (the one with next_zip equal to NULL), I would also pick up the zip of the record behind it. This would also tell me it is part of a series of duplicates, so I could use that information to set the flag.

 

[sxschech]

Thanks Dagon,

was off site on Friday, so reading it now. Thank you for your explanation.