#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    ORA-00907: missing right parenthesis in CASE


    I don't see where my error is? How could I be missing a right parenthesis?
    PHP Code:
    /* --Get Majors--*/ 
     
    INSERT INTO PS_NTSR_GF_MAJFILE A 
     SELECT 
    %RunControl 
     
    ,%Bind(NTSR_GDFT_AET.INSTITUTION
     ,
    'major' 
     
    ,'add' 
     
    ,ACAD_PLAN 
     
    ,DESCR  
      FROM 

                 
    SELECT ACAD_PLANDESCR 
                  FROM PS_ACAD_PLAN_TBL B
                  WHERE B
    .ACAD_PLAN_TYPE 'MAJ'
                
    AND B.EFF_STATUS 'A'
                
    AND B.ACAD_PROG BETWEEN
                                            
    (CASE PROG 
                                            WHEN 
    ( %Bind(NTSR_GDFT_AET.INSTITUTION) = 'DL773'THEN '500' AND '699'
                                            
    WHEN ( %Bind(NTSR_GDFT_AET.INSTITUTION) = 'NT752'THEN '100' AND '999' 
                                            
    END
                                            
    )
                AND 
    B.EFFDT = (
                                         
    SELECT MAX(BB.EFFDT)
                                          
    FROM PS_ACAD_PLAN_TBL BB
                                          WHERE B
    .ACAD_PLAN BB.ACAD_PLAN
                                          
    AND B.EFF_STATUS BB.EFF_STATUS
                                          
    AND B.ACAD_PLAN_TYPE BB.ACAD_PLAN_TYPE 
                                         
    AND BB.EFFDT <= sysdate
                 AND 
    B.INSTITUTION = %Bind(NTSR_GDFT_AET.INSTITUTION)  
                  ) 
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    This one
    Code:
    WHEN ( %Bind(NTSR_GDFT_AET.INSTITUTION) = 'DL773') THEN '500' AND '699'
    is wrong.

    The "AND '699'" is not allowed there.
    What are you trying to do?

    Additionally the BETWEEN operator expects two expression. You only have one expression (the malformed CASE)
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    I'm trying to select all ACAD_PLAN, DESCR depending on what institution is selected. If it is DL773 then I want only those acad_prog between '500' AND '699'. If it is NT752 then I want all of them from '100' to '999'
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by SFDonovan
    I'm trying to select all ACAD_PLAN, DESCR depending on what institution is selected. If it is DL773 then I want only those acad_prog between '500' AND '699'. If it is NT752 then I want all of them from '100' to '999'
    Something like
    Code:
    AND (    
          (%Bind(NTSR_GDFT_AET.INSTITUTION) = 'DL773' AND B.ACAD_PROG BETWEEN '500' AND '699')
      OR  (%Bind(NTSR_GDFT_AET.INSTITUTION) = 'NT752' AND B.ACAD_PROG BETWEEN '100' AND '999')
    )

IMN logo majestic logo threadwatch logo seochat tools logo