firebird
firebird copied to clipboard
FirebirdSQL
no current record for fetch operation [CORE6106]
Submitted by: @livius2
Attachments: COREXXX.FDB
WITH
KWARTALY AS (
SELECT *
FROM KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I' AND '2019.I'
),
KWARTALY_DATY AS (
SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM KWARTALY KW
)
SELECT
K.ID, K.KONT_NR
FROM
KONTRAHENT K
WHERE
NOT (
EXISTS (
SELECT 1
FROM KWARTALY_DATY KW
WHERE
/* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest platnikiem */
EXISTS (
SELECT *
FROM SPR S
INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = SKW.ID
WHERE
S.ID_KONTRAHENT = K.ID
AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL AND KW.MAX_KWARTAL
)
OR
/* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie z przyszlosci) to tez jest platnikiem */
EXISTS (
SELECT *
FROM
STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO
AND (
SPT.FLAGA_SYS IN (1)
AND (
(SP.DATA_OD <= KW.MAX_D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD))
)
)
)
UNION ALL
SELECT 1
FROM KWARTALY_DATY KD
INNER JOIN URZADZENIE U ON 1 = 1
/* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci kontrahenta i szukamy brakow deklaracji */
INNER JOIN KWARTALY KW ON
EXISTS (
SELECT *
FROM STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO
AND (
SPT.FLAGA_SYS IN (1)
AND (
(SP.DATA_OD <= KW.D_DO) AND
(SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD))
)
)
)
WHERE
NOT EXISTS (
SELECT *
FROM SPR S
WHERE
S.ID_KONTRAHENT = K.ID
AND S.ID_URZADZENIE = U.ID
AND (
( EXTRACT(YEAR FROM S.DATA_OD) * 12 + EXTRACT(MONTH FROM S.DATA_OD)) BETWEEN (KW.ROK * 12 + KW.MIESIAC_OD) AND (KW.ROK * 12 + KW.MIESIAC_DO)
OR (EXTRACT(YEAR FROM S.DATA_DO) * 12 + EXTRACT(MONTH FROM S.DATA_DO)) BETWEEN (KW.ROK * 12 + KW.MIESIAC_OD) AND (KW.ROK * 12 + KW.MIESIAC_DO)
)
)
)
AND K.KONT_NR > 0
)
----------------------------------------
SQL Message : \-508
The cursor identified in the UPDATE or DELETE statement is not positioned on a row\.
Engine Code : 335544348
Engine Message :
no current record for fetch operation
----------------------------------------
if you change last
AND K.KONT_NR > 0)
to
AND K.KONT_NR+0 > 0)
it run without an error
Modified by: @livius2
description: WITH KWARTALY AS (SELECT * FROM KWARTAL KW WHERE KW.CKWARTAL BETWEEN '2019.I' AND '2019.I'), KWARTALY_DATY AS (SELECT MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD , MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO , MIN(KW.D_OD) AS MIN_D_OD , MAX(KW.D_DO) AS MAX_D_DO , MIN(KW.CKWARTAL) AS MIN_KWARTAL , MAX(KW.CKWARTAL) AS MAX_KWARTAL FROM KWARTALY KW) SELECT http://K.ID , K.KONT_NR
FROM KONTRAHENT K WHERE NOT ( EXISTS ( SELECT 1 FROM KWARTALY_DATY KW WHERE /* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest platnikiem*/ EXISTS (SELECT * FROM SPR S INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = http://SKW.ID WHERE S.ID_KONTRAHENT = http://K.ID AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL AND KW.MAX_KWARTAL ) OR /* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie z przyszlosci) to tez jest platnikiem */ EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.MAX_D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD)))) )
UNION ALL
SELECT
1
FROM
KWARTALY\_DATY KD
INNER JOIN URZADZENIE U ON 1 = 1
/* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci kontrahenta i szukamy brakow deklaracji */ INNER JOIN KWARTALY KW ON EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD)))) ) WHERE NOT EXISTS (SELECT * FROM SPR S WHERE S.ID_KONTRAHENT = http://K.ID AND S.ID_URZADZENIE = http://U.ID AND ((EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM S.DATA_OD)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR (EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO)) ) ) AND K.KONT_NR > 0)
SQL Message : -508 The cursor identified in the UPDATE or DELETE statement is not positioned on a row.
Engine Code : 335544348 Engine Message : no current record for fetch operation
if you change last AND K.KONT_NR > 0) to AND K.KONT_NR+0 > 0)
it run without an error
=>
WITH KWARTALY AS (SELECT * FROM KWARTAL KW WHERE KW.CKWARTAL BETWEEN '2019.I' AND '2019.I'), KWARTALY_DATY AS (SELECT MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD , MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO , MIN(KW.D_OD) AS MIN_D_OD , MAX(KW.D_DO) AS MAX_D_DO , MIN(KW.CKWARTAL) AS MIN_KWARTAL , MAX(KW.CKWARTAL) AS MAX_KWARTAL FROM KWARTALY KW) SELECT http://K.ID , K.KONT_NR
FROM KONTRAHENT K WHERE NOT ( EXISTS ( SELECT 1 FROM KWARTALY_DATY KW WHERE /* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest platnikiem*/ EXISTS (SELECT * FROM SPR S INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = http://SKW.ID WHERE S.ID_KONTRAHENT = http://K.ID AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL AND KW.MAX_KWARTAL ) OR /* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie z przyszlosci) to tez jest platnikiem */ EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.MAX_D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD)))) )
UNION ALL
SELECT
1
FROM
KWARTALY\_DATY KD
INNER JOIN URZADZENIE U ON 1 = 1
/* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci kontrahenta i szukamy brakow deklaracji */ INNER JOIN KWARTALY KW ON EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD)))) ) WHERE NOT EXISTS (SELECT * FROM SPR S WHERE S.ID_KONTRAHENT = http://K.ID AND S.ID_URZADZENIE = http://U.ID AND ((EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM S.DATA_OD)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR (EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO)) ) ) AND K.KONT_NR > 0)
----------------------------------------
SQL Message : -508 The cursor identified in the UPDATE or DELETE statement is not positioned on a row.
Engine Code : 335544348 Engine Message : no current record for fetch operation
----------------------------------------
if you change last AND K.KONT_NR > 0) to AND K.KONT_NR+0 > 0)
it run without an error
Commented by: Sean Leyne (seanleyne)
Reformatted SQL for readibility
Modified by: Sean Leyne (seanleyne)
description: WITH KWARTALY AS (SELECT * FROM KWARTAL KW WHERE KW.CKWARTAL BETWEEN '2019.I' AND '2019.I'), KWARTALY_DATY AS (SELECT MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD , MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO , MIN(KW.D_OD) AS MIN_D_OD , MAX(KW.D_DO) AS MAX_D_DO , MIN(KW.CKWARTAL) AS MIN_KWARTAL , MAX(KW.CKWARTAL) AS MAX_KWARTAL FROM KWARTALY KW) SELECT http://K.ID , K.KONT_NR
FROM KONTRAHENT K WHERE NOT ( EXISTS ( SELECT 1 FROM KWARTALY_DATY KW WHERE /* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest platnikiem*/ EXISTS (SELECT * FROM SPR S INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = http://SKW.ID WHERE S.ID_KONTRAHENT = http://K.ID AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL AND KW.MAX_KWARTAL ) OR /* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie z przyszlosci) to tez jest platnikiem */ EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.MAX_D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD)))) )
UNION ALL
SELECT
1
FROM
KWARTALY\_DATY KD
INNER JOIN URZADZENIE U ON 1 = 1
/* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci kontrahenta i szukamy brakow deklaracji */ INNER JOIN KWARTALY KW ON EXISTS (SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD)))) ) WHERE NOT EXISTS (SELECT * FROM SPR S WHERE S.ID_KONTRAHENT = http://K.ID AND S.ID_URZADZENIE = http://U.ID AND ((EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM S.DATA_OD)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR (EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO)) ) ) AND K.KONT_NR > 0)
----------------------------------------
SQL Message : -508 The cursor identified in the UPDATE or DELETE statement is not positioned on a row.
Engine Code : 335544348 Engine Message : no current record for fetch operation
----------------------------------------
if you change last AND K.KONT_NR > 0) to AND K.KONT_NR+0 > 0)
it run without an error
=>
WITH KWARTALY AS ( SELECT * FROM KWARTAL KW WHERE KW.CKWARTAL BETWEEN '2019.I' AND '2019.I' ), KWARTALY_DATY AS ( SELECT MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD , MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO , MIN(KW.D_OD) AS MIN_D_OD , MAX(KW.D_DO) AS MAX_D_DO , MIN(KW.CKWARTAL) AS MIN_KWARTAL , MAX(KW.CKWARTAL) AS MAX_KWARTAL FROM KWARTALY KW ) SELECT http://K.ID, K.KONT_NR FROM KONTRAHENT K WHERE NOT( EXISTS( SELECT 1 FROM KWARTALY_DATY KW WHERE /* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest platnikiem*/ EXISTS( SELECT * FROM SPR S INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = http://SKW.ID WHERE S.ID_KONTRAHENT = http://K.ID AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL AND KW.MAX_KWARTAL ) OR /* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie z przyszlosci) to tez jest platnikiem */ EXISTS ( SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO AND ( SPT.FLAGA_SYS IN(1) AND ( (SP.DATA_OD <= KW.MAX_D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD)) ) ) ) UNION ALL SELECT 1 FROM KWARTALY_DATY KD INNER JOIN URZADZENIE U ON 1 = 1 /* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci kontrahenta i szukamy brakow deklaracji */ INNER JOIN KWARTALY KW ON EXISTS( SELECT * FROM STATUS_PLATNIKA SP INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID WHERE SP.ID_KONTRAHENT = http://K.ID AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO AND ( SPT.FLAGA_SYS IN(1) AND ( (SP.DATA_OD <= KW.D_DO) AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD)) ) ) ) WHERE NOT EXISTS( SELECT * FROM SPR S WHERE S.ID_KONTRAHENT = http://K.ID AND S.ID_URZADZENIE = http://U.ID AND ( ( EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM S.DATA_OD)) BETWEEN (KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR (EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) ) ) ) AND K.KONT_NR > 0 )
----------------------------------------
SQL Message : -508 The cursor identified in the UPDATE or DELETE statement is not positioned on a row.
Engine Code : 335544348 Engine Message : no current record for fetch operation
----------------------------------------
if you change last AND K.KONT_NR > 0) to AND K.KONT_NR+0 > 0)
it run without an error
Commented by: Sean Leyne (seanleyne)
Karol,
There are some aspects of the SQL which I find "unexpected":
1- The use of UNION when AND EXISTS could be used
2- The use of an EXISTS as a JOIN criteria, again this could be better expressed as a EXISTS within the WHERE clause
3- You use the alias "KW" 4 separate times. IMO, a very bad practice
Commented by: @livius2
This is good catch, but as you probably can guess, this is not hand written query. It is generated and mixed together from few separated modules.
I can change query (some generation paths) to hide the problem. But the issue somewhere exist, when the plan ues one additional index.