Oracle 8 outer join quirks 2

[ddiamond]

I am going to use a simple example to illistrate the problems I have run into.
Example:
You want to return a query of all insurance policy numbers and their associated insured if they have an insured. So you at first attempt the following outer join:

SELECT pm.policy_number, emp.Full_Name
FROM policy_master pm, Employee emp
WHERE pm.policy_number = emp.policy_number (+)
  AND emp.employee_type = 'INSURED'

Unfortunately, eventhough you have an outer join, you will still drop records because of the criteria on employee_type. The common solution to this that I've seen is:

SELECT pm.policy_number, emp.Full_Name
FROM policy_master pm, Employee emp
WHERE pm.policy_number = emp.policy_number (+)
  AND (emp.employee_type = 'INSURED' OR 
       emp.policy_number is null)

This works better but still may drop some records if you have a policy that has some employees associated with the policy, but none of them are of type 'INSURED'. This is because it will pass the outer join, and thus fail the is null criteria. I've seen the following ugly work around:

SELECT pm.policy_number, emp.Full_Name
FROM policy_master pm, Employee emp
WHERE pm.policy_number = emp.policy_number
  AND emp.employee_type = 'INSURED'

UNION ALL

SELECT pm.policy_number, '' AS Full_Name
FROM policy_master pm
WHERE NOT EXISTS (
  SELECT 1 FROM Employee emp
  WHERE emp.employee_type = 'INSURED'
    AND emp.policy_number = pm.policy_number)

In this simple example, the work-around wasn't too bad, but if you had a query with many outer joins that had additional criteria, this would get messy very quickly. Does anyone know of a cleaner approach? I know in later versions of Oracle, you can get around this with the LEFT JOIN key word, but I don't believe Oracle 8 supports this.

 

[SantaMufasa]

DDiamond,

Just for my satisfaction, could you re-run your most recent code in SQL*Plus with

set time on
set timing on

...then compare the consumption to this code alternative:

SELECT pm.policy_number, emp.Full_Name
FROM policy_master pm, Employee emp
WHERE pm.policy_number = emp.policy_number
  AND emp.employee_type = 'INSURED'
UNION ALL
(SELECT pm.policy_number, '' AS Full_Name
   FROM policy_master pm
  MINUS
 SELECT emp.policy_number, null
   FROM Employee emp
  WHERE emp.employee_type <> 'INSURED');

Let us know 1) are the results correct, and 2) is it faster?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[ddiamond]

The example I gave you was fictitious to simplify things. I'll see if I can come up with a similar example that uses real tables and run your experiment. On my real query, it took 2 minutes to run on the version that dropped records. After I did the union all and the not exists, it took 8 hours. I then added an index on the equivelent field to emp.policy_number, and it ran in 30 seconds. Your minus solution probably won't require an index.

 

[ddiamond]

In case your interested, here is my real query, although I don't expect anyone to take the time to decipher it. But at least you'll why I made up a simpler example:

SELECT 
  MAX(CCS.EOM_DATE) AS EOM_DATE,
  P.PRODUCER_NUMBER AS PRODUCER,
  P.PRODUCER_NAME,
  CM.PRODUCER || CCS.CLAIM_STATUS AS PRODUCER_AND_STATUS,
  NVL(VW.PRIOR_OPEN_COUNT,0) AS PRIOR_OPEN_COUNT,
  NVL(VW.MONTH_OPEN_COUNT,0) AS MONTH_OPEN_COUNT,
  NVL(VW.MONTH_CLOSE_COUNT,0) AS MONTH_CLOSE_COUNT,
  NVL(VW.CURRENT_OPEN_COUNT,0) AS CURRENT_OPEN_COUNT,
  CM.POLICY_NUMBER,
  PM.EFFECTIVE_DATE,
  PM.POLICY_DBA_NAME,
  CCS.CLAIM_NUMBER,
  CCS.CLAIM_STATUS,
  CM.DATE_OF_LOSS_RECEIVED,
  CM.DATE_OF_LOSS,
  CM.LOSS_DESCRIPTION,
  SUM(DECODE(CLD.SOURCE, 'MTDLoss', 0, CLD.LOSS_RESERVE)) AS LOSS_RESERVE,
  SUM(DECODE(CLD.SOURCE, 'MTDLoss', 0, CLD.EXPENSE_RESERVE)) AS EXPENSE_RESERVE,
  SUM(DECODE(CLD.SOURCE, 'MTDLoss', 0, CLD.LOSS_PAID_MINUS_DEDUCTIBLE)) AS LOSS_PAID,
  SUM(DECODE(CLD.SOURCE, 'MTDLoss', 0, CLD.EXPENSE_PAID_MINUS_DEDUCTIBLE)) AS EXPENSE_PAID,
  SUM(DECODE(CLD.SOURCE, 'MTDLoss', 0, CLD.LOSS_SALVAGE + CLD.LOSS_DEDUCTIBLE_PAID + CLD.EXPENSE_DEDUCTIBLE_PAID)) AS RECOVERIES,
  SUM(DECODE(LF.calendar_book_date, CCS.eom_date, LF.incurred, 0)) AS INCURRED
