?
Solved

Showing Patient and Insured information on 1 row

Posted on 2013-10-29
9
Medium Priority
?
357 Views
Last Modified: 2013-10-31
Hello,

I have a patient that can be different from the insured name, say a spouse of the employee.
Spouse - Minnie Mouse
Employee - Mickey Mouse

If I want to show the Insured name on the spouse's record, how do I do that?
I want to show Mickey as the Insured and Minnie as the patient on 1 row.
The query below does this, but what if I have a big table that needs to find out who the insured
persons name and have it shown on the dependent or spouse's record?
Do I need to write a sub query to do this?  This is based on the same table - MEM_MEMBER
This is part of a larger query and not separate and not to be joined on the '22222201-REGRESS2-'
constant value but joined somehow...

Using Oracle 11g

Thanks!

SELECT PATIENT.MEM_UID Pat_MEM_UID,
       PATIENT.MEM_ID Pat_MEM_UID,
       PATIENT.MEM_FORMATTED_NAME Pat_FORMATTED_NAME,
       PATIENT.MEM_MEM_UID_POLICY_HOLDER Pat_Policy_Holder,
       INSURED.MEM_UID Ins_MEM_UID,      
       INSURED.MEM_MEM_UID_POLICY_HOLDER Ins_POLICY_HOLDER,
       INSURED.MEM_ID Ins_MEM_ID,
       INSURED.MEM_FORMATTED_NAME Ins_FORMATTED_NAME
FROM   MEM_MEMBER PATIENT,
       MEM_MEMBER INSURED
WHERE  PATIENT.MEM_ID = '22222201-REGRESS2-'
AND    PATIENT.MEM_MEM_UID_POLICY_HOLDER = INSURED.MEM_UID

(Attachment - raw data and result set from query above)
Mouse.xls
0
Comment
Question by:Machinegunner
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39610562
I think you can use a Hierarchical Query for this.

Based on what you posted, see if this works.  If not, please add to the test case and explain where it fails.

drop table tab1 purge;

create table tab1(
	MEM_UID varchar2(8),
	MEM_ID varchar2(18),
	MEM_FORMATTED_NAME varchar2(13),
	MEM_MEM_UID_POLICY_HOLDER varchar2(8)
);

insert into tab1 values('1744104','22222201-REGRESS2-','MOUSE, MINNIE','1744103');
insert into tab1 values('1744103','22222200-REGRESS2-','MOUSE, MICKEY',null);
--ad a dummy record just as a balance
insert into tab1 values('1111111','11111111-REGRESS2-','Dummy, Record',null);
commit;
							
							
select mem_uid, mem_id, mem_formatted_name,
connect_by_root mem_uid, connect_by_root mem_id, connect_by_root mem_formatted_name
from tab1
where level > 1
connect by prior MEM_UID =MEM_MEM_UID_POLICY_HOLDER 
/

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39610641
If wishing to avoid a hierarchial query:
I added Mickey as patient for this, so it is one row per patient of both patient and insured:
| PAT_MEM_UID |         PAT_MEM_ID | PAT_FORMATTED_NAME | PAT_POLICY_HOLDER | INS_MEM_UID |         INS_MEM_ID | INS_FORMATTED_NAME |
|-------------|--------------------|--------------------|-------------------|-------------|--------------------|--------------------|
|     1744104 | 22222201-REGRESS2- |      MOUSE, MINNIE |           1744103 |     1744103 | 22222200-REGRESS2- |      MOUSE, MICKEY |
|     1744103 | 22222201-REGRESS2- |      MOUSE, MICKEY |           1744103 |     1744103 | 22222200-REGRESS2- |      MOUSE, MICKEY |

Open in new window

Produced by the following query
SELECT
        PATIENT.MEM_UID                                                      AS Pat_MEM_UID
      , PATIENT.MEM_ID                                                       AS Pat_MEM_ID
      , PATIENT.MEM_FORMATTED_NAME                                           AS Pat_FORMATTED_NAME
      , PATIENT.MEM_MEM_UID_POLICY_HOLDER                                    AS Pat_Policy_Holder
      , COALESCE(h2.mem_mem_uid_policy_holder, h1.mem_mem_uid_policy_holder
                , PATIENT.mem_mem_uid_policy_holder)                         AS Ins_MEM_UID
      , COALESCE(h2.MEM_ID, h1.MEM_ID)                                       AS Ins_MEM_ID
      , COALESCE(h2.mem_formatted_name, h1.mem_formatted_name)               AS Ins_FORMATTED_NAME
FROM MEM_MEMBER PATIENT
        LEFT JOIN MEM_HIER H1
                ON PATIENT.MEM_UID = H1.MEM_UID
        LEFT JOIN MEM_HIER H2
                ON H1.MEM_MEM_UID_POLICY_HOLDER = H2.MEM_UID
WHERE PATIENT.MEM_ID = '22222201-REGRESS2-'
;

******************************
data
CREATE TABLE MEM_HIER
    (MEM_UID int, MEM_ID varchar2(18), MEM_FORMATTED_NAME varchar2(13), MEM_MEM_UID_POLICY_HOLDER int)
;

INSERT ALL 
    INTO MEM_HIER (MEM_UID, MEM_ID, MEM_FORMATTED_NAME, MEM_MEM_UID_POLICY_HOLDER)
         VALUES (1744104, '22222201-REGRESS2-', 'MOUSE, MINNIE', 1744103)
    INTO MEM_HIER (MEM_UID, MEM_ID, MEM_FORMATTED_NAME, MEM_MEM_UID_POLICY_HOLDER)
         VALUES (1744103, '22222200-REGRESS2-', 'MOUSE, MICKEY', NULL)
