[thelist] SQL - hardcore... at least it looks that way to me,...

[Tris]

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