mardi 20 août 2019

SQL Syntax error near IF with multiple AND/OR conditions

I'm working on a uni project and I have to create a procedure from which I can get:

  1. A list of all the appropriate candidates for a job according to the score they have
  2. A list of the rejected ones and
  3. If the whole process is not finished yet display a message.

This is my code, I think there is something wrong with the if statments but I don't understand exactly why, am I using some other statment wrong?

DELIMITER $

CREATE PROCEDURE choose_candidate(IN id_job INT(4))
BEGIN
DECLARE CandName VARCHAR(12);
DECLARE CandPers INT;
DECLARE CandEduc INT ;
DECLARE CandExp INT ;
DECLARE CandInter INT;
DECLARE CandScore INT;
DECLARE finishedflag INT;
DECLARE Finished INT;
DECLARE Flag INT;
IF ( ( interview.personality IS NULL) OR ( interview.experience IS NULL ) OR ( interview.education IS NULL ) ) THEN
 DECLARE candlist_cursorIII CURSOR FOR 
  SELECT cname
  FROM interview
  WHERE id_job=interview.inter_job; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET Flag=1;
 OPEN candlist_cursorIII;
 SET Flag=0;
 FETCH candlist_cursorIII INTO CandName;
 WHILE (Flag=0) DO
  SELECT CandName AS 'Evaluation not finished';
  FETCH candlist_cursorIII INTO CandName;
 END WHILE;
 CLOSE candlist_cursorIII;
ELSE IF ( ( interview.personality != 0 ) AND ( interview.education != 0 ) AND ( interview.experience !=0 ) ) THEN
 DECLARE candlist_cursor CURSOR FOR 
  SELECT cname, AVG(personality), education, experience, COUNT(cname), SUM(personality+education+experience)
  FROM interview
  WHERE id_job=interview.inter_job GROUP BY cname ORDER BY SUM(personality+education+experience) DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finishedflag=1;
 OPEN candlist_cursor;
 SET finishedflag=0;
 FETCH candlist_cursor INTO CandName, CandPers, CandEduc, CandExp, CandInter, CandScore;
 WHILE (finishedflag=0) DO
  SELECT CandName AS 'NAME', CandPers AS 'PERSONALITY', CandEduc AS 'EDUCATION', CandExp AS 'EXPERIENCE', CandInter 'NUMBER_OF_INTERVIEWS', CandScore AS 'SCORE';
  FETCH candlist_cursor INTO CandName, CandPers, CandEduc, CandExp, CandInter, CandScore;
 END WHILE;
 CLOSE candlist_cursor;
ELSE IF ( ( interview.personality = 0 ) OR ( interview.education = 0 ) OR ( interview.experiece = 0 ) ) THEN
  DECLARE candlist_cursorII CURSOR FOR 
   SELECT cname
   FROM interview
   WHERE id_job=interview.inter_job;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished=1;
  OPEN candlist_cursorII;
  SET Finished=0;
  FETCH candlist_cursorII INTO CandName;
  WHILE (Finished=0) DO
   SELECT CandName AS 'NAME', CONCAT_WS (/, no prior experience, inadequeate education, failed the interview ) AS 'Rejection Reason';
   FETCH candlist_cursor INTO CandName;
  END WHILE;
  CLOSE candlist_cursorII;
END IF;  
END$

DELIMITER ;

Aucun commentaire:

Enregistrer un commentaire