August 21st, 2011, 06:12 PM
Join Date: Aug 2011
Time spent in forums: 21 m 56 sec
Reputation 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.
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;
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,
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