Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: PLS - 00103 error

    hi why is this giving me an error, please help


    DECLARE
    lv_total_num NUMBER(6,2);
    lv_rating_txt VARCHAR2(4);
    lv_eg_txt VARCHAR2(10) ;

    BEGIN
    SELECT SUM(total)
    INTO lv_total_num
    FROM bb_basket
    WHERE idShopper = :g_shopper
    AND orderplaced = 1
    GROUP BY idshopper;
    Case lv_total_num
    WHEN lv_total_num > 200 THEN lv_eg_txt := 'HIGH';
    WHEN lv_total_num > 100 THEN lv_eg_txt := 'MID';
    WHEN lv_total_num < 100 THEN lv_eg_txt := 'LOW';
    ELSE
    END CASE;
    DBMS_OUTPUT.PUT_LINE( 'Shopper '||:g_shopper||' is rated '||lv_rating_txt);
    END;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >WHERE idShopper = :g_shopper
    Interesting code.
    what is ":g_shopper"?

    Code:
    SQL> DECLARE
      2  lv_total_num NUMBER(6,2);
      3  lv_rating_txt VARCHAR2(4);
      4  lv_eg_txt VARCHAR2(10) ;
      5  
      6  BEGIN
      7  SELECT SUM(total)
      8  INTO lv_total_num
      9  FROM bb_basket
     10  WHERE idShopper = :g_shopper
     11  AND orderplaced = 1
     12  GROUP BY idshopper;
     13  Case lv_total_num
     14  WHEN lv_total_num > 200 THEN lv_eg_txt := 'HIGH';
     15  WHEN lv_total_num > 100 THEN lv_eg_txt := 'MID';
     16  WHEN lv_total_num < 100 THEN lv_eg_txt := 'LOW';
     17  ELSE
     18  END CASE;
     19  DBMS_OUTPUT.PUT_LINE( 'Shopper '||:g_shopper||' is rated '||lv_rating_txt);
     20  END;
     21  /
    SP2-0552: Bind variable "G_SHOPPER" not declared.
    You might want to either remove/eliminate the "ELSE" at line #17
    or include/add a valid statement between #17 & #18 above.
    Last edited by anacedent; 10-03-09 at 20:31.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2009
    Posts
    4
    for g_shopper the code
    is
    VARIABLE g_shopper NUMBER
    :g_shopper := 22;
    END;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •