August 21st, 2011, 06:12 PM
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.
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.
ACCEPT 'p_make' PROMPT 'Enter the make:'
ACCEPT 'p_model' PROMPT 'Enter the model:'
VARIABLE g_output VARCHAR2(2000);
SELECT COUNT(DISTINCT servinv), AVG(totalcost)
INTO v_count, v_average
WHERE serial IN
WHERE 'p_make' = make AND 'p_model'= model);
IF v_count < 3 THEN
: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;
CREATE TABLE servinv
servinv CHAR(5) PRIMARY KEY,
cname CHAR(20) NOT NULL REFERENCES customer(cname),
serial CHAR(8) NOT NULL REFERENCES car(serial),
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
August 23rd, 2011, 12:35 PM
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.