sql - Getting an error when creating a calculated column within a query

Question

I getting this error message when I execute the code below. What I am trying to get is use the subquery results as my results set for the outer query, my main goal is to create the column defined in the case statement.

Error:

Error report -
SQL Error: ORA-00923: FROM keyword not found where expected
 1. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:

Code:

SET DEFINE OFF

SET DEFINE OFF




 CREATE OR REPLACE FORCE EDITIONABLE VIEW "GINSOBADEV_DW"."V_EVAL_QUESTIONNAIRE_F6"("EVAL_PARENT_PI_NUMBER", "AD_RP_CI", "AD_RP_CI2", "AD_JOB_Interest", "AD_JOB_Interest2", "AD_PO", "AD_PO2", "AD_COMPREHENSION_M", "AD_COMPREHENSION_M2", "AD_DETAIL_A", "AD_DETAIL_A2", "AD_NEGOTIATION_P", "AD_NEGOTIATION_P2", "AD_ORGANIZATION_TM", "AD_ORGANIZATION_TM2", "AD_REASONING", "AD_REASONING2", "AD_VERBAL", "AD_VERBAL2", "AD_NON_VERBAL", "AD_NON_VERBAL2", "AD_RP_NonVerbal", "Verb_NonVerb_Index", "Raw_Int_Rp_Score", 
"Int_RP_Quartile","Count_LowRatings1", "Count_LowRatings2", "SERVICE_RP_ACCURACY", "SERVICE_RP_CI","SERVICE_RP_PERSUASIVENESS", "HR_SERVICE_ORIENTATION", "HR_JOB_INTEREST", "HR_PO", "HR_PERSUASION", "SALES_RP_CI", "SALES_RP_CIS", "CSR_RP_DM", "CSR_RP_PERSUASION", "SALES_RP_PERSUASION", "CSR_RP_SO") AS 


SELECT 
a.EVAL_PARENT_PI_NUMBER,
a.AD_RP_CI,
a.AD_RP_CI2, 
a.AD_JOB_Interest, 
a.AD_JOB_Interest2, 
a.AD_PO, 
a.AD_PO2, 
a.AD_COMPREHENSION_M, 
a.AD_COMPREHENSION_M2,
a.AD_DETAIL_A,
a.AD_DETAIL_A2,
a.AD_NEGOTIATION_P
a.AD_NEGOTIATION_P2,
a.AD_ORGANIZATION_TM,
a.AD_ORGANIZATION_TM2, 
a.AD_REASONING, 
a.AD_REASONING2, 
a.AD_VERBAL, 
a.AD_VERBAL2,
a.AD_NON_VERBAL,
a.AD_NON_VERBAL2,
a.AD_RP_NonVerbal,
a.V.erb_NonVerb_Index,
Raw_Int_Rp_Score, 
( CASE        
WHEN a.Raw_Int_Rp_Score <= -.388826 THEN 1
WHEN a.Raw_Int_Rp_Score BETWEEN -.388825999 AND -.004742 THEN 2
WHEN a.Raw_Int_Rp_Score BETWEEN -.063390 AND .706751 THEN 3
WHEN a.Raw_Int_Rp_Score >= .706752  THEN 4
END )  AS Int_RP_Quartile,
a.Count_LowRatings1, 
a.Count_LowRatings2,
a.SERVICE_RP_ACCURACY,
a.SERVICE_RP_CI, 
a.SERVICE_RP_PERSUASIVENESS,
a.HR_SERVICE_ORIENTATION,
a.HR_JOB_INTEREST, 
a.HR_PO, 
a.HR_PERSUASION,
a.SALES_RP_CI,
a.SALES_RP_CIS,
a.CSR_RP_DM,
a.CSR_RP_PERSUASION,
a.SALES_RP_PERSUASION,
a.CSR_RP_SO

  FROM (

  SELECT aa.PARENT_PI_NUMBER EVAL_PARENT_PI_NUMBER,  


                  /*max (
                                CASE
                                WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Call Center Manager Interview' 
                                AND aa.SBM_EVAL_QUES_DESC_BL = 'Interviewer 1 (Enter your associate ID if you conducted the interview.  Or enter the correct associate ID if someone else conducted the interview):') 
                                THEN
                                                aa.SBM_EVAL_ANS_TXT_DT
                                END) As Manager_ID,*/
                /* MAX  (
    CASE
                                WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Call Center Manager Interview' 
                                AND aa.SBM_EVAL_QUES_DESC_BL = 'Date Evaluation Completed')
                                THEN
                                TO_CHAR(TO_DATE(aa.SBM_EVAL_ANS_TXT_DT,'MM-DD-YYYY'),'DD-MON-YYYY')





        END) As MI_Date,*/

        MAX(
      CASE 
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Auto Damage Interview'
        AND  aa.SBM_EVAL_QUES_DESC_BL  = 'Customer Interaction')
        THEN aa.SBM_EVAL_ANS_DESC_BL END) AS AD_RP_CI, 


                            MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Auto Damage Interview'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Customer Interaction')
        THEN CASE
