C est une énorme requête. En exécutant la procédure suivante, cela montre que

# 1064 - Vous avez une erreur dans votre syntaxe SQL; Vérifiez le manuel qui correspond à la version de votre serveur MySQL pour la bonne syntaxe à utiliser à côté de 'DECLARE i integer' à la ligne 2.

Quelle est l'erreur dans ma requête.

DELIMITER $$
BEGIN
DECLARE i integer $$
DROP TEMPORARY TABLE IF EXISTS tmp_tags;     
CREATE TEMPORARY TABLE tmp_tags (
`id` int(11) NOT NULL AUTO_INCREMENT,
`org_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`tag_id` varchar(150) NOT NULL,PRIMARY KEY (`id`)
    );
  SET i = 1;
  REPEAT
  INSERT INTO tmp_tags ( org_id, user_id, tag_id)
      SELECT org_id,user_id ,strSplit(tag_id, ',', i) FROM tagging
      WHERE strSplit(tag_id, ',', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
  DROP TEMPORARY TABLE IF EXISTS tmp_tagged_org;
    IF userId IS NULL THEN      
    CREATE TEMPORARY TABLE tmp_tagged_org AS
SELECT GROUP_CONCAT( organization_tag.tag_name
SEPARATOR ', ' ) AS tags,tmp_tags.org_id AS tagged_org_id
FROM organization_tag
INNER JOIN tmp_tags ON organization_tag.id = tmp_tags.tag_id
GROUP BY tmp_tags.org_id;
SELECT calllist.id AS DT_RowId,tagging.tag_id, tmp_tagged_org.tags AS display_tag, organization.name, (

SELECT GROUP_CONCAT( CONCAT( CONCAT( contacts.first_name, ' ', contacts.last_name ), ':', contacts.phone )
SEPARATOR '<br> ' )
FROM contacts
WHERE organization.id = org_id
) AS
unames, CONCAT(calllist.call_back_date,' ' , if(calllist.call_back_date < now(), '<label style='position:absolute;'><i class='fa fa-warning' style='color:red'></i></label> ', '')) as calldate, IF(tmp_tagged_org.tags IS NULL,'<input type='hidden' class='servicetags' style=' width:150px'>',CONCAT('<input type='hidden' class='servicetags' value='',tmp_tagged_org.tags,'' style=' width:150px' >')) as tag_name
FROM calllist
INNER JOIN organization ON organization.id = calllist.org_id
LEFT JOIN tmp_tagged_org ON tmp_tagged_org.tagged_org_id =  calllist.org_id
LEFT JOIN tagging ON calllist.org_id = tagging.org_id
LEFT JOIN contacts ON organization.id = contacts.org_id
GROUP BY contacts.org_id order by calllist.id ;
  ELSE
    CREATE TEMPORARY TABLE tmp_tagged_org AS
SELECT GROUP_CONCAT( organization_tag.tag_name
SEPARATOR ', ' ) AS tags,tmp_tags.org_id AS tagged_org_id
FROM organization_tag
INNER JOIN tmp_tags ON organization_tag.id = tmp_tags.tag_id
GROUP BY tmp_tags.org_id;
SELECT calllist.id AS DT_RowId,tagging.tag_id, tmp_tagged_org.tags AS display_tag, organization.name, (
SELECT GROUP_CONCAT( CONCAT( CONCAT( contacts.first_name, ' ', contacts.last_name ), ':', contacts.phone )
SEPARATOR '<br> ' )
FROM contacts
WHERE organization.id = org_id
) AS
unames,CONCAT(calllist.call_back_date,' ' , if(calllist.call_back_date < now(), '<label style='position:absolute'><i class='fa fa-warning' style='color:red'></i></label>', ' ')) as calldate,IF(tmp_tagged_org.tags IS NULL,'<input type='hidden' class='servicetags' style='width:150px'>',CONCAT('<input type='hidden' class='servicetags' value='',tmp_tagged_org.tags,'' style=' width:150px' >')) as tag_name
FROM calllist
INNER JOIN organization ON organization.id = calllist.org_id AND organization.user_id = userId
LEFT JOIN tmp_tagged_org ON tmp_tagged_org.tagged_org_id =  calllist.org_id
LEFT JOIN tagging ON calllist.org_id = tagging.org_id
LEFT JOIN contacts ON organization.id = contacts.org_id
GROUP BY contacts.org_id order by calllist.id;  
  END IF;
END $$
DELIMITER;  
-3
Sree devi 31 déc. 2015 à 11:44

3 réponses

Meilleure réponse

Les premières lignes devraient être comme ci-dessous-

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_name`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_name`()
BEGIN
DECLARE i integer 
0
Zafar Malik 31 déc. 2015 à 09:01

$$ est le délimiteur qui sera utilisé pour fermer la définition de procédure stockée, il ne doit donc pas être immédiatement après votre déclaration (c'est-à-dire dans la procédure elle-même). Cette déclaration devrait toujours utiliser ;.

0
paxdiablo 31 déc. 2015 à 08:50

Modifier l'instruction Declare

 DECLARE i integer;
0
VIPAN SABHERWAL 31 déc. 2015 à 10:37