You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
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
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
The text was updated successfully, but these errors were encountered:
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');
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
The text was updated successfully, but these errors were encountered: