#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    2
    Rep Power
    0

    SQL query question


    Hi, I've written this code for my pl/sql class and it runs fine. The only problem is that no matter what I input I always get the v_toolow exception which makes me sure my SELECT statement is not right, since I looked in the database and it intentionally put in data that should not trigger the exception. I would really appreciate some help. I have no idea what I'm doing wrong. With all of the other error I have made during this class I got error messages. This seems to be working just not correctly.

    Code:
    ACCEPT 'p_make' PROMPT 'Enter the make:' 
    ACCEPT 'p_model' PROMPT 'Enter the model:'
    VARIABLE g_output VARCHAR2(2000);
    DECLARE
       v_count NUMBER(6);
       v_average NUMBER(8,2);
       v_toolow EXCEPTION;
    BEGIN
       SELECT COUNT(DISTINCT servinv), AVG(totalcost)
          INTO v_count, v_average
          FROM s1.servinv
       WHERE serial IN
          (SELECT serial 
             FROM s1.car
           WHERE 'p_make' = make AND 'p_model'= model);
          IF v_count < 3 THEN
             RAISE v_toolow;
          END IF;
        :g_output := '&p_make' || ' ' || '&p_model' || ' ' || '# visits: ' || ' ' || v_count || ' ' || 'average $ per visit: ' || ' ' || v_average;
    EXCEPTION
       WHEN v_toolow THEN
       :g_output := '&p_make' || ' ' || '&p_model' || ' ' || '# visits: ' || ' ' || v_count;
    
    END;
    /
    
    Print :g_output;
    ps, I'm including a copy of the tables invovled in this query, but don't worry the S1 schema is the one we are using.
    thanks,
    Michelle

    CREATE TABLE servinv
    (
    servinv CHAR(5) PRIMARY KEY,
    serdate DATE,
    cname CHAR(20) NOT NULL REFERENCES customer(cname),
    serial CHAR(8) NOT NULL REFERENCES car(serial),
    partscost NUMBER(7,2),
    laborcost NUMBER(7,2),
    tax NUMBER(6,2),
    totalcost NUMBER(8,2)
    );
    CREATE TABLE car
    (
    serial CHAR(8) PRIMARY KEY,
    cname CHAR(20) REFERENCES customer(cname),
    make CHAR(10) NOT NULL,
    model CHAR(8) NOT NULL,
    cyear CHAR(4) NOT NULL
    );
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    140
    Rep Power
    14
    Are you sure you're getting the custom exception? Maybe you should add a 'WHEN OTHERS THEN' clause in your exception block.

    You should also check the query outside of PL/SQL, and see what data you actually have with that query and the parameters you enter.

IMN logo majestic logo threadwatch logo seochat tools logo