Need latest record from a multiple table query

[elantrip]

I have three tables:
INSURED (basic insured info):
POLICY#
LAST
FIRST
EXP_DATE
EFF_DATE

CLAIM (claim info for each claim files by the insured):
CLAIM#
POLICY#
TYPE_LOSS
CAUSE
DATE_LOSS
DATE_REPORTED
CATEGORY
DRIVER

HIST_CLAIM (every change the claim goes through):
CLAIM#
DATE_CHANGED
RESERVES_START
PAID_PMT
TOT_INCURRED
RECOVERIES
LOSS_RESERVES
LOSS_RECOV

I need to create a record set that has the INSURED and CLAIM data and the latest change in HIST_CLAIM.

For example:

A date range of 01-JAN-01 through 12-DEC-03 is entered. Claim #10 has several records in the HIST_CLAIM file that file within that date range. However, I only want the last HIST_CLAIM record within that date range (using DATE_CHANGED to get the records).


Thread759-672989 helped a lot, except that it only dealt with one table.

 

[carp]

I haven't tested this, but I think it will work:

SELECT whatever_you_want_displayed
FROM insured i, claim c, hist_claim h
WHERE i.policy# = c.policy#
AND c.claim# = h.claim#
AND h.date_changed = (SELECT max(date_changed)
FROM hist_claim
WHERE date_changed BETWEEN your_min_date AND your_max_date
AND claim# = h.claim#);

Elbert, CO
1424 MST

 

[elantrip]

Thanks for the response, but I am not getting any records back. Here's what the query looks like:

SELECT *
FROM insured i, claim c, hist_claim h
WHERE i.policy# = c.policy#
AND c.claim# = h.claim#
AND h.date_changed = (SELECT max(date_changed)
FROM hist_claim
WHERE date_changed BETWEEN TO_DATE
('01-JAN-01') AND TO_DATE('12-DEC-03') and
claim# = h.claim#)

 

[carp]

Assuming your database's default date format is the standard 'DD-MON-YY', you don't need the to_date functions.
I went ahead and set up a (highly simplified) simulation of your situation, and the query DOES work:
SQL> select * from insured;

POLICY NAME
---------- ----------------
1 jones
2 smith

SQL> select * from claim;

POLICY CLAIM DUMMY
---------- ---------- ----------
1 1 5
2 2 35

SQL> select * from hist_claim;

SQL> select * from hist_claim;

CLAIM DATE_CHAN
---------- ---------
1 29-NOV-03
1 01-DEC-03
1 07-DEC-03
1 09-DEC-03
2 21-NOV-03
2 01-DEC-03
2 10-DEC-03

SQL> select *
2 from insured i, claim c, hist_claim h
3 where i.policy = c.policy
4 and c.claim = h.claim
5 and date_changed = (select max(date_changed)
6 from hist_claim
7 where date_changed between sysdate - 3 and sysdate and claim = h.claim);

POLICY NAME POLICY CLAIM DUMMY
---------- ------------------------------ ---------- ---------- ----------
CLAIM DATE_CHAN
---------- ---------
1 jones 1 1 5
1 09-DEC-03

2 smith 2 2 35
2 10-DEC-03

 

[elantrip]

Does work like a charm.

For some reason the same query does not return any rows in the Table Editor in the Manager Console.

Worked great within SQL Plus.

You've been a GREAT help.

Thanks again.

 

[carp]

Any time!