no current record for fetch operation [CORE6106] #6355

firebird-automations · 2019-07-22T16:59:26Z

Submitted by: @livius2

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

The text was updated successfully, but these errors were encountered:

firebird-automations · 2019-07-22T17:02:02Z

Modified by: @livius2

Attachment: COREXXX.FDB [ 13362 ]

firebird-automations · 2019-07-22T17:02:38Z

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

firebird-automations · 2019-07-22T17:27:52Z

Commented by: Sean Leyne (seanleyne)

Reformatted SQL for readibility

firebird-automations · 2019-07-22T17:27:52Z

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

firebird-automations · 2019-07-22T19:13:27Z

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

firebird-automations · 2019-07-23T01:02:15Z

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.

firebird-automations · 2019-07-23T16:36:48Z

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

firebird-automations added affect-version: 3.0.5 priority: major component: engine type: bug labels Apr 25, 2021

firebird-automations assigned dyemanov Apr 25, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

no current record for fetch operation [CORE6106] #6355

no current record for fetch operation [CORE6106] #6355

firebird-automations commented Jul 22, 2019 •

edited by dyemanov

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 23, 2019

firebird-automations commented Jul 23, 2019

no current record for fetch operation [CORE6106] #6355

no current record for fetch operation [CORE6106] #6355

Comments

firebird-automations commented Jul 22, 2019 • edited by dyemanov

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 22, 2019

firebird-automations commented Jul 23, 2019

firebird-automations commented Jul 23, 2019

firebird-automations commented Jul 22, 2019 •

edited by dyemanov