Cheaplogic Cheaplogic - 7 months ago 14
SQL Question

Oracle Database SQL - Embedded selects

I am attempting to build a script to retrieve a list of "edit_mnemonic" values that are populated for today. To do this, I have to check against the "last_analysis_date" on the claim table.

I am working with three tables

claim - primary key "claim_iid" - contains last_analysis_date, claim_EID

claim_line - primary key "claim_line_iid" - contains "claim_iid"

claim_line_edits - primary key "claime_line_edit_iid" - contains "claim_line_iid", "edit_mnemonic"

So far I have come up with

select edit_mnemonic

from claim_line_edit

where claim_line_iid in
(select claim_line_iid from claim_line where claim_iid in

(select claim_iid from claim where to_date(last_analysis_date) >= to_date(sysdate)));

select claim_eid from claim where to_date(last_analysis_date) >= to_date(sysdate);


This gives me the following:

EDIT_MNEMONIC
--------------------
MFD
ICM
EST
ICM
EST

5 rows selected.

CLAIM_EID
---------------------------------------------------------------------------
1850735 B
9999999
9999999

3 rows selected.


My question is, how can I select the corresponding claim_EID from the claim table for these edit_mnemonics, within the same statement? Do I need to use a join here?

I tried using

select edit_mnemonic, claim_eid

from claim_line_edit, claim

where claim_line_iid in
(select claim_line_iid from claim_line where claim_iid in

(select claim_iid from claim where to_date(last_analysis_date) >= to_date(sysdate)));


The result was that I received a list of ALL claim_eid's on the claim table.

My desired output would be

EDIT_MNEMONIC CLAIM_EID
------------- ---------
MFD 1850735 B
ICM 9999999
EST 9999999
ICM 9999999
EST 9999999

5 rows selected.


Thank you for your time in reading my post, please let me know if additional information is needed.

Answer
select edit_mnemonic, claim_eid
from claim_line_edits cle join claim_line cl on cle.claim_line_iid = cl.claim_line_iid 
                          join claims c on cl.claim_iid = c.claim_iid
where last_analysis_date >= trunc(sysdate)

This assumes last_analysis_date is in date format, as it should be; otherwise you would need your to_date(last_analysis_date), better yet with an explicit date format.