Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Case in Where Clause--

SeverityNov 23 2011 — edited Nov 23 2011
Hi, i'm new in oracle programming and i have a problem with my query in oracle reports
the code is something like this
function CF_ITEMFormula return number is
v_item number(30);
begin
 IF :P_state <> ALL then
        IF :P_STATE = 'SMALL' THEN
        SELECT aa.item
        INTO v_item
        FROM tableA aa,
                 tableb bb
        WHERE aa.code = bb.code
        AND aa.date = :P_date
        AND aa.name = :P_name
        AND bb.state LIKE '%et%''
        AND (case :P_DIV
                when ('west') then aa.division = 1
                when ('east') then aa.division = 2
                when ('south') then aa.division = 3
                when ('south & west) then aa.division in (1,3)
                else aa.division in (1,2,3)
                end);
        end if;
       IF :P_STATE = 'BIG' THEN
        SELECT aa.item
        INTO v_item
        FROM tableA aa,
                 tableb bb
        WHERE aa.code = bb.code
        AND aa.date = :P_date
        AND aa.name = :P_name
        AND bb.state LIKE '%wa%''
        AND (case :P_DIV
                when ('west') then aa.division = 1
                when ('east') then aa.division = 2
                when ('south') then aa.division = 3
                when ('south & west) then aa.division in (1,3)
                else aa.division in (1,2,3)
                end);
        end if;
end if;
EXCEPTION
WHEN NULL THEN RETURN (0)
end;
when i complied it, it didn't produce error but when i executed the report a pop up massages showed fatal error in pl/sql CF_ITEMFormula
so would someone help me to correct this query

thanks

Hendra
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 21 2011
Added on Nov 23 2011
7 comments
91 views