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

    Join Date
    Aug 2011
    Rep Power

    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.

    ACCEPT 'p_make' PROMPT 'Enter the make:' 
    ACCEPT 'p_model' PROMPT 'Enter the model:'
    VARIABLE g_output VARCHAR2(2000);
       v_count NUMBER(6);
       v_average NUMBER(8,2);
       v_toolow EXCEPTION;
       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;
       WHEN v_toolow THEN
       :g_output := '&p_make' || ' ' || '&p_model' || ' ' || '# visits: ' || ' ' || v_count;
    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.

    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)
    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
    Rep Power
    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