Hi all,
I have a problem with a procedure.
The table looks like this:

z1 z2 z3 z4 z5 anz
1   2   3   4   5    0
4   5   6   7   8    0

ca 1500 rows.

Now I will check how many same numbers are in the next row and save this in the column anz.
I tried this:
PROCEDURE ANZAHL;
DECLARE STAT CURSOR WITH RETURN FOR STMT;
DECLARE N1 SMALLINT;
DECLARE N2 SMALLINT;
DECLARE N3 SMALLINT;
DECLARE N4 SMALLINT;
DECLARE N5 SMALLINT;

PREPARE STMT FROM TABLE;
OPEN STAT;

FETCH FIRST FROM STAT(Z1,Z2,Z3,Z4,Z5) INTO N1,N2,N3,N4,N5;
WHILE NOT EOF(STAT) DO

IF ?????????

FETCH NEXT FROM STAT(Z1,Z2,Z3,Z4,Z5) INTO N1,N2,N3,N4,N5;
END WHILE;

Please help me.
TIA
Dieter

  

Dieter

I wonder if John Hay will be able to come up with a one liner for this

There's a lot wrong with your code. I can see two approaches, neither very efficient, but it depends on wether or not the table has a primary key. Does it, and if so what is it?

Roy Lambert [Team Elevate]

Roy

