Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Showing Patient and Insured information on 1 row

Posted on 2013-10-29
9
Medium Priority
?
358 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 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