lundi 29 décembre 2014

mysql ERROR 1248: Every derived table must have its own alias



-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_MissingBooks`(p_UsrID BIGINT ,
p_Product VARCHAR(20))
BEGIN


DECLARE p_LinkedBoard INT;
DECLARE p_LinkedSubjects INT;
DECLARE p_LinkedClasses INT;
DECLARE p_LinkedBoards INT;
DECLARE p_SchoolBooks INT;

SELECT COUNT(*) INTO p_LinkedBoard
FROM userboards
WHERE userboards.usrid = p_UsrID;
SELECT COUNT(*) INTO p_LinkedClasses
FROM userclasses
WHERE userclasses.UsrID = p_UsrID;
SELECT COUNT(*) INTO p_LinkedSubjects
FROM usersubjects
WHERE usersubjects.usrid = p_UsrID;
SELECT COUNT(*) INTO p_LinkedBoards
FROM schoolboards;
SELECT COUNT(*) INTO p_SchoolBooks
FROM SchoolBooks;
Select * From temp_MatchList;

CREATE TEMPORARY TABLE temp_MatchList(ID BIGINT ,
Year INT ,
Name NVARCHAR(100) ,
SyllabusDisplayName NVARCHAR(100) ,
BookType NVARCHAR(20) ,
ClassName NVARCHAR(20) ,
ClassID INT ,
ClassNumber INT ,
BookName NVARCHAR(100) ,
Board NVARCHAR(100) ,
Publisher NVARCHAR(100) ,
SubjectName NVARCHAR(100) ,
SubjectID INT ,
BoardID INT ,
STATUS CHAR(1) ,
BoardStatus CHAR(1)
);


INSERT INTO temp_MatchList
SELECT * FROM
(
SELECT vBooksDetails.*
FROM
(
SELECT DISTINCT * FROM
(
SELECT
classmaster.ClassID AS ClassID,
CDisplayName,
subjectmaster.id ,
SubjectID,
subjectmaster.DispName ,
BookName
FROM classmaster
JOIN subjectclasslinkage ON
classmaster.ClassID = subjectclasslinkage.ClassNumber
JOIN subjectmaster ON
subjectclasslinkage.SubjectId = subjectmaster.id
LEFT JOIN
(
SELECT * FROM vBooksDetails
WHERE
(
(
(p_LinkedBoard = 0 ) OR (BoardID IN (SELECT boardname
FROM userboards WHERE userboards.usrid = p_UsrID )))
AND ((p_LinkedBoards = 0 ) OR (BoardID IN (SELECT BoardID FROM schoolboards )))
AND EXISTS (SELECT * FROM vBoardInfo WHERE ID = BoardID )AND vBooksDetails.Status = 'A'))
lessonsyllabus ON subjectmaster.id = lessonsyllabus.SubjectID AND
lessonsyllabus.ClassID = classmaster.ClassID ) TAB WHERE TAB.BookName IS NULL ) TAB_MISSING
JOIN vBooksDetails ON TAB_MISSING.ClassID = vBooksDetails.ClassID
AND TAB_MISSING.SubjectID = vBooksDetails.SubjectID AND vBooksDetails.BoardID = 3
WHERE vBooksDetails.Status = 'A' AND ((p_LinkedClasses = 0 )
OR ( vBooksDetails.ClassID IN(SELECT classid FROM userclasses WHERE UsrID = p_UsrID )))
AND (( p_LinkedSubjects = 0 ) OR (vBooksDetails.SubjectID IN (SELECT subjectid FROM usersubjects
WHERE usrid = p_UsrID )))
-- AND ((@LinkedBoard = 0) OR (BoardID IN (SELECT boardname FROM userboards WHERE usrid=@UsrID)) )
AND ((p_SchoolBooks = 0 ) OR ( vBooksDetails.Id NOT IN ( SELECT BookID FROM SchoolBooks))));

END




Aucun commentaire:

Enregistrer un commentaire