I have a view in Informix 12.10 (Linux x86_64) defined like this.
create view "dbo".perinexv
(
ac0, ri0, wf0, up0, di0, dp0, if0, iu0, ts0, dv0, dc0, ca0, m0, ax0, ce0, al0, tg0, ti0, tf0, tn0, pu0, tr0, fm0, pq0, av0, ad0, ab0, af0, an0, ct0, et0, ft0, fn0, nb0, pi0, hb0, rx0, rz0, az0, as0, dr0, ii0, xr0, le0, sk0, sl0, wd0
)
as
select
('SK' || x1.kat_c ) ::varchar(8),
'PE' ::char(2),
CASE WHEN ((x0.dat_zru < CURRENT year to month ) OR ((x0.dat_zru IS NULL ) AND (((mod(x0.vestzru,100 ) - YEAR (TODAY ) ) < 0. ) OR ((mod(x0.vestzru,100 ) - YEAR (TODAY ) ) > 20. ) ) ) ) THEN '.' ELSE ' ' END ::char(1),
CASE WHEN (x0.dat_zru < (CURRENT year to month - interval( 1) month(9) to month ) ) THEN 'H' WHEN (x0.dat_zru < CURRENT year to month ) THEN 'W' WHEN (x1.datzv < (CURRENT year to month - interval( 1) month(9) to month ) ) THEN ('U' || CASE WHEN EXISTS (select x5.datzv from "dbo".evuknor x5 where ((((x5.datzv < CURRENT year to month ) AND (x5.zmop != '1' ) ) AND (x5.identif = x0.identif ) ) AND (x5.rokvyd = x0.rokvyd ) ) ) THEN '*M' ELSE ' ' END ) ELSE ('N' || CASE WHEN EXISTS (select x6.datzv from "dbo".evuknor x6 where ((((x6.datzv < CURRENT year to month ) AND (x6.zmop != '1' ) ) AND (x6.identif = x0.identif ) ) AND (x6.rokvyd = x0.rokvyd ) ) ) THEN '*M' ELSE ' ' END ) END ::char(3),
TRIM ( BOTH ' ' FROM x0.identif ) ::varchar(50),
x1.datzv ::date,
CASE WHEN (x0.identif NOT MATCHES ((((('*' || LPAD (x0.cistn,2,'0' )) || ' ' ) || x0.cisskn ) || LPAD (x0.cisnsk,2,'0' )) || '*' ) ) THEN (((LPAD (x0.cistn,2,'0' )|| ' ' ) || x0.cisskn ) || LPAD (x0.cisnsk,2,'0' )) END ::char(7),
'Normalization office' ::varchar(128),
'ST*N' ::char(4),
x1.datuc ::date,
x1.datsch ::date,
(select informix.concagr(TRIM ( BOTH ' ' FROM x7.ics ),'*' ) ::varchar(64) from "dbo".charcsn x7 where ((x7.identif = x0.identif ) AND (x7.rokvyd = x0.rokvyd ) ) ) ::varchar(64),
((('TK ' || x2.cistnk ) || ' ' ) || x2.nametnk ::varchar(255) ),
NULL ::date,
NULL ::char(1),
x1.jazyk ::char(2),
NULL ::char(1),
REPLACE (x3.nazang,'|','/' )::char(440),
NULL ::char(1),
REPLACE (x0.nazevn,'|','/' )::char(440),
'UNMS SR' ::varchar(24),
NULL ::char(1),
NVL ((x1.str_celk || CASE WHEN (x1.format != 'A4' ) THEN ('/' || x1.format ) ELSE '' END ),'not applicable' )::varchar(14),
(x4.cena_kc || ' EUR' ::varchar(13) ),
NULL ::char(1),
NULL ::char(1),
NULL ::char(1),
NULL ::char(1),
rmnl(REPLACE (x3.abstr,'|','/' ))::lvarchar(2046),
NULL ::char(1),
(select informix.concagr(TRIM ( BOTH ' ' FROM x9.heslo ),'*' ) ::lvarchar(2046) from "dbo".deskrcsn x8,"dbo".klicslo x9 where (((((x8.identif = x0.identif ) AND (x8.rokvyd = x0.rokvyd ) ) AND (x8.isoroot = x9.isoroot ) ) AND (x9.jazyk = 'en' ) ) AND (x9.heslo > '' ) ) ) ::lvarchar(2046),
NULL ::char(1),
(select informix.concagr(TRIM ( BOTH ' ' FROM x11.heslo ),'*' ) ::lvarchar(2046) from "dbo".deskrcsn x10,"dbo".klicslo x11 where (((((x10.identif = x0.identif ) AND (x10.rokvyd = x0.rokvyd ) ) AND (x10.isoroot = x11.isoroot ) ) AND (x11.jazyk = 'sk' ) ) AND (x11.heslo > '' ) ) ) ::lvarchar(2046),
rmnl(REPLACE (x0.pozncsn,'|','/' ))::varchar(255),
NULL ::char(1),
NULL ::char(1),
(select informix.concagr((((TRIM ( BOTH ' ' FROM x13.identif ) || '(' ) || REPLACE (LPAD (x13.rokvyd ::varchar(7),7,'0' ),'.','-' )) || ')' ),'*' ) ::lvarchar(2046) from "dbo".zmena x12,"dbo".evuknor x13 where (((((x12.kat_c = x13.kat_c ) AND (x12.identif = x0.identif ) ) AND (x12.rokvyd = x0.rokvyd ) ) AND (x12.zmzr IN ('0','S' )) ) AND (x12.rokvyd > 1900.00 ) ) ) ::lvarchar(2046),
(select informix.concagrl((((TRIM ( BOTH ' ' FROM x14.identif ) || '(' ) || REPLACE (LPAD (x14.rokvyd ::varchar(7),7,'0' ),'.','-' )) || ')' ),'*' ) ::lvarchar(4094) from "dbo".zmena x14 where (((x14.kat_c = x1.kat_c ) AND (x14.zmzr IN ('0','S' )) ) AND (x14.rokvyd > 1900.00 ) ) ) ::lvarchar(4094),
(select informix.concagr((((x15.zmop || ' ' ) || TRIM ( BOTH ' ' FROM x15.idzm ) ) || NVL ((' ' || LPAD (REPLACE ((x15.vestnik / 100.0000000000000000 ) ::decimal(4,2),'.','/' ),5,'0' )),'' )),'*' ) from "dbo".evuknor x15 where ((((x15.identif = x0.identif ) AND (x15.rokvyd = x0.rokvyd ) ) AND (x15.zmop != '1' ) ) AND (x15.datzv < CURRENT year to month ) ) ) ::varchar(255),
NULL ::char(1),
NULL ::char(1),
(select informix.concagr(((((CASE WHEN (x16.stupshod = 'E' ) THEN 'eqv' WHEN (x16.stupshod = 'H' ) THEN 'har' WHEN (x16.stupshod = 'I' ) THEN 'idt' WHEN (x16.stupshod = 'M' ) THEN 'mod' WHEN (x16.stupshod = 'N' ) THEN 'neq' WHEN (x16.stupshod = 'O' ) THEN 'idt' WHEN (x16.stupshod = 'R' ) THEN 'rel' WHEN (x16.stupshod = 'Z' ) THEN 'zap' ELSE NVL (x16.stupshod ::char(3),' ' ) END || ' ' ) || TRIM ( BOTH ' ' FROM x17.identzn ) ) || ':' ) || x17.rokvzn ),'*' ) from "dbo".prevzeti x16,"dbo".meznorm x17 where ((x16.kat_c = x1.kat_c ) AND (x16.xmezn = x17.xmezn ) ) ) ::varchar(255),
NULL ::char(1),
NULL ::char(1),
(CASE WHEN (x0.dat_zru < CURRENT year to month ) THEN 'Z' ELSE 'A' END || sortkey(x0.identif )::varchar(50) ),
NULL ::char(1),
x0.dat_zru ::date
from "dbo".csn x0,
"dbo".evuknor x1,
outer("dbo".tnk x2 ),
outer("dbo".csntext x3 ),
outer("dbo".censkup x4 )
where
(
(
(
(
(
(
(
((x0.rokvyd > 1900.00 ) AND (x1.zmop = '1' ) )
AND (x1.datzv < CURRENT year to month )
)
AND (x1.identif = x0.identif )
)
AND (x1.rokvyd = x0.rokvyd )
)
AND (x0.cistnk = x2.cistnk )
)
AND (x3.identif = x0.identif )
)
AND (x3.rokvyd = x0.rokvyd )
)
AND (x4.censk = x1.censk )
)
;
When I run the command select count(ac0) from perinexv, it gives me correct results
> select count(ac0) from perinexv;
(count)
71476
1 row(s) retrieved.
But when I try to run select count(*) from perinexv I receive error 683.
> select count(*) from perinexv;
683: Specified STEP expression will not traverse RANGE.
Error in line 1
Near character position 28
If I run select ac0 from perinexv the output is correct
> select ac0 from perinexv;
ac0
SK68386
SK68387
...
SK136374
SK136375
71476 row(s) retrieved.
It is also correct for each separate column. No error is returned. The error 683 means:
683 Specified STEP expression will not traverse RANGE.
The evaluated value of the STEP expression in the FOR statement will lead to an infinite loop.
Example of error:
FOR i = 10 TO 20 STEP -1; — error … END FOR
Correction: Correct either the range or the step expressions so that the incremented values are within the range.
I don't see any step function in the view definition, also, when I try to select each column separately it is without errors.
When I limited the number of rows returned, e.g. select first 100 * from perinexv and it works OK up to about 20000, but sure, when I add sorting (order by ca0), it fails with the error above.
When I run the select * from perinexv, it always finishes with the same ac0 value, so it seems like an issue in the data. Is there any way how to find the following row of the result?
Do you have any other idea what can cause the issue?
Issue identified. It was an issue with data.
In view perinexv there is column sk0 with the following definition:
(CASE WHEN (x0.dat_zru < CURRENT year to month ) THEN 'Z' ELSE 'A' END || sortkey(x0.identif )::varchar(50) )
There is a function sortkey() and parameter x0.identif (x0 is table evuknor in this case).
In definition of the sortkey() the following FOR loop can be found:
LET txt = TRIM(txt);
FOR posa=1 TO LENGTH(txt) STEP 1
IF SUBSTR(txt,posa,1)=' ' THEN
LET txt = SUBSTR(txt,posa);
EXIT FOR;
END IF
END FOR
As you can see, when the LENGTH(txt) (where txt consists of a value of x0.identif) is equal to 0 (empty string), the error I mentioned appears.
Unfortunately, the empty string value exists in the source table and it caused this issue.
The solution is to add the missing value to the source table and change the column definition to not allow empty strings.