Forum Stats

  • 3,782,455 Users
  • 2,254,650 Discussions
  • 7,880,081 Comments

Discussions

Categories

PL/SQL error PLS-00103

654774
654774 Member Posts: 220
edited Sep 25, 2008 10:43AM in SQL & PL/SQL
Hi Everyone,

This is a piece of PL/SQL that i have been working on. It is designed to update consignment figures by a percentage. the user will define wether the change is to be a plus or a minus.

BEGIN
IF :p6_maths=- then
update concount_forecast_data_copy
set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent where country_name=:p2_country and week_no =:p2_week_no and year =:p2_year;
ELSE
update concount_forecast_data_copy
set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent where country_name=:p2_country and week_no =:p2_week_no and year =:p2_year;
ENDIF;
END;

However it does not compile. The error i get is:

ORA-06550: line 2, column 17: PLS-00103: Encountered the symbol "THEN" when expecting one of the following: ( - + case mod new null avg count current max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

1. BEGIN
2. if :p6_maths=- then
3. update concount_forecast_data_copy
4. set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent where country_name=:p2_country and week_no =:p2_week_no and year =:p2_year;

I am not that experienced in PL/SQL and any help as to where i am going wrong would be appritiated :)

-N.S.N.O.
· Share on TwitterShare on Facebook

Answers

  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Try to use this ->
    BEGIN
 IF :p6_maths = '-' then
    update concount_forecast_data_copy
    set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent 
    where country_name=:p2_country 
    and week_no =:p2_week_no 
    and year =:p2_year;
 ELSE
    update concount_forecast_data_copy
    set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent 
    where country_name=:p2_country 
    and week_no =:p2_week_no 
    and year =:p2_year;
 ENDIF;
END;
    N.B.: Not tested....

    You cannot directly put minus sign there after your equal operator in your first if statement.

    Regards.

    Satyaki De.
    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,623 Red Diamond
    Try...
    IF :p6_maths='-' then
    with quotes around your minus sign
    · Share on TwitterShare on Facebook
  • 654774
    654774 Member Posts: 220
    Thanks guys,

    Although i do get a slightly less offensive error. It is......

    ORA-06550: line 14, column 4:
    PLS-00103: Encountered the symbol ";" when expecting one of the following:

    if
    *1. BEGIN*
    *2. IF :p6_maths= '-' THEN*
    *3. update concount_forecast_data_copy*

    any ideas?

    -N.S.N.O.
    · Share on TwitterShare on Facebook
  • Sven W.
    Sven W. Member Posts: 10,534 Gold Crown
    edited Sep 19, 2008 10:45AM
    ENDIF; => VB Code

    END IF; => Pl/SQL Code

    you could even forget the whole if condition and do a single update:
    update concount_forecast_data_copy
   set MANUAL_ENTRY_CONS = MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*:p2_percent, 
        CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*:p2_percent 
where country_name=:p2_country 
and week_no =:p2_week_no 
and year =:p2_year;
    Note: There is no difference between your two update versions.
    If you want to substract when minus and add if not then the following coud help:
    update concount_forecast_data_copy
   set MANUAL_ENTRY_CONS = MANUAL_ENTRY_CONS +decode(:p6_maths,'-',-1,1) * (MANUAL_ENTRY_CONS/100)*:p2_percent
       ,CITEDI_CONS   = CITEDI_CONS +decode(:p6_maths,'-',-1,1) * (CITEDI_CONS/100)*:p2_percent 
where country_name=:p2_country 
and week_no =:p2_week_no 
and year =:p2_year;
    Edited by: Sven W. on Sep 19, 2008 4:37 PM

    Edited by: Sven W. on Sep 19, 2008 4:41 PM
    · Share on TwitterShare on Facebook
  • 654774
    654774 Member Posts: 220
    Hey Guys,

    I forgot about this thread :P

    This is how i got around the problem.......

    BEGIN
    IF :p9_maths=0 THEN
    update corcon01_forecast_data
    set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS - (MANUAL_ENTRY_CONS/100)*wwv_flow.g_x01, CITEDI_CONS=CITEDI_CONS - (CITEDI_CONS/100)*wwv_flow.g_x01
    where country_name=wwv_flow.g_x02 and week_no =wwv_flow.g_x03 and year =wwv_flow.g_x04;
    ELSE
    update corcon01_forecast_data
    set MANUAL_ENTRY_CONS=MANUAL_ENTRY_CONS + (MANUAL_ENTRY_CONS/100)*wwv_flow.g_x01, CITEDI_CONS=CITEDI_CONS + (CITEDI_CONS/100)*wwv_flow.g_x01
    where country_name=wwv_flow.g_x02 and week_no =wwv_flow.g_x03 and year =wwv_flow.g_x04;
    END IF;
    END;

    I created a select list called P9_MATHS which gave back a value of either '0' or '1'

    In my first post the SQL did have a error. I wanted the first statement totake away and the second to add. This was just a typo.

    -N.S.N.O.
    · Share on TwitterShare on Facebook
This discussion has been closed.