FROM MIGRATE_GRE.CLAIM_PREV_EOM_STATUS CPS,
  MIGRATE_GRE.CLAIM_EOM_STATUS CCS,
  MIGRATE_GRE.CLAIM_MASTER CM,
  MIGRATE_GRE.POLICY_MASTER PM,
  MIGRATE_GRE.PRODUCERS P,
  MIGRATE_GRE.CLAIM_COUNTS_BY_PRODUCER_VW VW,
  MIGRATE_GRE.LOSS_FINANCIAL LF,
  MIGRATE_GRE.CLAIM_LOSS_DIRECT CLD
WHERE 1=1   
  AND P.PRODUCER_NUMBER = CM.PRODUCER (+)
  AND CM.POLICY_NUMBER = PM.POLICY_NUMBER (+)
  AND CM.PRODUCER = VW.PRODUCER (+)
  AND CM.CLAIM_NUMBER = CCS.CLAIM_NUMBER (+)
  AND CCS.CLAIM_NUMBER = CPS.CLAIM_NUMBER (+)
  AND CM.CLAIM_NUMBER = CLD.CLAIM_NUMBER (+)
  AND CM.CLAIM_NUMBER = LF.CLAIM_NUMBER (+)
  AND ( 
  (CCS.CLAIM_STATUS='Closed' AND   CPS.CLAIM_STATUS='Open')
  OR
  (CCS.CLAIM_STATUS='Open')
  )
GROUP BY
  P.PRODUCER_NUMBER,
  P.PRODUCER_NAME,
  CM.PRODUCER || CCS.CLAIM_STATUS,
  VW.PRIOR_OPEN_COUNT,
  VW.MONTH_OPEN_COUNT,
  VW.MONTH_CLOSE_COUNT,
  VW.CURRENT_OPEN_COUNT,
  CM.POLICY_NUMBER,
  PM.EFFECTIVE_DATE,
  PM.POLICY_DBA_NAME,
  CCS.CLAIM_NUMBER,
  CCS.CLAIM_STATUS,
  CM.DATE_OF_LOSS_RECEIVED,
  CM.DATE_OF_LOSS,
  CM.LOSS_DESCRIPTION

UNION ALL

SELECT
  DECODE(1,2,SYSDATE,NULL) AS EOM_DATE,
  P.PRODUCER_NUMBER AS PRODUCER,
  P.PRODUCER_NAME,
  '' AS PRODUCER_AND_STATUS,
  0 AS PRIOR_OPEN_COUNT,
  0 AS MONTH_OPEN_COUNT,
  0 AS MONTH_CLOSE_COUNT,
  0 AS CURRENT_OPEN_COUNT,
  '' AS POLICY_NUMBE5,
  DECODE(1,2,SYSDATE,NULL) AS EFFECTIVE_DATE,
  '' AS POLICY_DBA_NAME,
  '' AS CLAIM_NUMBER,
  '' AS CLAIM_STATUS,
  DECODE(1,2,SYSDATE,NULL) AS DATE_OF_LOSS_RECEIVED,
  DECODE(1,2,SYSDATE,NULL) AS DATE_OF_LOSS,
  NULL AS LOSS_DESCRIPTION,
  0 AS LOSS_RESERVE,
  0 AS EXPENSE_RESERVE,
  0 AS LOSS_PAID,
  0 AS EXPENSE_PAID,
  0 AS RECOVERIES,
  0 AS INCURRED
FROM 
  MIGRATE_GRE.PRODUCERS P
WHERE 1=1  
  AND EXISTS (
    SELECT 1
    FROM
      MIGRATE_GRE.CLAIM_EOM_STATUS CCS,
      MIGRATE_GRE.CLAIM_MASTER CM
    WHERE CM.producer = P.PRODUCER_NUMBER
      AND CM.CLAIM_NUMBER = CCS.CLAIM_NUMBER)
  AND NOT EXISTS (
    SELECT 1
    FROM
      MIGRATE_GRE.CLAIM_PREV_EOM_STATUS CPS,
      MIGRATE_GRE.CLAIM_EOM_STATUS CCS,
      MIGRATE_GRE.CLAIM_MASTER CM
    WHERE CM.producer = P.PRODUCER_NUMBER
      AND CM.CLAIM_NUMBER = CCS.CLAIM_NUMBER (+) 
      AND  CCS.CLAIM_NUMBER = CPS.CLAIM_NUMBER (+)
      AND ( 
      (CCS.CLAIM_STATUS='Closed' AND CPS.CLAIM_STATUS='Open')
      OR
      (CCS.CLAIM_STATUS='Open')
      ))

 

