Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The cursor identified in the UPDATE or DELETE statement is not positioned on a row. no current record for fetch operation. [CORE6361] #6602

Closed
firebird-automations opened this issue Jul 9, 2020 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: ANTONIO CRISTOVAO GOMES (acgubamg)

Duplicates CORE2155

Error:

The cursor identified in the UPDATE or DELETE statement is not positioned on a row.
no current record for fetch operation.

Tables:

CREATE TABLE CADCLI (
CODCLI integer not null,
NOME_CLI varchar(60)
);

CREATE TABLE CADREC (
EMPRESA_REC integer,
CODIGO_REC bigint,
CLIENTE_REC integer,
DOCUMENTO_REC varchar(20),
EMISSAO_REC date not null,
VENCIMENTO_REC date not null,
PAGAMENTO_REC date,
VALOR_REC numeric(18,2));

insert into cadcli(CODCLI, NOME_CLI)
values(1, 'TESTE1');

insert into cadcli(CODCLI, NOME_CLI)
values(2, 'TESTE2');

// cliente 1

insert into CADREC(EMPRESA_REC, CODIGO_REC, CLIENTE_REC, DOCUMENTO_REC, EMISSAO_REC, VENCIMENTO_REC, PAGAMENTO_REC, VALOR_REC)
values(1, 1, 1, 'doc1', '09.07.2020', '10.07.2020', null, 150);

insert into CADREC(EMPRESA_REC, CODIGO_REC, CLIENTE_REC, DOCUMENTO_REC, EMISSAO_REC, VENCIMENTO_REC, PAGAMENTO_REC, VALOR_REC)
values(1, 2, 1, 'doc2', '09.07.2020', '15.07.2020', null, 150);

insert into CADREC(EMPRESA_REC, CODIGO_REC, CLIENTE_REC, DOCUMENTO_REC, EMISSAO_REC, VENCIMENTO_REC, PAGAMENTO_REC, VALOR_REC)
values(1, 3, 1, 'doc3', '09.07.2020', '20.07.2020', null, 150);

// cliente 2
insert into CADREC(EMPRESA_REC, CODIGO_REC, CLIENTE_REC, DOCUMENTO_REC, EMISSAO_REC, VENCIMENTO_REC, PAGAMENTO_REC, VALOR_REC)
values(1, 4, 2, 'doc4', '09.07.2020', '10.07.2020', null, 100);

insert into CADREC(EMPRESA_REC, CODIGO_REC, CLIENTE_REC, DOCUMENTO_REC, EMISSAO_REC, VENCIMENTO_REC, PAGAMENTO_REC, VALOR_REC)
values(1, 5, 2, 'doc5', '09.07.2020', '15.07.2020', null, 100);

insert into CADREC(EMPRESA_REC, CODIGO_REC, CLIENTE_REC, DOCUMENTO_REC, EMISSAO_REC, VENCIMENTO_REC, PAGAMENTO_REC, VALOR_REC)
values(1, 6, 2, 'doc6', '09.07.2020', '20.07.2020', null, 100);

create or alter procedure CADREC_PARCELA_ABERTO (
PEMPRESA integer,
PPESSOA integer,
PDATA_LIMITE date)
returns (
RPARCELAS_ABERTO integer,
RMESES_ABERTO integer,
RVALOR_ABERTO NUM18_2)
AS
declare variable EMP integer;
declare variable MES integer;
declare variable ANO integer;
declare variable VALOR numeric(18,2);
declare variable CNT integer;
begin
RPARCELAS_ABERTO = 0;
RMESES_ABERTO = 0;
RVALOR_ABERTO = 0;

for select CADREC.EMPRESA_REC
, extract(month from CADREC.VENCIMENTO_REC)
, extract(year from CADREC.VENCIMENTO_REC)
, count(*)
, sum(CADREC.VALOR_REC)
from CADREC
where CADREC.CLIENTE_REC = :PPESSOA
and CADREC.PAGAMENTO_REC is null
and CADREC.VALOR_REC > 0
and CADREC.VENCIMENTO_REC < :PDATA_LIMITE
group by
1, 2, 3
order by
1, 3, 2
into :EMP,
:MES,
:ANO,
:CNT,
:VALOR
do
begin
if ((:PEMPRESA = 0)
or (:PEMPRESA = :EMP)) then
begin
RMESES_ABERTO = :RMESES_ABERTO + 1;
RPARCELAS_ABERTO = :RPARCELAS_ABERTO + :CNT;
RVALOR_ABERTO = :RVALOR_ABERTO + :VALOR;
end
end

suspend;
end

// select

select CADREC.EMPRESA_REC
, CADREC.CODIGO_REC
, CADREC.EMISSAO_REC
, CADREC.CLIENTE_REC
, AB.RPARCELAS_ABERTO
, AB.RMESES_ABERTO
, AB.RVALOR_ABERTO

from CADCLI
inner join CADREC_PARCELA_ABERTO(0, CADCLI.CODCLI, current_date) AB on (AB.RPARCELAS_ABERTO between 1 and 4)
left join CADREC on (CADREC.CLIENTE_REC = CADCLI.CODCLI)
where CADREC.EMPRESA_REC = 1

select CADREC.EMPRESA_REC
, CADREC.CODIGO_REC
, CADREC.EMISSAO_REC
, CADREC.CLIENTE_REC
, AB.RPARCELAS_ABERTO
, AB.RMESES_ABERTO
, AB.RVALOR_ABERTO

from CADCLI
inner join CADREC_PARCELA_ABERTO(0, CADCLI.CODCLI, current_date) AB on (0 = 0)
left join CADREC on (CADREC.CLIENTE_REC = CADCLI.CODCLI)
where CADREC.EMPRESA_REC = 1
and AB.RPARCELAS_ABERTO between 1 and 4

@firebird-automations
Copy link
Collaborator Author

Modified by: ANTONIO CRISTOVAO GOMES (acgubamg)

security: Developers [ 10012 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Looks like duplicating CORE2155. Workaround is to replace INNER JOIN with LEFT JOIN.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE2155 [ CORE2155 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant