My Query is this:
Select cm.CASE_NUM , lrc.AGENCY_NAME , lg.GENDER , (Select PAT_STAT_PREG, CASE PAT_STAT_PREG when 0 then 'No'
when 1 then 'Yes' when 2 then 'Unknown' else 'Not Applicable'
END
from case_pat_info ) from safetyreport sr, case_master cm, lm_regulatory_contact lrc, LM_GENDER lg , CASE_PAT_INFO cpi
where
date_imported >= '26-NOV-18' and cpi.GENDER_ID = 2 and
sr.case_xref = cm.case_id and
sr.AGENCY_ID = lrc.AGENCY_ID and
cpi.GENDER_ID = lg.GENDER_ID and
cm.state_id <>1 and
lrc.deleted is NULL and
lg.deleted is NULL and
cpi.deleted is NULL;
I want to show Pregnancy status along with the Pregnancy ID in this query itself.
But am getting an error: too many values.
Kindly rectify my query.
.........................................
For example, if I just use cpi.PAT_STAT_PREG, I will get a column with values 0,1 or 2.
But I want a column for business which shows the pregnancy status , instead of 0, 1,2.
Like when 0 then 'No'
when 1 then 'Yes' when 2 then 'Unknown' else 'Not Applicable'.
I thought of using CASE function for this, but instead I am getting error..."too many values".
Request for More Info:
You need to clarify what you want (which is why we asked for *test cases*)
Because "(Select PAT_STAT_PREG, CASE PAT_STAT_PREG"
what do you want to compare to 0,1,2 ?
================================
Update:
OK, I *think* I understand what you mean. You'll have this:
Select
cm.CASE_NUM ,
lrc.AGENCY_NAME ,
lg.GENDER ,
case cpi.CASE PAT_STAT_PREG
when 0 then 'No'
when 1 then 'Yes'
when 2 then 'Unknown'
else 'Not Applicable'
end status
from safetyreport sr,
case_master cm,
lm_regulatory_contact lrc,
LM_GENDER lg ,
CASE_PAT_INFO cpi
where date_imported >= '26-NOV-18' and cpi.GENDER_ID = 2 and
sr.case_xref = cm.case_id and
sr.AGENCY_ID = lrc.AGENCY_ID and
cpi.GENDER_ID = lg.GENDER_ID and
cm.state_id <>1 and
lrc.deleted is NULL and
lg.deleted is NULL and
cpi.deleted is NULL;