SELECT * FROM dual
;

CREATE TABLE MEM_MEMBER
    ("MEM_UID" int, "MEM_ID" varchar2(18), "MEM_FORMATTED_NAME" varchar2(13), "MEM_MEM_UID_POLICY_HOLDER" int)
;

INSERT ALL 
    INTO MEM_MEMBER ("MEM_UID", "MEM_ID", "MEM_FORMATTED_NAME", "MEM_MEM_UID_POLICY_HOLDER")
         VALUES (1744104, '22222201-REGRESS2-', 'MOUSE, MINNIE', 1744103)
    INTO MEM_MEMBER ("MEM_UID", "MEM_ID", "MEM_FORMATTED_NAME", "MEM_MEM_UID_POLICY_HOLDER")
         VALUES (1744103, '22222201-REGRESS2-', 'MOUSE, MICKEY', 1744103)
SELECT * FROM dual
;


[1]: http://sqlfiddle.com/#!4/166ee/2 

Open in new window

0
 

Author Comment

by:Machinegunner
ID: 39611714
Thanks for the replies,
I kind of like the non-hierarchical query as it produced the row that I wanted when I ran that query sample.
When I ran the hierarchical query, I received this error - ORA-01436: CONNECT BY loop in user data.
Anyways...
The table that this query is being connected to is called - MEM_MEMBER and it
currently has - Select count(*) FROM MEM_MEMBER = 1,470,876 rows...

I'm just trying to see how if the Mouse's will return or show with just the joins but
even doing a LIKE clause, it still doing a full table retrieve!  Any clues?

SELECT
    PATIENT.MEM_UID AS Pat_MEM_UID
  , PATIENT.MEM_ID AS Pat_MEM_ID
  , PATIENT.MEM_FORMATTED_NAME AS Pat_FORMATTED_NAME
  , PATIENT.MEM_MEM_UID_POLICY_HOLDER AS Pat_Policy_Holder
  , COALESCE(h2.mem_mem_uid_policy_holder, h1.mem_mem_uid_policy_holder
  , PATIENT.mem_mem_uid_policy_holder) AS Ins_MEM_UID
  , COALESCE(h2.MEM_ID, h1.MEM_ID) AS Ins_MEM_ID
  , COALESCE(h2.mem_formatted_name, h1.mem_formatted_name) AS Ins_FORMATTED_NAME
FROM MEM_MEMBER PATIENT
LEFT JOIN MEM_MEMBER H1 ON PATIENT.MEM_UID = H1.MEM_UID
LEFT JOIN MEM_MEMBER H2 ON H1.MEM_MEM_UID_POLICY_HOLDER = H2.MEM_UID
and PATIENT.MEM_FORMATTED_NAME like '%MOUSE%'
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39611730
>>ORA-01436: CONNECT BY loop in user data

Can an insured also be an insurer of an insured?

>>LIKE clause, it still doing a full table retrieve!

What indexes do you have on he tables?
Are statistics up to date on the tables?
0
 

Author Comment

by:Machinegunner
ID: 39611936
Thanks for the reply.
Yes, the insurer can also be the insured.
Mickey Mouse can be the insured for himself as well.

I looked via Toad and there are 20 indexes on that table, and no index on the formatted name but one on the mem_uid and mem_id.

Sorry, I wish I could update the statistics on this table but no permissions to do this, I would have to create a ticket and have a DBA to do this and that would take "days" to get accomplished...  :-)

I'm just trying to use this as part of a much larger query and there won't be
and connections to just one specific value like '22222201-REGRESS2-' but to find out
what the insurer name is and their affiliated information.

Thanks again!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39612899
try with no leading %

        and PATIENT.MEM_FORMATTED_NAME like 'MOUSE%'

however without an index on that field it must do a full scan to satisfy your request
(how else will it know if a any row is going to be a mouse?)

note: IF the full name isn't in upper case it may get worse as you would need to do this:

        and upper(PATIENT.MEM_FORMATTED_NAME) like 'MOUSE%'

and for this to be more efficient you would need a function based index
0
 

Author Closing Comment

by:Machinegunner
ID: 39612912
Thanks for the information, I was able to re-query and come up with this and works, thanks again!

SELECT
.
.
COALESCE(Insured.mem_first_name, Patient.mem_first_name) InsuredFirstName,
COALESCE(Insured.mem_first_name || ' ' || Insured.mem_last_name, Patient.mem_first_name || ' ' || Patient.mem_last_name) InsuredFullName,
COALESCE(Insured.mem_mem_uid_policy_holder, Patient.mem_mem_uid_policy_holder) InsuredId,
COALESCE(Insured.mem_last_name, Patient.mem_last_name) InsuredLastName,
COALESCE(Insured.mem_gender, Patient.mem_gender) InsuredSex
.
.
.

FROM    
 MCTRAIN.MEM_MEMBER mem,
 MCTRAIN.MEM_MEMBER Patient,
 MCTRAIN.MEM_MEMBER Insured
 .
 .
 .
 
WHERE  
.
.
.
AND mem.MEM_UID = Patient.MEM_UID (+)
AND Patient.MEM_MEM_UID_POLICY_HOLDER = Insured.MEM_UID (+)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39612957
thanks for the grading. Cheers, Paul

ps:
not sure I would take the giant leap backwards to "ye olde join" syntax(+)
strongly prefer ansi syntax :)
0
 

Author Comment

by:Machinegunner
ID: 39613935
Yeah me too, but the original program was written back in early 2000's something like and a very long program, so didn't want to rewrite the whole thing and just add what is needed.
Thanks again, really appreciate the help and saved the day.
Steve
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

765 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