Error when trying to insert an insert into MySQL

Question

Error when trying to insert an insert into MySQL

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I'm doing a procedure in which I create a temporary table and fill it with the result of a query, or at least that's what I'm trying to do.

I have the following script:

CREATE TEMPORARY table tblTemporal(
id bigint AUTO_INCREMENT PRIMARY KEY,
 idC bigint,
 idVersion bigint) ENGINE = MEMORY;

INSERT into tblTemporal
SELECT null,
       C.IdC,
       CV.IdCVersion
FROM Cuentas as C join CuentasVersion as CV on C.IdCuenta = CV.IdCuenta
WHERE C.EstatusC=2
      and CV.EstatusV=2
      and CV.IdCVersion=(select MAX(IdCVersion) from ContratoVersion where IdContrato=C.IdContrato)
      and CV.TipoPGenerar=2
      and CV.TipoPExihibicion=1
      and ((select DATE(Max(FechaCreo)) from Requisicion where IdContrato=C.IdContrato)<= DATE_ADD(CV.GeneradaPorPeriodo,INTERVAL NOW() DAY)
        or (select DATE(Max(FechaCreo)) from Requisicion where IdContrato=C.IdContrato) is null);

When executing the previous script I get the following error:

1292 - Incorrect datetime value: '3'

What seems strange to me is that if I execute the select only it does not mark me any error and it brings the data correctly, I hope somebody can help me to know what I am doing wrong.

mysql script stored-procedure

asked by Cristian Ramirez 21.07.2018 в 18:41

source

2 answers

1

If the select works well, the problem may be to try to give a value of null to the field with autoincrement, try to copy the other fields and autoincrement auto fill.

INSERT INTO tblTemporal (IdC, IdCVersion)
    SELECT C.IdC,
           CV.IdCVersion
    FROM Cuentas AS C JOIN CuentasVersion AS CV ON C.IdCuenta = CV.IdCuenta
    WHERE C.EstatusC=2
          AND CV.EstatusV=2
          AND CV.IdCVersion=(SELECT MAX(IdCVersion) FROM ContratoVersion WHER IdContrato=C.IdContrato)
          AND CV.TipoPGenerar=2
          AND CV.TipoPExihibicion=1
          AND ((SELECT DATE(Max(FechaCreo)) FROM Requisicion WHERE IdContrato=C.IdContrato)<= DATE_ADD(CV.GeneradaPorPeriodo,INTERVAL NOW() DAY)
            OR (SELECT DATE(Max(FechaCreo)) FROM Requisicion WHERE IdContrato=C.IdContrato) is null);

answered by 21.07.2018 в 19:03

Imitate a canvas background Save data using a struct

score 1 · Accepted Answer

The error was generated by misinterpreting the field CV.GeneradaPorPeriodo, and when consulting tried to add the value of the mentioned field to the current date.

I realized that CV.GeneradaPorPeriodo had the value of 3 and that was where the error was derived when trying to add days to number 3. So I updated the script and it was as follows:

INSERT INTO tblTemporal (IdC, IdCVersion)
SELECT C.IdC,
       CV.IdCVersion
FROM Cuentas AS C JOIN CuentasVersion AS CV ON C.IdCuenta = CV.IdCuenta
WHERE C.EstatusC=2
      AND CV.EstatusV=2
      AND CV.IdCVersion=(SELECT MAX(IdCVersion) FROM ContratoVersion WHER IdContrato=C.IdContrato)
      AND CV.TipoPGenerar=2
      AND CV.TipoPExihibicion=1
      AND ((SELECT DATE(Max(FechaCreo)) FROM Requisicion WHERE IdContrato=C.IdContrato)<= DATE_ADD(NOW(),INTERVAL CV.GeneradaPorPeriodo DAY)
        OR (SELECT DATE(Max(FechaCreo)) FROM Requisicion WHERE IdContrato=C.IdContrato) is null);