when aa.SBM_EVAL_ANS_DESC_BL = '1   (Poor)' THEN 1
when aa.SBM_EVAL_ANS_DESC_BL = '1+ (Poor Plus)' THEN 2
when aa.SBM_EVAL_ANS_DESC_BL = '2-  (Weak Minus)' THEN 3
when aa.SBM_EVAL_ANS_DESC_BL = '2   (Weak)' THEN 4
when aa.SBM_EVAL_ANS_DESC_BL = '2+ (Weak Plus)' THEN 5
when aa.SBM_EVAL_ANS_DESC_BL = '3-  (OK Minus)' THEN 6
when aa.SBM_EVAL_ANS_DESC_BL IN( '3 (OK)', '3   (OK)') THEN 7
when aa.SBM_EVAL_ANS_DESC_BL = '3+ (OK Plus)' THEN 8
when aa.SBM_EVAL_ANS_DESC_BL = '4-  (Good Minus)' THEN 9
when aa.SBM_EVAL_ANS_DESC_BL = '4   (Good)' THEN 10
when aa.SBM_EVAL_ANS_DESC_BL = '4+ (Good Plus)' THEN 11
when aa.SBM_EVAL_ANS_DESC_BL = '5-  (Excellent Minus)' THEN 12
when aa.SBM_EVAL_ANS_DESC_BL = '5   (Excellent)' THEN 13
END 
      END) AS AD_RP_CI2,   


       MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Auto Damage Interview'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Job Interest')
        THEN aa.SBM_EVAL_ANS_DESC_BL
      END)  AS AD_JOB_Interest,


      MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Auto Damage Interview'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Job Interest')
        THEN CASE
when aa.SBM_EVAL_ANS_DESC_BL = '1   (Poor)' THEN 1
when aa.SBM_EVAL_ANS_DESC_BL = '1+ (Poor Plus)' THEN 2
when aa.SBM_EVAL_ANS_DESC_BL = '2-  (Weak Minus)' THEN 3
when aa.SBM_EVAL_ANS_DESC_BL = '2   (Weak)' THEN 4
when aa.SBM_EVAL_ANS_DESC_BL = '2+ (Weak Plus)' THEN 5
when aa.SBM_EVAL_ANS_DESC_BL = '3-  (OK Minus)' THEN 6
when aa.SBM_EVAL_ANS_DESC_BL IN( '3 (OK)','3   (OK)') THEN 7
when aa.SBM_EVAL_ANS_DESC_BL = '3+ (OK Plus)' THEN 8
when aa.SBM_EVAL_ANS_DESC_BL = '4-  (Good Minus)' THEN 9
when aa.SBM_EVAL_ANS_DESC_BL = '4   (Good)' THEN 10
when aa.SBM_EVAL_ANS_DESC_BL = '4+ (Good Plus)' THEN 11
when aa.SBM_EVAL_ANS_DESC_BL = '5-  (Excellent Minus)' THEN 12
when aa.SBM_EVAL_ANS_DESC_BL = '5   (Excellent)' THEN 13
END 
      END)  AS  AD_JOB_Interest2,      



    MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Auto Damage Interview'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Performance Orientation')
        THEN aa.SBM_EVAL_ANS_DESC_BL
      END)  AS AD_PO,   


      MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'Auto Damage Interview'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Performance Orientation')
        THEN CASE
