CODEWITHSUNDEEP
oracle-databaseselectoracle11g
blackpanther
blackpanther

Reputation: 65

select statement for a field inside another select statement - oracle

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

Answers (2)

osama yaccoub
osama yaccoub

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

fatihn
fatihn

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

Related Questions