Now I know that it must be Fetch first from Table('z1,'z2' ....) into.....

No there is no primary key.


Roy Lambert wrote:

Dieter

I wonder if John Hay will be able to come up with a one liner for this

There's a lot wrong with your code. I can see two approaches, neither very efficient, but it depends on wether or not the table has a primary key. Does it, and if so what is it?


Roy Lambert [Team Elevate]

You'll have to have either a primary key or some index of some sort to
know which row is considered the "next" row.  You don't know which order
your rows are returned.  So that is the very first thing you need to do
before any other logic is applied.

--
David Cornelius
Cornelius Concepts

On 11/04/12 01:04, Dieter Nagy wrote:
> Roy
>
> Now I know that it must be Fetch first from Table('z1,'z2' ....) into.....
>
> No there is no primary key.
>
>
> Roy Lambert wrote:
>
> Dieter
>
> I wonder if John Hay will be able to come up with a one liner for this
>
> There's a lot wrong with your code. I can see two approaches, neither very efficient, but it depends on wether or not the table has a primary key. Does it, and if so what is it?
>
>
> Roy Lambert [Team Elevate]
>

Dieter


Without a primary key you'll need two cursors into the table, one for the line to be updated and one for the next line, Code something like the below should work. It does come with a health warning - I haven't tested it at all.


PROCEDURE ANZAHL;
DECLARE UpdateSTAT CURSOR FOR UpdateStr;
DECLARE TestSTAT CURSOR FOR TestStr;
DECLARE TstStr VARCHAR;
DECLARE N1 VARCHAR;
DECLARE N2 VARCHAR;;
DECLARE N3 VARCHAR;
DECLARE N4 VARCHAR;
DECLARE N5 VARCHAR;
DECLARE NFound SMALLINT;
DECLARE Counter INTEGER;


PREPARE UpdateStr FROM 'SELECT
'|'+CAST(Z1 AS VARCHAR)  + '|' AS Chk1,
'|'+CAST(Z2 AS VARCHAR)  + '|' AS Chk2,
'|'+CAST(Z3 AS VARCHAR)  + '|' AS Chk3,
'|'+CAST(Z4 AS VARCHAR)  + '|' AS Chk4,
'|'+CAST(Z5 AS VARCHAR)  + '|' AS Chk5,,
NFound';

FROM tablename';
OPEN UpdateSTAT;

PREPARE TestStr FROM 'SELECT '|'+CAST(Z1 AS VARCHAR)  + '|'+CAST(Z2 AS VARCHAR) + '|'+CAST(Z3 AS VARCHAR) + '|'+CAST(Z4 AS VARCHAR) + '|'+CAST(Z5 AS VARCHAR) As ToCompare   FROM tablename';
OPEN TestSTAT;

FETCH FIRST FROM TestSTAT(ToCompare) INTO TstStr;
FETCH FIRST FROM UpdateSTAT(Chk1,Chk2,Chk3,Chk4,Chk5, ZFound) INTO N1,N2,N3,N4,N5,NFound;

WHILE NOT EOF(TestSTAT) DO
SET Counter = 0;
FETCH NEXT FROM TestSTAT(ToCompare) INTO TstStr;

IF 0 <> POSITION(Chk1 IN TstStr) THEN
 SET Counter = Counter + 1;
END;

IF 0 <> POSITION(Chk2 IN TstStr) THEN
SET Counter = Counter + 1;
END;

IF 0 <> POSITION(Chk3 IN TstStr) THEN
SET Counter = Counter + 1;
END;

IF 0 <> POSITION(Chk4 IN TstStr) THEN
SET Counter = Counter + 1;
END;

IF 0 <> POSITION(Chk5 IN TstStr) THEN
SET Counter = Counter + 1;
END;

UPDATE UpdateSTAT SET NFound = Counter;
FETCH NEXT FROM UpdateSTAT(Chk1,Chk2,Chk3,Chk4,Chk5, ZFound) INTO N1,N2,N3,N4,N5,NFound;

END WHILE;

Roy Lambert [Team Elevate]

David


>You'll have to have either a primary key or some index of some sort to
>know which row is considered the "next" row. You don't know which order
>your rows are returned. So that is the very first thing you need to do
>before any other logic is applied.

My assumption is that its simply the "natural" order.

Roy Lambert [Team Elevate]

ps Thanks for the VERY helpful suggestion re XP problems

Roy,

after hours of testing I tried this and its works..

DECLARE STATCURSOR CURSOR WITH RETURN FOR STMT;
DECLARE R SMALLINT;
DECLARE AZ1 SMALLINT;
DECLARE AZ2 SMALLINT;
DECLARE AZ3 SMALLINT;
DECLARE AZ4 SMALLINT;
DECLARE AZ5 SMALLINT;
DECLARE AZ6 SMALLINT;
DECLARE R2 SMALLINT;
DECLARE A2Z1 SMALLINT;
DECLARE A2Z2 SMALLINT;
DECLARE A2Z3 SMALLINT;
DECLARE A2Z4 SMALLINT;
DECLARE A2Z5 SMALLINT;
DECLARE A2Z6 SMALLINT;


PREPARE STMT FROM'SELECT * FROM ZTEST';
OPEN STATCURSOR;

FETCH FIRST FROM  STATCURSOR('Z1','Z2','Z3','Z4','Z5','Z6')INTO  AZ1,AZ2,AZ3,AZ4,AZ5,AZ6;

SET A2Z1 = AZ1;
SET A2Z2 = AZ2;
SET A2Z3 = AZ3;
SET A2Z4 = AZ4;
SET A2Z5 = AZ5;
SET A2Z6 = AZ6;
fetch next from statcursor;
WHILE NOT EOF(STATCURSOR)DO

 IF(IF(AZ1 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ2 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ3 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ4 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ5 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ6 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0))=2

      THEN UPDATE STATCURSOR SET 'A2' = 2;
ELSE
   IF(IF(AZ1 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ2 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ3 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ4 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ5 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0)+
   IF(AZ6 IN(A2Z1,A2Z2,A2Z3,A2Z4,A2Z5,A2Z6),1,0))=3

THEN UPDATE STATCURSOR SET 'A2' = 3;

END IF;
END IF;
SET A2Z1 = AZ1;
SET A2Z2 = AZ2;
SET A2Z3 = AZ3;
SET A2Z4 = AZ4;
SET A2Z5 = AZ5;
SET A2Z6 = AZ6;

FETCH NEXT FROM  STATCURSOR('Z1','Z2','Z3','Z4','Z5','Z6')INTO AZ1,AZ2,AZ3,AZ4,AZ5,AZ6;
END WHILE;


Thanks for your help
Dieter


Roy Lambert wrote:

David


>You'll have to have either a primary key or some index of some sort to
>know which row is considered the "next" row. You don't know which order
>your rows are returned. So that is the very first thing you need to do
>before any other logic is applied.

My assumption is that its simply the "natural" order.

Roy Lambert [Team Elevate]

ps Thanks for the VERY helpful suggestion re XP problems

David,

thanks for your help.

Dieter






David Cornelius wrote:

You'll have to have either a primary key or some index of some sort to
know which row is considered the "next" row.  You don't know which order
your rows are returned.  So that is the very first thing you need to do
before any other logic is applied.

--
David Cornelius
Cornelius Concepts

On 11/04/12 01:04, Dieter Nagy wrote:
> Roy
>
> Now I know that it must be Fetch first from Table('z1,'z2' ....) into.....
>
> No there is no primary key.
>
>
> Roy Lambert wrote:
>
> Dieter
>
> I wonder if John Hay will be able to come up with a one liner for this
>
> There's a lot wrong with your code. I can see two approaches, neither very efficient, but it depends on wether or not the table has a primary key. Does it, and if so what is it?
>
>
> Roy Lambert [Team Elevate]
>

Dieter,

A little "trick" that you can use whenever you want to simply open a table
cursor on a table ordered by the primary key is:

PREPARE STMT FROM 'TABLE ZTEST';

Just make sure that your CURSOR is DECLAREd without the INSENSITIVE clause,
otherwise you'll get an insensitive, static result set.

Tim Young
Elevate Software
www.elevatesoft.com

From all I've read about client/server databases, you should NEVER rely
on table order of records, but ALWAYS specify explicitly what you want
and what order you want it in.  It's up to the database server engine to
decide what order to give you records if you don't specify.  The engine
will optimize disk space and internal record structures and you have no
way of guaranteeing any sort of order, especially as records are deleted
and new records fill their spot before adding to the database file.

So unless Tim corrects me, I would say it is very dangerous to assume
any natural order in a "true" database.  The one exception is Tim's
trick listed in this thread where you can assume primary key order if
nothing else is stated.  But even then, it's at least implicitly defined.

--
David Cornelius
Cornelius Concepts

On 11/05/12 01:05, Roy Lambert wrote:
> David
>
>
>> You'll have to have either a primary key or some index of some sort to
>> know which row is considered the "next" row. You don't know which order
>> your rows are returned. So that is the very first thing you need to do
>> before any other logic is applied.
>
> My assumption is that its simply the "natural" order.
>
> Roy Lambert [Team Elevate]
>
> ps Thanks for the VERY helpful suggestion re XP problems
>