Reputation: 65
I have a select statement written for a table i.e STAY table in which 2 of the date fields namely ADMSN_DT and DSCHRG_DT are not availabe. So I was informed to fetch these fields from another table named CLM table by joining the claim_id.
So I have framed my select query as,
select
Member_id as MemberID,
(select a.ADMSN_DT from CLM a, STAY b where a.cl_id = b.claim_id) as Admission_Date,
(select a.DSCHRG_DT from CLM a, STAY b where a.cl_id = b.claim_id) as Discharge_Date,
field 1,
feild 2, such as ... some fields from STAY table
from STAY;
The problem here is I got a 2 records from this table, the claim_id is different but I get the same date in both the record.
For Eg:-
Claim_id Admission_date Discharge_date
1234 11/12/2015 12/12/2015 -- this claim id has dates in clm table
5678 11/12/2015 12/12/2015 -- this claim id has no dates in clm table
its just copying the same value from first record to the second record.. HOw to avoid this? Any help is much appreciated.
Thanks!
Upvotes: 0
Views: 3811
Reputation: 1866
Your code makes cartesian product between result of sub query and other results returning from the enclosing query
try this :
select
Member_id as MemberID,
a.ADMSN_DT as Admission_Date,
a.DSCHRG_DT as Discharge_Date,
field 1,
feild 2, ...
from STAY b, CLM a
where a.cl_id = b.claim_id(+)
Upvotes: 1
Reputation: 147
Try following query;
select Member_id as MemberID,
(select a.ADMSN_DT from CLM a where a.cl_id = s.claim_id) as Admission_Date,
(select a.DSCHRG_DT from CLM a where a.cl_id = s.claim_id) as Discharge_Date
from STAY s;
Upvotes: 1