|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
New to Oracle9i and in need of help? I am trying to added a column to the mm_movie table named stk_flag that will hold a value of '*' if stock is $75 or more .Othrwise the value is NULL I am to added the column and create a anonymous block tha contains a CURSOR FOR loop to accompish tha task.
DECLARE CURSOR nmovie_qty IS Select value, price from mm_movie FOR UPDATE NOWAIT; lv_stock mm_movie.stk_flag%TYPE; BEGIN FOR rec_prod IN nmovie_qty LOOP IF rec_prod.value >= 75 THEN lv_stock := rec.prod.price '*'; END IF; IF rec_prod.vlaue < 75 THEN lv_stock := rec.prod.price null; END IF; UPDATE mm_movie SET stk_flag = lv_stock WHERE CURRENT OF nmovie_qty = 75; END LOOP; COMMIT; END; / ERROR MESSAGE IS as : rec.prod.price '*'; * ERROR at line 10: ORA-06550: line 10, column 40: PLS-00103: Encountered the symbol "*" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol "." was substituted for "*" to continue. ORA-06550: line 13, column 42: PLS-00103: Encountered the symbol "NULL" when expecting one of the following: . ( * @ % & = - + ; < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol "*" was substituted for " ORA-06550: line 17, column 34: PLS-00103: Encountered the symbol "=" when expecting one of the following: . ( % ; |
|
#2
|
|||
|
|||
|
Code:
UPDATE mm_movie SET stk_flag = case when value >= 75 then '*' end |
|
#3
|
||||
|
||||
|
Quote:
Bleah ... too easy Probably he prefers writing queries like "Select ... where stk_flag = '*'" over "Select ... where value > 75" ![]()
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias) |
|
#4
|
|||
|
|||
|
Question ?
Quote:
Pablo ? what am I doing wrong ? UPDATE mm_movie SET STK_FLAG = case When value >= 75 then '*' end After making the change then my DECLARE statement has a * errors? / DECLARE CURSOR nmovie_qty IS Select value, price from mm_movie FOR UPDATE NOWAIT; lv_stock mm_movie.stk_flag%TYPE; BEGIN FOR rec_prod IN nmovie_qty LOOP IF rec_prod.value >= 75 THEN lv_stock := rec.prod.price '*'; END IF; IF rec_prod.vlaue < 75 THEN lv_stock := rec.prod.price null; END IF; UPDATE mm_movie SET stk_flag = lv_stock WHERE CURRENT OF nmovie_qty = 75; END LOOP; COMMIT; END; / |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Anonymous block and CURSOR |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|