Solved

Showing Patient and Insured information on 1 row

Posted on 2013-10-29
9
355 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 500 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
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 shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

688 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