Solved

Showing Patient and Insured information on 1 row

Posted on 2013-10-29
9
350 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
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
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Machinegunner
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 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

16 Experts available now in Live!

Get 1:1 Help Now