Repeated results when running a cursor in MySQL

0

I have a function in MySQL that has a cursor. For the comparisons in sybase you must return two results, but it returns 3 since the second one that returns repeats it. How can I solve it?

I leave the code of the function:

delimiter //
create function dba.fn_atl_sap_fac_imp(vCOD_CLI_FAC decimal(30,6), vID_FAC bigint(30)) returns text
begin
DECLARE ID_IVA CHAR(2);
DECLARE vIMP_IVA CHAR(15);
DECLARE BASE_IVA CHAR(15);
DECLARE IMPORTE_FACTURADO DEC(20, 6);
DECLARE importe_facturado_iva DEC(20, 6);
DECLARE DIFERENCIA DEC(20, 6);
DECLARE numero int;
DECLARE return_Impuesto long VARCHAR;
declare FIN int;


DECLARE csr_IVA_FAC_PAR CURSOR FOR 
        SELECT 
        concat(replace(space(2-length(iva.ID_IVA_SAP)), ' ', '0') , iva.ID_IVA_SAP),
        concat(replace(space(15-length(convert(i.IMP_IVA*100,signed))), ' ', '0'),convert(i.IMP_IVA*100,signed)),
        concat(replace(space(15-length(convert(i.BASE_IVA*100,signed))), ' ', '0'),convert(i.BASE_IVA*100,signed))
        FROM IVA_FAC_PAR i
            join IVA on (iva.id_iva = i.id_iva)
        WHERE #IVA_FAC_PAR.COD_CLI_FAC=vCOD_CLI_FAC AND 
            i.ID_FAC=vID_FAC;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET FIN = 1;

set return_Impuesto='';
set numero=0;

SELECT 
    IMP_FAC
INTO IMPORTE_FACTURADO FROM
    FAC_PAR
WHERE
    FAC_PAR.ID_FAC = vID_FAC;


SELECT 
    SUM(i.IMP_IVA + i.BASE_IVA)
INTO importe_facturado_iva FROM
    IVA_FAC_PAR i
WHERE
    i.ID_FAC = vID_FAC;


SET DIFERENCIA = IMPORTE_FACTURADO - importe_facturado_iva;


OPEN csr_IVA_FAC_PAR;

CICLO:loop
        IF FIN THEN
            LEAVE CICLO;
        END IF;
FETCH csr_IVA_FAC_PAR into ID_IVA, vIMP_IVA, BASE_IVA;

IF DIFERENCIA <> 0 THEN
    SET vIMP_IVA = vIMP_IVA/100;
    SET vIMP_IVA = vIMP_IVA + DIFERENCIA;
    SET vIMP_IVA=concat(replace(space(15-length(convert(vIMP_IVA*100,signed))), ' ', '0'),convert(vIMP_IVA*100,signed));
    SET DIFERENCIA = 0;
END IF;

SET numero= numero + 1;

SET return_Impuesto = concat(return_Impuesto,'T ', ID_IVA , vIMP_IVA , BASE_IVA , ';');  


end loop CICLO;
CLOSE csr_IVA_FAC_PAR;

return return_Impuesto;
end;
    
asked by Oscar Marés Barrera 02.10.2018 в 07:42
source

1 answer

0

It must be that in the cursor you first check the status of the cursor and then you fetch the data, so the last record is always repeated, it should be solved by changing the fetch line above the IF:

 delimiter //
create
function dba.fn_atl_sap_fac_imp(vCOD_CLI_FAC decimal(30, 6), vID_FAC bigint(30)) returns text
begin
DECLARE ID_IVA CHAR(2);
DECLARE vIMP_IVA CHAR(15);
DECLARE BASE_IVA CHAR(15);
DECLARE IMPORTE_FACTURADO DEC(20, 6);
DECLARE importe_facturado_iva DEC(20, 6);
DECLARE DIFERENCIA DEC(20, 6);
DECLARE numero int;
DECLARE return_Impuesto long VARCHAR;
declare FIN int;


DECLARE csr_IVA_FAC_PAR CURSOR FOR
SELECT
concat(replace(space(2 - length(iva.ID_IVA_SAP)), ' ', '0'), iva.ID_IVA_SAP),
    concat(replace(space(15 - length(convert(i.IMP_IVA * 100, signed))), ' ', '0'), convert(i.IMP_IVA * 100, signed)),
    concat(replace(space(15 - length(convert(i.BASE_IVA * 100, signed))), ' ', '0'), convert(i.BASE_IVA * 100, signed))
FROM IVA_FAC_PAR i
join IVA on(iva.id_iva = i.id_iva)
WHERE# IVA_FAC_PAR.COD_CLI_FAC = vCOD_CLI_FAC AND
i.ID_FAC = vID_FAC;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET FIN = 1;

set return_Impuesto = '';
set numero = 0;

SELECT
IMP_FAC
INTO IMPORTE_FACTURADO FROM
FAC_PAR
WHERE
FAC_PAR.ID_FAC = vID_FAC;


SELECT
SUM(i.IMP_IVA + i.BASE_IVA)
INTO importe_facturado_iva FROM
IVA_FAC_PAR i
WHERE
i.ID_FAC = vID_FAC;


SET DIFERENCIA = IMPORTE_FACTURADO - importe_facturado_iva;


OPEN csr_IVA_FAC_PAR;
FETCH csr_IVA_FAC_PAR into ID_IVA, vIMP_IVA, BASE_IVA;

CICLO: loop
IF FIN THEN
LEAVE CICLO;
END IF;


IF DIFERENCIA < > 0 THEN
SET vIMP_IVA = vIMP_IVA / 100;
SET vIMP_IVA = vIMP_IVA + DIFERENCIA;
SET vIMP_IVA = concat(replace(space(15 - length(convert(vIMP_IVA * 100, signed))), ' ', '0'), convert(vIMP_IVA * 100, signed));
SET DIFERENCIA = 0;
END IF;

SET numero = numero + 1;

SET return_Impuesto = concat(return_Impuesto, 'T ', ID_IVA, vIMP_IVA, BASE_IVA, ';');


end loop CICLO;
CLOSE csr_IVA_FAC_PAR;
  

Fetch MYSQL: link

    
answered by 04.10.2018 в 20:44