CODEWITHSUNDEEP
sqloracle-databaseplsql
Doc Holiday
Doc Holiday

Reputation: 10254

PL/SQL: ORA-00933: SQL command not properly ended

I’m trying to do an IF/ELSE in my WHERE clause of the procedure but doesn’t seem to job right. I’m not sure if I'm structuring it right.

I get PL/SQL: ORA-00933: SQL command not properly ended at "IF p_job_year IS NOT NULL THEN"

This is what I've got:

 WHERE 
    (val1s.val1 = p_val1 OR p_val1 IS NULL) AND
    (UPPER(val2s.val2) LIKE UPPER(p_val2) OR p_val2 IS NULL) AND
    (jp.row_top.job_type_id = p_job_type_id OR p_job_type_id IS NULL) 

    AND ntab.group_id = pgds.id(+) 
    AND jp.row_top.val1_id = val1s.id(+) 
    AND val1s.val2_id = val2s.id(+)

 IF p_job_year IS NOT NULL THEN <<<<<<<< ERROR HERE
            AND p_job_year = ntab.fiscal_year(+)
            AND jp.jobload_year =  p_job_year
            AND jp.row_top.fiscal_year = p_job_year
  ELSE
        AND jp.jobload_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY') 
        AND jp.row_top.fiscal_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY')


END IF;

    AND JP.VER = (select MAX(jp2.ver) from job_plans jp2
                                                    WHERE JP.ID_NUMBER = JP2.ID_NUMBER)
    AND  jack_work_pkg.get_last_jack_work_first_type(jp.id) != 1;                                                
          RETURN result_cur;
  END summarize_work_data;

Upvotes: 2

Views: 2907

Answers (5)

bpgergo
bpgergo

Reputation: 16037

What makes you think that you can write an IF / ELSE / END IF into a WHERE clause?

Basically what you can add to the WHERE clause is something like this:

AND (
    (p_job_year IS NOT NULL AND (
            AND p_job_year = ntab.fiscal_year(+)
            AND jp.jobload_year =  p_job_year
            AND jp.row_top.fiscal_year = p_job_year))
    OR (p_job_year IS NULL AND (
        AND jp.jobload_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY') 
        AND jp.row_top.fiscal_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY')))
)

Upvotes: 0

Andrew
Andrew

Reputation: 27294

You can't use an IF clause in that way, you have to construct the where clause slightly differently.

AND
(
    (
        p_job_year IS NULL
        AND jp.jobload_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY')          
        AND jp.row_top.fiscal_year < TO_CHAR(ADD_MONTHS(CURRENT_DATE, -24),'YYYY') 
    ) 
    OR
    (
        p_job_year is not NULL
        AND p_job_year = ntab.fiscal_year(+)             
        AND jp.jobload_year =  p_job_year             
        AND jp.row_top.fiscal_year = p_job_year   
    )
)

Upvotes: 3

Yahia
Yahia

Reputation: 70369

There is no IF THEN ELSE inside a WHERE clause - but you can compensate by using CASE WHEN THEN ELSE if need be...

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

I am assuming this entire block is only the where clause. If that's the case, you cannot use "IF..ELSE" within a where clause.

Instead of using

Where condition1
  and condition2
  and IF Condition3 then
      AND condition4...
      ELSE
          condition5
  and <.....>

rewrite your query like this..just using AND and OR and parentheses as needed

Where condition1
  and condition2
  and ((Condition3 and condition4) or (condition5))
  and <.....>

Upvotes: 0

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120917

Well, it's not as simple as you think. You cannot create dynamic queries this way. An option is to create your query as a string, and use execute immediate to run it.

Upvotes: -1

Related Questions