Issue Details (XML | Word | Printable)

Key: CORE-6361
Type: Bug Bug
Status: Closed Closed
Resolution: Duplicate
Priority: Major Major
Assignee: Unassigned
Reporter: ANTONIO CRISTOVAO GOMES
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 09/Jul/20 08:25 PM   Updated: 10/Jul/20 12:51 PM
Component/s: API / Client Library
Affects Version/s: 2.5.9
Fix Version/s: None

Environment: ibexpert, windows 10 64, select with inner join
Issue Links:
Duplicate
 

QA Status: No test


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 10/Jul/20 09:41 AM
Looks like duplicating CORE-2155. Workaround is to replace INNER JOIN with LEFT JOIN.