[SimonSellick]

ddiamond,

You could cater for the extra restriction on not-null rows with something along these lines:

SELECT pm.policy_number, emp.Full_Name
FROM   policy_master pm, Employee emp
WHERE  pm.policy_number = emp.policy_number (+)
  AND  nvl(emp.employee_type, 'INSURED') = 'INSURED'

as long as none of the employee_type values are null.

I thought that Oracle 8 did implement the ANSI join syntax, but it's a while since I used it and I cannot remember definitely.

Simon

 

[ddiamond]

Thanks Simon for the suggestion. That will solve the problem in scenario where the left join fails, but what happens if the left join succeeds, but there are no records of type 'INSURED'? Whouldn't your query drop the record in that case? I wish oracle 8.0 had the ansi join syntax. That would completely solve my problem. I don't know if 8i has it, but I know that 8.0 does not.

 

[SimonSellick]

Ah. Didn't think of that :-(

 

[ddiamond]

Mufasa,

I tried your suggestion with my real query and I did get different results. When I investigated the discrepancy, I discoved that the results from your version of the query were actually correct, and my query using the not exists was still dropping some records. So if nothing else, you provided me with another way to test the accuracy of my results. Will provide timing results once I've fixed my original query so that it provides the correct results.

- Dan

 

[ddiamond]

Mufasa,

I can't seem to get consistant timing results. Both seem to take anywhere from a minute and a half to 3 minutes. It may depend on both caching and what other load is on our oracle server. But from that range, I conclude that both have about the same performance. Although, I would imagine your solution would be much faster than mine before I added that index. I do think the solution with the minus is a little more readable.

- Dan

 

[Beilstwh]

Actually, the code can be used as follows. (I do this type of outer join all the time.

SELECT pm.policy_number, emp.Full_Name
FROM policy_master pm, Employee emp
WHERE pm.policy_number = emp.policy_number (+)
  AND emp.employee_type[B](+)[/B] = 'INSURED';

Simply use an outer join on the constant join

Bill
Oracle DBA/Developer
New York State, USA

 

[ddiamond]

That is exactly what I was looking for. Thanks Bill.

 

[StevenB]

OK, I found this while googling, and I may be running into something similar.

I'm trying to write what I thought was a fairly simple query in Oracle 8i for a resume tracking system. We have two tables:

1. resume_mst, which has 1 record per resume (applicant)
2. res_simple_addr, which has potentially multiple e-mails per applicant, with e-mails identified by a combination of resume_id, an address_type of 1, and a sequence number where the highest number is the most recently entered e-mail.

I'm trying to write a report that brings back some information about a resume, along with the most recent e-mail address for that resume.

Without any joins, there are about 23,000 rows of resume data. As soon as I join in the res_simple_addr table, I lose about 3,000 rows - all of the ones where the employee has no e-mail address in the res_simple_addr table.

I put an outer join so that the query should return rows even if the candidate has no e-mail address.

I can't figure it out. Then again, I'm barely knowledgeable enough with SQL to be dangerous. Here's the actual query:

Select mst.last_name, eml.address
from resume_mst mst,
res_simple_addr eml
where mst.resume_id= eml.resume_id(+)
       and eml.address_type=1
	   and eml.seq_num = (select max(seq_num) from res_simple_addr eml2 where eml2.resume_id = eml.resume_id)
and   mst.resume_id in 
		(select distinct(a.resume_id)
		from action a
		where a.sys_req_id in 
		(select r.sys_req_id from requistn r where r.req_stat_code in ('O','H','P'))
		union
		select distinct(resume_id) from action where action_date > sysdate-180)

Any suggestions? If at all possible, I'd love to avoid doing a union...

Thanks!

Steve

 

[Beilstwh]

Select mst.last_name, eml.address
from resume_mst mst,
res_simple_addr eml
where mst.resume_id= eml.resume_id(+)
and eml.address_type(+)=1
and eml.seq_num(+) = (select max(seq_num) from res_simple_addr eml2 where eml2.resume_id = eml.resume_id)
and mst.resume_id in
(select distinct(a.resume_id)
from action a
where a.sys_req_id in
(select r.sys_req_id from requistn r where r.req_stat_code in ('O','H','P'))
union
select distinct(resume_id) from action where action_date > sysdate-180)

Bill
Oracle DBA/Developer
New York State, USA