Блог

У меня возникла проблема с командой sql, но она работала раньше, поэтому я не мог понять.

мой столик находится ;

     CREATE TABLE `cdrConfCall` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `callingNumber` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  `calledNumber` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateTimeConnect` int(11) DEFAULT NULL,
  `dateTimeDisconnect` int(11) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `origDeviceName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `destDeviceName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `jabber` int(11) DEFAULT NULL,
  `confid` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dateTimeConnectSTR` datetime DEFAULT NULL,
  `dateTimeDisconnectSTR` datetime DEFAULT NULL,
  `causeCode` int(11) DEFAULT NULL,
  `callingName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `calledName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
  `catCalling` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `catCalled` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ay` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 

команда sql;

 INSERT INTO CMOON.cdrConfCall (
                callingNumber,
                calledNumber,
                dateTimeConnect,
                dateTimeDisconnect,
                duration,
                origDeviceName,
                destDeviceName,
                jabber,
                confid,
                dateTimeConnectSTR,
                dateTimeDisconnectSTR) 
                SELECT * from
(select  '9999' as callingNumber ,'9995' as calledNumber,1625275399 as dateTimeConnect,1625275446 as dateTimeDisconnect,
'47' as duration,'xxxxx' as origDeviceName,'xxxxx' as destDeviceName,1 as jabber,'1625275399-1625275446-9999-9995' as confid,
'2021-07-03 04:23:19' as dateTimeConnectSTR,'2021-07-03 04:24:06'  as dateTimeDisconnectSTR) as tmp
                WHERE NOT EXISTS (Select * From CMOON.cdrConfCall c WHERE c.dateTimeConnect=1625275399 and c.dateTimeDisconnect=1625275446 and c.callingNumber='9999' and c.calledNumber='9995') LIMIT 1
 

однако mysql выдает следующее сообщение об ошибке;

Код ошибки: 1054. Неизвестный столбец «dateTimeConnectSTR» в «списке полей»

Я не понимаю проблемы с этим запросом. Мне нужна помощь в решении этой проблемы, не могли бы вы помочь в обнаружении проблемы.

Я забыл написать триггеру, поэтому отредактировал

 CREATE DEFINER=`locku`@`%` TRIGGER `CMOON`.`cdrConfCall_BEFORE_INSERT` BEFORE INSERT ON `cdrConfCall` FOR EACH ROW
BEGIN

declare calledNumberTXT varchar(95);
declare callingNumberTXT varchar(95);

declare catCalling varchar(45);
declare catCalled varchar(45);

IF new.calledName is NULL then
    
    set calledNumberTXT =    (SELECT displayname FROM `CMOON`.`cdrDevicelist` where ext=new.calledNumber order by id limit 1);
    set catCalled =  (SELECT cat FROM `CMOON`.`cdrDevicelist` where ext=new.calledNumber order by id limit 1);
    if calledNumberTXT is NULL then
        set calledNumberTXT = (select displayName from `CMOON`.`cdrLdapUser` where telephoneNumber=new.calledNumber or mobileNumber = CONCAT(' 9',new.calledNumber) or mobileNumber=new.calledNumber or mail = CONCAT(new.calledNumber,'@xxx') limit 1);
        set catCalled = (select cat from `CMOON`.`cdrLdapUser` where telephoneNumber=new.calledNumber  or mobileNumber = CONCAT(' 9',new.calledNumber) or mobileNumber=new.calledNumber  or mail = CONCAT(new.calledNumber,'@xxx') limit 1);
        
    end if;
    set new.calledName=calledNumberTXT;
    set new.catCalling=catCalled;
end if;

if new.callingName is NULL then
    set callingNumberTXT =    (SELECT displayname FROM `CMOON`.`cdrDevicelist` where ext=new.callingNumber order by id limit 1);
    set catCalling =  (SELECT cat FROM `CMOON`.`cdrDevicelist` where ext=new.callingNumber order by id limit 1);
    if callingNumberTXT is NULL then
        set callingNumberTXT = (select displayName from `CMOON`.`cdrLdapUser` where telephoneNumber=new.callingNumber  or mobileNumber = CONCAT(' 9',new.callingNumber)  or mobileNumber=new.callingNumber or mail = CONCAT(new.callingNumber,'@xxx') limit 1);
        set catCalling =(select cat from `CMOON`.`cdrLdapUser` where telephoneNumber=new.callingNumber  or mobileNumber = CONCAT(' 9',new.callingNumber) or mobileNumber=new.callingNumber or mail = CONCAT(new.callingNumber,'@xxx') limit 1);
    end if;
    set new.callingName=callingNumberTXT;
    set new.catCalled = catCalling;
    end if;
set new.ay = concat(MONTHNAME(dateTimeConnectSTR),year(dateTimeConnectSTR));
END
 

Я нашел решение этой проблемы, вызванной следующей строкой в триггере ;

 set new.ay = concat(MONTHNAME(dateTimeConnectSTR),year(dateTimeConnectSTR));
 

правильная линия ;

 set new.ay = concat(MONTHNAME(new.dateTimeConnectSTR),year(new.dateTimeConnectSTR));