when aa.SBM_EVAL_ANS_DESC_BL = '1   (Poor)' THEN 1
when aa.SBM_EVAL_ANS_DESC_BL = '1+ (Poor Plus)' THEN 2
when aa.SBM_EVAL_ANS_DESC_BL = '2-  (Weak Minus)' THEN 3
when aa.SBM_EVAL_ANS_DESC_BL = '2   (Weak)' THEN 4
when aa.SBM_EVAL_ANS_DESC_BL = '2+ (Weak Plus)' THEN 5
when aa.SBM_EVAL_ANS_DESC_BL = '3-  (OK Minus)' THEN 6
when aa.SBM_EVAL_ANS_DESC_BL IN( '3 (OK)','3   (OK)') THEN 7
when aa.SBM_EVAL_ANS_DESC_BL = '3+ (OK Plus)' THEN 8
when aa.SBM_EVAL_ANS_DESC_BL = '4-  (Good Minus)' THEN 9
when aa.SBM_EVAL_ANS_DESC_BL = '4   (Good)' THEN 10
when aa.SBM_EVAL_ANS_DESC_BL = '4+ (Good Plus)' THEN 11
when aa.SBM_EVAL_ANS_DESC_BL = '5-  (Excellent Minus)' THEN 12
when aa.SBM_EVAL_ANS_DESC_BL = '5   (Excellent)' THEN 13
END 
      END) AS  AD_PO2, 



        MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Comprehension & Memory')
        THEN aa.SBM_EVAL_ANS_DESC_BL
      END)  AS AD_COMPREHENSION_M,   



     MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Comprehension & Memory')
        THEN CASE
when aa.SBM_EVAL_ANS_DESC_BL = '1   (Poor)' THEN 1
when aa.SBM_EVAL_ANS_DESC_BL = '1+ (Poor Plus)' THEN 2
when aa.SBM_EVAL_ANS_DESC_BL = '2-  (Weak Minus)' THEN 3
when aa.SBM_EVAL_ANS_DESC_BL = '2   (Weak)' THEN 4
when aa.SBM_EVAL_ANS_DESC_BL = '2+ (Weak Plus)' THEN 5
when aa.SBM_EVAL_ANS_DESC_BL = '3-  (OK Minus)' THEN 6
when aa.SBM_EVAL_ANS_DESC_BL = '3   (OK)' THEN 7
when aa.SBM_EVAL_ANS_DESC_BL = '3+ (OK Plus)' THEN 8
when aa.SBM_EVAL_ANS_DESC_BL = '4-  (Good Minus)' THEN 9
when aa.SBM_EVAL_ANS_DESC_BL = '4   (Good)' THEN 10
when aa.SBM_EVAL_ANS_DESC_BL = '4+ (Good Plus)' THEN 11
when aa.SBM_EVAL_ANS_DESC_BL = '5-  (Excellent Minus)' THEN 12
when aa.SBM_EVAL_ANS_DESC_BL = '5   (Excellent)' THEN 13
END 
      END) AS  AD_COMPREHENSION_M2, 



      MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Detail & Accuracy')
        THEN aa.SBM_EVAL_ANS_DESC_BL
      END)  AS AD_DETAIL_A,   

      MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Detail & Accuracy')
        THEN CASE
when aa.SBM_EVAL_ANS_DESC_BL = '1   (Poor)' THEN 1
when aa.SBM_EVAL_ANS_DESC_BL = '1+ (Poor Plus)' THEN 2
when aa.SBM_EVAL_ANS_DESC_BL = '2-  (Weak Minus)' THEN 3
when aa.SBM_EVAL_ANS_DESC_BL = '2   (Weak)' THEN 4
when aa.SBM_EVAL_ANS_DESC_BL = '2+ (Weak Plus)' THEN 5
when aa.SBM_EVAL_ANS_DESC_BL = '3-  (OK Minus)' THEN 6
when aa.SBM_EVAL_ANS_DESC_BL = '3   (OK)' THEN 7
when aa.SBM_EVAL_ANS_DESC_BL = '3+ (OK Plus)' THEN 8
when aa.SBM_EVAL_ANS_DESC_BL = '4-  (Good Minus)' THEN 9
when aa.SBM_EVAL_ANS_DESC_BL = '4   (Good)' THEN 10
when aa.SBM_EVAL_ANS_DESC_BL = '4+ (Good Plus)' THEN 11
when aa.SBM_EVAL_ANS_DESC_BL = '5-  (Excellent Minus)' THEN 12
when aa.SBM_EVAL_ANS_DESC_BL = '5   (Excellent)' THEN 13
END 
      END)  AS  AD_DETAIL_A2, 






     MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Negotiation/Persuasion')
        THEN aa.SBM_EVAL_ANS_DESC_BL
      END)  AS AD_NEGOTIATION_P,   




       MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Negotiation/Persuasion')
        THEN CASE
