title: Oracle - SELECT CASE WHEN syntax
body: <p>I'm developing a JasperSoft report, and I'm currently getting a ORA-00905 error in my underlying Oracle SQL. The query editor doesn't seem to like this code. Is this syntactically correct, or is this erroneous? I can't seem to find out why I'm getting the ORA-00905 error as it's not pointing at any specific line in my code in JasperSoft Studio.</p>
  
  <pre><code>SELECT
      CASE WHEN $P{groupResManagerInvManager} = 'RESMGR'
      THEN r.resource_manager_key ELSE i.investment_manager_key END manager_key,
      CASE WHEN $P{groupResManagerInvManager} = 'RESMGR'
      THEN r.resource_manager ELSE i.investment_manager END manager,
      r.resource_key AS resource_key,            
      r.resource_name AS resource_name,
      te.investment_name AS investment_name,
      te.incident_name AS incident_name,       
      te.task_name AS task_name,
      t.wbs_sequence AS wbs_sequence,
      tln.task_status AS task_status,
      te.charge_code AS charge_code,
      te.input_type_code AS input_type_code,
      teln.time_record_type AS time_record_type, 
      CASE WHEN $P{firstDayOfWorkWeek} IS NULL
          THEN cfg.week_start_day
          ELSE CASE WHEN $P{firstDayOfWorkWeek} = 'WeekdayMonday' THEN 1
          WHEN $P{firstDayOfWorkWeek} = 'WeekdayTuesday'   THEN 2
          WHEN $P{firstDayOfWorkWeek} = 'WeekdayWednesday' THEN 3
          WHEN $P{firstDayOfWorkWeek} = 'WeekdayThursday'  THEN 4
          WHEN $P{firstDayOfWorkWeek} = 'WeekdayFriday'    THEN 5
          WHEN $P{firstDayOfWorkWeek} = 'WeekdaySaturday'  THEN 6
          WHEN $P{firstDayOfWorkWeek} = 'WeekdaySunday'    THEN 0
                   ELSE 1 END                    
              END AS week_start_day,              
      tp.start_date AS period_start_date,
      tp.finish_date  AS period_end_date,
      tsln.timesheet_status AS timesheet_status,   
      SUM(CASE WHEN $P!{weekDay} = 1  THEN pf.time_hours ELSE 0 END) day_1,
      SUM(CASE WHEN $P!{weekDay} = 2  THEN pf.time_hours ELSE 0 END) day_2,
      SUM(CASE WHEN $P!{weekDay} = 3  THEN pf.time_hours ELSE 0 END) day_3,
      SUM(CASE WHEN $P!{weekDay} = 4  THEN pf.time_hours ELSE 0 END) day_4,
      SUM(CASE WHEN $P!{weekDay} = 5  THEN pf.time_hours ELSE 0 END) day_5,
      SUM(CASE WHEN $P!{weekDay} = 6  THEN pf.time_hours ELSE 0 END) day_6,
      SUM(CASE WHEN $P!{weekDay} = 7  THEN pf.time_hours
      WHEN $P!{weekDay} = 0  THEN pf.time_hours ELSE 0 END) day_7               
  FROM   dwh_tme_period tp
         INNER JOIN  dwh_tme_sheet ts ON tp.time_period_key = ts.time_period_key
         INNER JOIN  dwh_res_resource r ON ts.resource_key = r.resource_key
         INNER JOIN  dwh_tme_sheet_ln tsln ON  tsln.timesheet_key = ts.timesheet_key
                                           AND tsln.language_code = $P{ppmUserLanguage} 
         INNER JOIN  dwh_tme_entry te ON ts.timesheet_key = te.timesheet_key
         INNER JOIN  dwh_tme_entry_ln teln ON  teln.timeentry_key = te.timeentry_key
                                           AND teln.language_code = $P{ppmUserLanguage} 
         INNER JOIN  dwh_tme_entry_facts pf ON  te.timeentry_key = pf.timeentry_key
         INNER JOIN  dwh_cfg_settings cfg ON 1=1              
         INNER JOIN  dwh_cmn_period c ON  c.period_key = pf.period_key   
                                      AND c.period_start_date BETWEEN $P!{dwhDBSchema}.dwh_cal_trunc_date_fct(tp.start_date)
                                                              AND     $P!{dwhDBSchema}.dwh_cal_trunc_date_fct(tp.finish_date)                                                
                                      AND c.period_type_key = 'DAILY'                                                                                     
         LEFT  OUTER JOIN dwh_inv_assignment a ON te.assignment_key = a.assignment_key
         LEFT  OUTER JOIN dwh_inv_task t ON te.task_key = t.task_key
         LEFT  OUTER JOIN dwh_inv_investment i ON te.investment_key = i.investment_key 
         LEFT  OUTER JOIN dwh_inv_task_ln tln ON  t.task_key = tln.task_key
                                              AND tln.language_code = $P{ppmUserLanguage} 
  WHERE  1=1
  AND    r.employment_type_key &lt;&gt; 0
  AND    r.resource_type_key = 0     
  AND   (r.is_active = CASE WHEN $P{includeInactiveResources} = 1 THEN r.is_active ELSE 1 END)                                        
  AND   (i.is_active = CASE WHEN $P{includeInactiveInvestments} = 1 THEN i.is_active ELSE 1 END)     
  AND    $X{IN, r.employment_type_key, employmentTypeKey_1}
  AND   ($X{IN, 'all', investmentTypeKey_1} OR $X{IN, i.investment_type_key, investmentTypeKey_1})
  AND    $X{IN, r.resource_key, resourceKey_1}
  AND    $X{IN, r.resource_manager_key, resourceManagerKey_1}
  AND    $X{IN, i.investment_key, investmentKeyNoOBS}
  AND    $X{IN, i.investment_manager_key, investmentManagerKey_1}
  AND    $X{IN, ts.timesheet_status_key, timesheetStatusKey_1}
  AND  (($X{IN, tp.time_period_key, timePeriod}) 
         OR    ($X{IN, -1, timePeriod}
                AND    tp.time_period_key = (SELECT a.time_period_key
                                             FROM   dwh_tme_period a 
                                             WHERE (SELECT $P!{dwhDBSchema}.dwh_cal_date_add_fct(b.start_date,'DAY',-1)
                                                    FROM   dwh_tme_period b
                                                    WHERE  $P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{today}) BETWEEN b.start_date AND b.finish_date) BETWEEN a.start_date AND a.finish_date))
         OR    ($X{IN, -2, timePeriod}
                AND    tp.time_period_key = (SELECT a.time_period_key 
                                             FROM   dwh_tme_period a 
                                             WHERE  $P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{today}) BETWEEN a.start_date AND a.finish_date))
         OR    ($X{IN, -3, timePeriod}
                AND    tp.time_period_key = (SELECT a.time_period_key
                                             FROM   dwh_tme_period a 
                                             WHERE (SELECT $P!{dwhDBSchema}.dwh_cal_date_add_fct(b.finish_date,'DAY',1)
                                                    FROM   dwh_tme_period b
                                                    WHERE  $P!{dwhDBSchema}.dwh_cal_trunc_date_fct($P{today}) BETWEEN b.start_date AND b.finish_date) BETWEEN a.start_date AND a.finish_date)))
  AND   ($P{resourceOBSUnitKey_1} IS NULL
         OR     
         r.resource_key IN (SELECT DISTINCT obsm.resource_key
                            FROM   dwh_res_obs_mapping obsm
                                   INNER JOIN dwh_cmn_obs_hierarchy obsh ON obsm.obs_unit_key = obsh.child_obs_unit_key
                            WHERE  obsh.parent_obs_unit_key = $P{resourceOBSUnitKey_1}))                                        
  AND    EXISTS (SELECT 1
                 FROM   dwh_res_security_v
                 WHERE  user_uid = $P{ppmUser}
                 AND    resource_key = r.resource_key)
  GROUP BY i.investment_manager_key, i.investment_manager, r.resource_manager_key, r.resource_manager,
           r.resource_key, r.resource_name, te.investment_name, te.task_name, t.wbs_sequence, tln.task_status, te.charge_code, te.input_type_code,
           cfg.week_start_day, tp.start_date, tp.finish_date, tsln.timesheet_status, te.incident_name, teln.time_record_type
  </code></pre>
  
  <p>For any unfamiliar with Jasper reports, the $P{} is a parameter/placeholder.</p>
  
  <p>Thanks</p>