asked on

Error in case when construct expecting a FROM statement ORACLE 11g

The error that is generated is FROM KEYWORD IS NOT FOUND WHERE EXPECTED. I am not proficient in constructing case when statement
What I am trying to do:
I am counting the records that meet the criteria in the where CLAUSE
If count = 0 then return o
else then evaluate conditions in the 2nd when statement
if either condition in the When statement is true count the number of people
return zero.

Note: The ptl alias is for the main query table.

This a subquery used to derive a calculated value for the TotalAvail column

(Select count(a.version_id)
Case When 0 THEN 0
WHEN ( ( (ptl.Min_Rank is not null AND ptl.Max_Rank is not null)
AND ( replace(p.pay_grade,'-') in
(select pl.rank from minmax_rank_lookup pl where rank_range Between
(select rank_range from minmax_rank_lookup where rank = ptl.Min_Rank) AND
(select rank_range from minmax_rank_lookup where rank = ptl.Max_Rank) ) ) )
AND ((ptl.Rate_Codes is not null AND p.rating = ptl.Rate_Codes) OR ( ptl.Rate_Codes is null))
AND (trunc(a.expiration_date) >= trunc(to_date('12/01/2011','MM/DD/YYYY')) )
OR (ptl.Min_Rank is null AND ptl.Max_Rank is null AND ((ptl.Rate_Codes is null) OR (ptl.Rate_Codes is NOT null AND p.rating = ptl.Rate_Codes) ))
THEN count(p_id) ELSE 0
END

FROM accreditations a, persons p where a.accred_version_id = main_query.accred_version_id and p.p_id = a.p_id ) "TotAvail"

Naveen Kumar

try :

(Select count(a.version_id) ,
Case When 0 THEN 0
WHEN ( ( (ptl.Min_Rank is not null AND ptl.Max_Rank is not null)
AND ( replace(p.pay_grade,'-') in
(select pl.rank from minmax_rank_lookup pl where rank_range Between
(select rank_range from minmax_rank_lookup where rank = ptl.Min_Rank) AND
(select rank_range from minmax_rank_lookup where rank = ptl.Max_Rank) ) ) )
AND ((ptl.Rate_Codes is not null AND p.rating = ptl.Rate_Codes) OR ( ptl.Rate_Codes is null))
AND (trunc(a.expiration_date) >= trunc(to_date('12/01/2011','MM/DD/YYYY')) )
OR (ptl.Min_Rank is null AND ptl.Max_Rank is null AND ((ptl.Rate_Codes is null) OR (ptl.Rate_Codes is NOT null AND p.rating = ptl.Rate_Codes) ))
THEN count(p_id) ELSE 0
END
FROM accreditations a, persons p where a.accred_version_id = main_query.accred_version_id and p.p_id = a.p_id ) "TotAvail"

Can you provide the complete query so that it becomes to understand and help.

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cookiejar

ASKER

This is the entire query

Select mav1.name "Title",
to_number(p_required) "Reqd",
'12-DEC-11' "Test_Date",
(Select count(mtl_accred_version_id)
(Select Case
when count(a.version_id) = 0 THEN 0
WHEN ( ( (ptl.Min_Rank is not null AND ptl.Max_Rank is not null)
AND ( replace(p.pay_grade,'-') in
(select pl.rank from minmax_rank_lookup pl where rank_range Between
(select rank_range from minmax_rank_lookup where rank = ptl.Min_Rank) AND
(select rank_range from minmax_rank_lookup where rank = ptl.Max_Rank) ) ) )
AND ((ptl.Rate_Codes is not null AND p.rating = ptl.Rate_Codes) OR ( ptl.Rate_Codes is null))
AND (trunc(a.expiration_date) >= trunc(to_date('12/01/2011','MM/DD/YYYY')) )
OR (ptl.Min_Rank is null AND ptl.Max_Rank is null AND ((ptl.Rate_Codes is null) OR (ptl.Rate_Codes is NOT null AND p.rating = ptl.Rate_Codes) ))
THEN count(p_id) ELSE 0 END
FROM accreditations a, persons p where a.accred_version_id = mav1.accred_version_id and p.p_id = a.p_id )) "TotAvail"

FROM prdt_load ptl,
maccred_versions mav,
maccred_versions mav1

WHERE ptl.title_id =mav.title_id
AND mav.maccreditation_id = mav1.maccreditation_id
AND mav1.vstatus='ACTIVE'
AND ptl.category = 'TLE'

Group by mav1.name, p_required
Order by mav1.title

Naveen Kumar

So what is the error you are getting for this full query ?