when aa.SBM_EVAL_ANS_DESC_BL = '1   (Poor)' THEN 1
when aa.SBM_EVAL_ANS_DESC_BL = '1+ (Poor Plus)' THEN 2
when aa.SBM_EVAL_ANS_DESC_BL = '2-  (Weak Minus)' THEN 3
when aa.SBM_EVAL_ANS_DESC_BL = '2   (Weak)' THEN 4
when aa.SBM_EVAL_ANS_DESC_BL = '2+ (Weak Plus)' THEN 5
when aa.SBM_EVAL_ANS_DESC_BL = '3-  (OK Minus)' THEN 6
when aa.SBM_EVAL_ANS_DESC_BL = '3   (OK)' THEN 7
when aa.SBM_EVAL_ANS_DESC_BL = '3+ (OK Plus)' THEN 8
when aa.SBM_EVAL_ANS_DESC_BL = '4-  (Good Minus)' THEN 9
when aa.SBM_EVAL_ANS_DESC_BL = '4   (Good)' THEN 10
when aa.SBM_EVAL_ANS_DESC_BL = '4+ (Good Plus)' THEN 11
when aa.SBM_EVAL_ANS_DESC_BL = '5-  (Excellent Minus)' THEN 12
when aa.SBM_EVAL_ANS_DESC_BL = '5   (Excellent)' THEN 13
END 
      END)  AS  AD_NEGOTIATION_P2,


     MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Organization & Time Management')
        THEN aa.SBM_EVAL_ANS_DESC_BL
      END)  AS AD_ORGANIZATION_TM,  

       MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Organization & Time Management')
        THEN CASE
when aa.SBM_EVAL_ANS_DESC_BL = '1   (Poor)' THEN 1
when aa.SBM_EVAL_ANS_DESC_BL = '1+ (Poor Plus)' THEN 2
when aa.SBM_EVAL_ANS_DESC_BL = '2-  (Weak Minus)' THEN 3
when aa.SBM_EVAL_ANS_DESC_BL = '2   (Weak)' THEN 4
when aa.SBM_EVAL_ANS_DESC_BL = '2+ (Weak Plus)' THEN 5
when aa.SBM_EVAL_ANS_DESC_BL = '3-  (OK Minus)' THEN 6
when aa.SBM_EVAL_ANS_DESC_BL = '3   (OK)' THEN 7
when aa.SBM_EVAL_ANS_DESC_BL = '3+ (OK Plus)' THEN 8
when aa.SBM_EVAL_ANS_DESC_BL = '4-  (Good Minus)' THEN 9
when aa.SBM_EVAL_ANS_DESC_BL = '4   (Good)' THEN 10
when aa.SBM_EVAL_ANS_DESC_BL = '4+ (Good Plus)' THEN 11
when aa.SBM_EVAL_ANS_DESC_BL = '5-  (Excellent Minus)' THEN 12
when aa.SBM_EVAL_ANS_DESC_BL = '5   (Excellent)' THEN 13
END 
      END)  AS  AD_ORGANIZATION_TM2, 







      MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   = 'Reasoning')
        THEN aa.SBM_EVAL_ANS_DESC_BL
      END)  AS AD_REASONING, 


       MAX (
      CASE
        WHEN (aa.SBM_EVAL_QUES_CAT_BL = 'AD Assessment'
        AND aa.SBM_EVAL_QUES_DESC_BL   =

Answer 1

WAAAAAY down in your awesome long and tedious query there is the following code:

..END)-7.993250)/2.411327),0))/8 as Raw_Int_Rp_Score,


                (    
SELECT B.* FROM AAA_V_EVAL_QUESTIONNAIRE_BASE B



) aa )a
group by PARENT_PI_NUMBER;

First of all, your selected columns finish with a comma, which should not be. You should not have comma here:

as Raw_Int_Rp_Score,

Then, you are missing the from clause.

My best guess is that you should change it to this:

..END)-7.993250)/2.411327),0))/8 as Raw_Int_Rp_Score


                   FROM (    
    SELECT B.* FROM AAA_V_EVAL_QUESTIONNAIRE_BASE B



    ) aa )a