• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

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"


0
cookiejar
Asked:
cookiejar
  • 3
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
or may be this...

(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 = main_query.accred_version_id and p.p_id  =  a.p_id    ) "TotAvail"

0
 
cookiejarAuthor Commented:
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
 
   
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
So what is the error you are getting for this full query ?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now