A mate came to me with a simple SQL query... what she's asked, I can do in PHP'Mysql with my eyes closed, but she's given me code I don't recognse, and therefore, how to answer her.. Does the following message mean anything to anyone?
She basically need so to adda studnet attendance, based on a name and a date. and the SQl ias supposed to go find the student ID and update... -====================== SQL> describe student; Name Null? Type ----------------------------------------- -------- -------------- STUDENT_ID NOT NULL NUMBER FIRST_NAME VARCHAR2(15) LAST_NAME VARCHAR2(20) SEX CHAR(1) TITLE VARCHAR2(6) SQL> DESCRIBE Attendance; Name Null? Type ----------------------------------------- -------- ---------- STUDENT_ID NOT NULL NUMBER ATT_DATE DATE Those are my two tables. I have written one procedure that changes the title when it gets the name passed: CREATE OR REPLACE PROCEDURE addtitle(p_fn varchar2, p_ln varchar2) IS v_sex varchar2(1); v_sid number; BEGIN SELECT student_id, sex INTO v_sid, v_sex FROM student s WHERE first_name = p_fn AND last_name = p_ln; IF v_sex = 'M' THEN UPDATE student SET title = 'Mr' WHERE student_id = v_sid; ELSIF v_sex = 'F' THEN UPDATE student SET title = 'Ms' WHERE student_id = v_sid; END IF; COMMIT; END; / EXEC addtitle ('Tom', 'Smith') EXEC addtitle ('Sweta', 'Patel') And now I was asked to: Write a procedure to enter records in teh Attendance table when the student's name and date of attendance are passed to the procedure. And i have this, which doesn't work (not surprised!) CREATE OR REPLACE PROCEDURE addAttendance(p_fn varchar2, p_ln varchar2, p_ad date) IS v_att_date date; v_sid number; BEGIN SELECT student_id, att_date INTO v_sid, v_att_date FROM Attendance a, Student s WHERE first_name = p_fn AND last_name = p_ln; IF v_att_date IS NULL THEN UPDATE attendance SET Att_date = att_date WHERE student_id = v_sid; ELSE UPDATE attendance SET Att_date = Att_date WHERE student_id = v_sid; END IF; COMMIT; END; / Thanks for looking at this. I just tried to change the first one round but need to somehow join them to get at the student id and I can't do that at the best of times without procedures LINE/COL ERROR -------- ---------------------------------------------------- 7/1 PL/SQL: SQL Statement ignored 7/8 PL/SQL: ORA-00918: column ambiguously defined ======================= -- Give a man a fish and he'll feed himself for a day. Give a man a religion and he'll starve to death praying for a fish. Anon `We are what we pretend to be, so we must be careful what we pretend to be.` Kurt Vonnegut `When a person can no longer laugh at himself, it is time for others to laugh at him.` Thomas Szasz ____ � The WDVL Discussion List from WDVL.COM � ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.