I have a SQL cursor that is used to retrieve records from parts table. The program is designed to return no more than 5000 records per read, then it closed the cursor.

In case if there are more records than 5000, it saves off record number 5001 and use it to reopen the cursor and get the rest of the records.

The issue appears to be not all records is returned on the second read.

The cursor look like this

EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY
, WAREHOUSE
, REPORT_NUMBER
, SOURCE_OF_SUPPLY
, PART_NUM
, BIN_LOC

FROM =PARTS FOR BROWSE ACCESS
WHERE COMPANY = :SEARCH-COMPANY AND
WAREHOUSE = :SEARCH-WAREHOUSE AND
REPORT_NUMBER = :SEARCH-REPORT-NUMBER AND
SOURCE_OF_SUPPLY >= :SEARCH-SOS AND
PART_NUM >= :SEARCH-PART-NUM AND
BIN_LOC >= :SEARCH-BIN-LOC AND
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM, BIN_LOC, ALT_BIN ASC
END-EXEC.

On the second read/Open cursor, it returns the record which was saved off to use it to reopen the cursor. but then it skips a bunch of records. I was hoping someone would be able to tell me what am i doing wrong in the cursor. Thanks in advance and have
a wonderful weekend !

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <e10c4470-30c4-437b-916e-47ac03f30f43n@googlegroups.com>, abolkini@gmail.com says...

I have a SQL cursor that is used to retrieve records from parts table. The = >program is designed to return no more than 5000 records per read, then it c= >losed the cursor.=20

In case if there are more records than 5000, it saves off record number 500= >1 and use it to reopen the cursor and get the rest of the records.

The issue appears to be not all records is returned on the second read.

The cursor look like this

EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=20
, WAREHOUSE
, REPORT_NUMBER
, SOURCE_OF_SUPPLY
, PART_NUM
, BIN_LOC

FROM =3DPARTS FOR BROWSE ACCESS
WHERE COMPANY =3D :SEARCH-COMPANY AND
WAREHOUSE =3D :SEARCH-WAREHOUSE AND
REPORT_NUMBER =3D :SEARCH-REPORT-NUMBER AND
SOURCE_OF_SUPPLY >=3D :SEARCH-SOS AND
PART_NUM >=3D :SEARCH-PART-NUM AND
BIN_LOC >=3D :SEARCH-BIN-LOC AND
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM,= BIN_LOC, ALT_BIN ASC
END-EXEC.

On the second read/Open cursor, it returns the record which was saved off = >to use it to reopen the cursor. but then it skips a bunch of records. I was=
hoping someone would be able to tell me what am i doing wrong in the curso=
r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"

What are the keycols for the table ?

What does an explain plan for the statement show ?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, August 11, 2023 at 2:59:09 PM UTC-7, JShepherd wrote:

In article <e10c4470-30c4-437b...@googlegroups.com>,
abol...@gmail.com says...

I have a SQL cursor that is used to retrieve records from parts table. The =
program is designed to return no more than 5000 records per read, then it c=
losed the cursor.=20

In case if there are more records than 5000, it saves off record number 500= >1 and use it to reopen the cursor and get the rest of the records.

The issue appears to be not all records is returned on the second read.

The cursor look like this

EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=20
, WAREHOUSE
, REPORT_NUMBER
, SOURCE_OF_SUPPLY
, PART_NUM
, BIN_LOC

FROM =3DPARTS FOR BROWSE ACCESS
WHERE COMPANY =3D :SEARCH-COMPANY AND
WAREHOUSE =3D :SEARCH-WAREHOUSE AND
REPORT_NUMBER =3D :SEARCH-REPORT-NUMBER AND
SOURCE_OF_SUPPLY >=3D :SEARCH-SOS AND
PART_NUM >=3D :SEARCH-PART-NUM AND
BIN_LOC >=3D :SEARCH-BIN-LOC AND
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM,= BIN_LOC, ALT_BIN ASC
END-EXEC.

On the second read/Open cursor, it returns the record which was saved off = >to use it to reopen the cursor. but then it skips a bunch of records. I was=
hoping someone would be able to tell me what am i doing wrong in the curso=
r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"

What are the keycols for the table ?

What does an explain plan for the statement show ?

Happy Monday,

Thanks for taking the time to reply back. The key is the selected columns
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NOT NULL
, WARE CHAR(3) NO DEFAULT NOT NULL
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
, PART_NUM CHAR(30) NO DEFAULT NOT NULL
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL


Since you brought up the ORDER BY and explain plan, I noticed the key of the table is in ascending order and exactly is my ORDER BY columns, so I removed ORDER BY from the cursor but It still skipped records.

Here is EXPLAIN plan:

Operation 1.0 : Scan
Table PARTS
with correlation name PARTS
Access type : No locks, browse access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 19 out of 20 columns

Access path 1 : Primary, sequential cache
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY= :SEARCH-COMPANY , WARE = :SEARCH-WARE ,
REPORT_NUMBER = :SEARCH-REPORT-NUMBER
End key pred. : COMPANY = :SEARCH-COMPANY , WARE =
:SEARCH-WARE , REPORT_NUMBER = :SEARCH-REPORT-NUMBER
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >= :SEARCH-SOS ) AND ( BIN_LOC >= :SEARCH-BIN-LOC )
AND ( PART_NUM >= :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table

Executor pred. : None
Table selectivity : Expect to select 1.2341% of rows from table
Expected row count: 5 rows after the scan
Operation cost : 35

Operation 1.1 : Sort
Requested : Explicitly in the query
Sort rows in the : Result of a Select
Purpose : To order rows for an Order By
Sort technique : FASTSORT
Sort type : Plan to use User Process Sort
UPS workspace : 34 Kbytes
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
PARTS.REPORT_NUMBER asc,
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,
Sort cost : 1

Total cost : 36

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>, abolkini@gmail.com says...

On Friday, August 11, 2023 at 2:59:09=E2=80=AFPM UTC-7, JShepherd wrote:

In article <e10c4470-30c4-437b...@googlegroups.com>,=20
abol...@gmail.com says...=20

=20
I have a SQL cursor that is used to retrieve records from parts table. T= >he =3D=20
program is designed to return no more than 5000 records per read, then i= >t c=3D=20
losed the cursor.=3D20=20
=20
In case if there are more records than 5000, it saves off record number = >500=3D
1 and use it to reopen the cursor and get the rest of the records.=20
=20
The issue appears to be not all records is returned on the second read.= >=20
=20
The cursor look like this=20
=20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D20
, WAREHOUSE=20
, REPORT_NUMBER=20
, SOURCE_OF_SUPPLY=20
, PART_NUM=20
, BIN_LOC=20

FROM =3D3DPARTS FOR BROWSE ACCESS=20
WHERE COMPANY =3D3D :SEARCH-COMPANY AND=20
WAREHOUSE =3D3D :SEARCH-WAREHOUSE AND=20
REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMBER AND=20
SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS AND=20
PART_NUM >=3D3D :SEARCH-PART-NUM AND=20
BIN_LOC >=3D3D :SEARCH-BIN-LOC AND=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM= >,=3D=20
BIN_LOC, ALT_BIN ASC=20
END-EXEC.=20
=20
On the second read/Open cursor, it returns the record which was saved of= >f =3D=20
to use it to reopen the cursor. but then it skips a bunch of records. I = >was=3D=20
hoping someone would be able to tell me what am i doing wrong in the cu= >rso=3D
r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"=20
=20
What are the keycols for the table ?=20
=20
What does an explain plan for the statement show ?

Happy Monday,

Thanks for taking the time to reply back. The key is the selected columns= >=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=
T NULL
, WARE CHAR(3) NO DEFAULT NOT NULL
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
, PART_NUM CHAR(30) NO DEFAULT NOT NULL
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL

Since you brought up the ORDER BY and explain plan, I noticed the key of th= >e table is in ascending order and exactly is my ORDER BY columns, so I remo= >ved ORDER BY from the cursor but It still skipped records.=20

Here is EXPLAIN plan:

Operation 1.0 : Scan
Table PARTS
with correlation name PARTS
Access type : No locks, browse access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 19 out of 20 columns
=20
Access path 1 : Primary, sequential cache
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D :SEARCH-COMPANY , WARE =3D :SEARCH-WAR=
E ,=20
REPORT_NUMBER =3D :SEARCH-REPORT-NUMB=
ER
End key pred. : COMPANY =3D :SEARCH-COMPANY , WARE =3D
:SEARCH-WARE , REPORT_NUMBER =3D :SEARCH-REPORT-N=
UMBER
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D :SEARCH-SOS ) AND ( BIN_=
LOC >=3D :SEARCH-BIN-LOC )
AND ( PART_NUM >=3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=20
Executor pred. : None
Table selectivity : Expect to select 1.2341% of rows from table
Expected row count: 5 rows after the scan
Operation cost : 35
=20
Operation 1.1 : Sort
Requested : Explicitly in the query
Sort rows in the : Result of a Select
Purpose : To order rows for an Order By
Sort technique : FASTSORT
Sort type : Plan to use User Process Sort
UPS workspace : 34 Kbytes
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
PARTS.REPORT_NUMBER asc,
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,=20
Sort cost : 1
=20
Total cost : 36

--------------------------------------------------

In case if there are more records than 5000, it saves off record number
and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part
of the where clause on reopening the cursor?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <9c035799-1510-429d-ab35-0bc674f0449dn@googlegroups.com>, abolkini@gmail.com says...

On Friday, August 11, 2023 at 2:59:09=E2=80=AFPM UTC-7, JShepherd wrote:

In article <e10c4470-30c4-437b...@googlegroups.com>,=20
abol...@gmail.com says...=20

=20
I have a SQL cursor that is used to retrieve records from parts table. T= >he =3D=20
program is designed to return no more than 5000 records per read, then i= >t c=3D=20
losed the cursor.=3D20=20
=20
In case if there are more records than 5000, it saves off record number = >500=3D
1 and use it to reopen the cursor and get the rest of the records.=20
=20
The issue appears to be not all records is returned on the second read.= >=20
=20
The cursor look like this=20
=20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D20
, WAREHOUSE=20
, REPORT_NUMBER=20
, SOURCE_OF_SUPPLY=20
, PART_NUM=20
, BIN_LOC=20

FROM =3D3DPARTS FOR BROWSE ACCESS=20
WHERE COMPANY =3D3D :SEARCH-COMPANY AND=20
WAREHOUSE =3D3D :SEARCH-WAREHOUSE AND=20
REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMBER AND=20
SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS AND=20
PART_NUM >=3D3D :SEARCH-PART-NUM AND=20
BIN_LOC >=3D3D :SEARCH-BIN-LOC AND=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM= >,=3D=20
BIN_LOC, ALT_BIN ASC=20
END-EXEC.=20
=20
On the second read/Open cursor, it returns the record which was saved of= >f =3D=20
to use it to reopen the cursor. but then it skips a bunch of records. I = >was=3D=20
hoping someone would be able to tell me what am i doing wrong in the cu= >rso=3D
r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"=20
=20
What are the keycols for the table ?=20
=20
What does an explain plan for the statement show ?

Happy Monday,

Thanks for taking the time to reply back. The key is the selected columns= >=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=
T NULL
, WARE CHAR(3) NO DEFAULT NOT NULL
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
, PART_NUM CHAR(30) NO DEFAULT NOT NULL
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL

Since you brought up the ORDER BY and explain plan, I noticed the key of th= >e table is in ascending order and exactly is my ORDER BY columns, so I remo= >ved ORDER BY from the cursor but It still skipped records.=20

Here is EXPLAIN plan:

Operation 1.0 : Scan
Table PARTS
with correlation name PARTS
Access type : No locks, browse access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 19 out of 20 columns
=20
Access path 1 : Primary, sequential cache
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D :SEARCH-COMPANY , WARE =3D :SEARCH-WAR=
E ,=20
REPORT_NUMBER =3D :SEARCH-REPORT-NUMB=
ER
End key pred. : COMPANY =3D :SEARCH-COMPANY , WARE =3D
:SEARCH-WARE , REPORT_NUMBER =3D :SEARCH-REPORT-N=
UMBER
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D :SEARCH-SOS ) AND ( BIN_=
LOC >=3D :SEARCH-BIN-LOC )
AND ( PART_NUM >=3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=20
Executor pred. : None
Table selectivity : Expect to select 1.2341% of rows from table
Expected row count: 5 rows after the scan
Operation cost : 35
=20
Operation 1.1 : Sort
Requested : Explicitly in the query
Sort rows in the : Result of a Select
Purpose : To order rows for an Order By
Sort technique : FASTSORT
Sort type : Plan to use User Process Sort
UPS workspace : 34 Kbytes
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
PARTS.REPORT_NUMBER asc,
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,=20
Sort cost : 1
=20
Total cost : 36

Was "ORDER BY COMPANY_GROUP" a typo in the original query text ?

What was the deal with the messed up where clause in the original query ?

What is the current query text ?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Tuesday, August 15, 2023 at 10:48:43 AM UTC-7, JShepherd wrote:

In article <9c035799-1510-429d...@googlegroups.com>,
abol...@gmail.com says...

On Friday, August 11, 2023 at 2:59:09=E2=80=AFPM UTC-7, JShepherd wrote:

In article <e10c4470-30c4-437b...@googlegroups.com>,=20
abol...@gmail.com says...=20

=20
I have a SQL cursor that is used to retrieve records from parts table. T=

he =3D=20

program is designed to return no more than 5000 records per read, then i=

t c=3D=20

losed the cursor.=3D20=20
=20
In case if there are more records than 5000, it saves off record number =

500=3D

1 and use it to reopen the cursor and get the rest of the records.=20
=20
The issue appears to be not all records is returned on the second read.= >=20
=20
The cursor look like this=20
=20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D20
, WAREHOUSE=20
, REPORT_NUMBER=20
, SOURCE_OF_SUPPLY=20
, PART_NUM=20
, BIN_LOC=20

FROM =3D3DPARTS FOR BROWSE ACCESS=20
WHERE COMPANY =3D3D :SEARCH-COMPANY AND=20
WAREHOUSE =3D3D :SEARCH-WAREHOUSE AND=20
REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMBER AND=20
SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS AND=20
PART_NUM >=3D3D :SEARCH-PART-NUM AND=20
BIN_LOC >=3D3D :SEARCH-BIN-LOC AND=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_NUM=

,=3D=20

BIN_LOC, ALT_BIN ASC=20
END-EXEC.=20
=20
On the second read/Open cursor, it returns the record which was saved of=

f =3D=20

to use it to reopen the cursor. but then it skips a bunch of records. I =

was=3D=20

hoping someone would be able to tell me what am i doing wrong in the cu=

rso=3D

r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"=20 >>=20
What are the keycols for the table ?=20
=20
What does an explain plan for the statement show ?

Happy Monday,

Thanks for taking the time to reply back. The key is the selected columns= >=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=
T NULL
, WARE CHAR(3) NO DEFAULT NOT NULL
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL
, PART_NUM CHAR(30) NO DEFAULT NOT NULL
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL

Since you brought up the ORDER BY and explain plan, I noticed the key of th=
e table is in ascending order and exactly is my ORDER BY columns, so I remo=
ved ORDER BY from the cursor but It still skipped records.=20

Here is EXPLAIN plan:

Operation 1.0 : Scan
Table PARTS
with correlation name PARTS
Access type : No locks, browse access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 19 out of 20 columns
=20
Access path 1 : Primary, sequential cache
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D :SEARCH-COMPANY , WARE =3D :SEARCH-WAR=
E ,=20
REPORT_NUMBER =3D :SEARCH-REPORT-NUMB=
ER
End key pred. : COMPANY =3D :SEARCH-COMPANY , WARE =3D
:SEARCH-WARE , REPORT_NUMBER =3D :SEARCH-REPORT-N=
UMBER
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D :SEARCH-SOS ) AND ( BIN_=
LOC >=3D :SEARCH-BIN-LOC )
AND ( PART_NUM >=3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=20
Executor pred. : None
Table selectivity : Expect to select 1.2341% of rows from table
Expected row count: 5 rows after the scan
Operation cost : 35
=20
Operation 1.1 : Sort
Requested : Explicitly in the query
Sort rows in the : Result of a Select
Purpose : To order rows for an Order By
Sort technique : FASTSORT
Sort type : Plan to use User Process Sort
UPS workspace : 34 Kbytes
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,
PARTS.REPORT_NUMBER asc,
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,=20
Sort cost : 1
=20
Total cost : 36

--------------------------------------------------

In case if there are more records than 5000, it saves off record number >>and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part
of the where clause on reopening the cursor?

When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5000 record limit will cause the program to save off the data for record 5001 and close the cursor. The second read will use the saved off the data as a starting point to read the
next 5000 records. So record number is not a part of the table and it's not used in the WHERE clause. It's an internal program counter to know when to stop reading as the page limit is set to 5000 records. Thanks again for your help!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <3a8f4387-ed26-450b-81d3-871223d45198n@googlegroups.com>, abolkini@gmail.com says...

On Tuesday, August 15, 2023 at 10:48:43=E2=80=AFAM UTC-7, JShepherd wrote:

In article <9c035799-1510-429d...@googlegroups.com>,=20
abol...@gmail.com says...=20

=20
On Friday, August 11, 2023 at 2:59:09=3DE2=3D80=3DAFPM UTC-7, JShepherd = >wrote:=20

In article <e10c4470-30c4-437b...@googlegroups.com>,=3D20=20
abol...@gmail.com says...=3D20=20

=3D20=20
I have a SQL cursor that is used to retrieve records from parts table= >. T=3D=20

he =3D3D=3D20=20

program is designed to return no more than 5000 records per read, the= >n i=3D=20

t c=3D3D=3D20=20

losed the cursor.=3D3D20=3D20=20
=3D20=20
In case if there are more records than 5000, it saves off record numb= >er =3D=20

500=3D3D=20

1 and use it to reopen the cursor and get the rest of the records.=3D= >20=20
=3D20=20
The issue appears to be not all records is returned on the second rea= >d.=3D=20

=3D20=20

=3D20=20
The cursor look like this=3D20=20
=3D20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D3D20=20
, WAREHOUSE=3D20=20
, REPORT_NUMBER=3D20=20
, SOURCE_OF_SUPPLY=3D20=20
, PART_NUM=3D20=20
, BIN_LOC=3D20=20
=20
FROM =3D3D3DPARTS FOR BROWSE ACCESS=3D20=20
WHERE COMPANY =3D3D3D :SEARCH-COMPANY AND=3D20=20
WAREHOUSE =3D3D3D :SEARCH-WAREHOUSE AND=3D20=20
REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMBER AND=3D20=20
SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS AND=3D20=20
PART_NUM >=3D3D3D :SEARCH-PART-NUM AND=3D20=20
BIN_LOC >=3D3D3D :SEARCH-BIN-LOC AND=3D20=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_= >NUM=3D=20

,=3D3D=3D20=20

BIN_LOC, ALT_BIN ASC=3D20=20
END-EXEC.=3D20=20
=3D20
On the second read/Open cursor, it returns the record which was saved=

of=3D=20

f =3D3D=3D20=20

to use it to reopen the cursor. but then it skips a bunch of records.=

I =3D=20

was=3D3D=3D20=20

hoping someone would be able to tell me what am i doing wrong in the=

cu=3D=20

rso=3D3D

r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"=3D2= >0=20
=3D20=20
What are the keycols for the table ?=3D20=20
=3D20
What does an explain plan for the statement show ?=20

=20
Happy Monday,=20

Thanks for taking the time to reply back. The key is the selected column= >s=3D=20
=3D20=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D
T NULL=20
, WARE CHAR(3) NO DEFAULT NOT NULL=20
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=20
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=20
, PART_NUM CHAR(30) NO DEFAULT NOT NULL=20
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL=20
=20

Since you brought up the ORDER BY and explain plan, I noticed the key of=

th=3D=20

e table is in ascending order and exactly is my ORDER BY columns, so I r= >emo=3D=20
ved ORDER BY from the cursor but It still skipped records.=3D20
=20
Here is EXPLAIN plan:=20
=20
Operation 1.0 : Scan=20
Table PARTS=20
with correlation name PARTS=20
Access type : No locks, browse access=20
Lock mode : Chosen by the system=20
Column processing : Requires retrieval of 19 out of 20 columns
=3D20
Access path 1 : Primary, sequential cache=20
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D3D :SEARCH-COMPANY , WARE =3D3D :SEARCH-WAR= >=3D=20
E ,=3D20=20
REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMB=3D=20
ER=20
End key pred. : COMPANY =3D3D :SEARCH-COMPANY , WARE =3D3D=20
:SEARCH-WARE , REPORT_NUMBER =3D3D :SEARCH-REPORT-N=3D=20
UMBER
Index selectivity : Expect to examine 100% of rows from table=20
Index pred. : None=20
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS ) AND ( BIN_=3D
LOC >=3D3D :SEARCH-BIN-LOC )=20
AND ( PART_NUM >=3D3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=3D20
Executor pred. : None=20
Table selectivity : Expect to select 1.2341% of rows from table=20
Expected row count: 5 rows after the scan=20
Operation cost : 35
=3D20
Operation 1.1 : Sort=20
Requested : Explicitly in the query=20
Sort rows in the : Result of a Select=20
Purpose : To order rows for an Order By=20
Sort technique : FASTSORT=20
Sort type : Plan to use User Process Sort=20
UPS workspace : 34 Kbytes=20
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=20
PARTS.REPORT_NUMBER asc,=20
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,=3D20=20
Sort cost : 1=20
=3D20=20
Total cost : 36

--------------------------------------------------

In case if there are more records than 5000, it saves off record number
and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part=20
of the where clause on reopening the cursor?

When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5= >000 record limit will cause the program to save off the data for record 50= >01 and close the cursor. The second read will use the saved off the data as=
a starting point to read the next 5000 records. So record number is not a =
part of the table and it's not used in the WHERE clause. It's an internal p= >rogram counter to know when to stop reading as the page limit is set to 500= >0 records. Thanks again for your help!

In the absence of any sample data,
I loaded a few rows with the variable part of the key as

SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
thru
SS P000000099 BIN000099

----------------------------
-- start the query

set param ?company 12;
set param ?warehouse "W12";
set param ?report_number 1234;

set param ?source_of_supply " ";
set param ?part_num " ";
set param ?bin_loc " ";

prepare s1 from

SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC
FROM =parts FOR BROWSE ACCESS
WHERE COMPANY = ?company and
WAREHOUSE = ?warehouse and
REPORT_NUMBER = ?report_number and
SOURCE_OF_SUPPLY >= ?source_of_supply and
PART_NUM >= ?part_num and
BIN_LOC >= ?bin_loc
;

--- SQL command prepared.

execute s1;

SOURCE_OF_SUPPLY PART_NUM BIN_LOC
---------------- ---------- ---------

SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
SS P000000004 BIN000004
SS P000000005 BIN000005
SS P000000006 BIN000006
SS P000000007 BIN000007
SS P000000008 BIN000008
SS P000000009 BIN000009

Assuming that 10 rows are fetched per open cursor
and the last row fetched was

set param ?source_of_supply "SS";
set param ?part_num "P000000010";
set param ?bin_loc "zzzzzzzzz";

The variable parts of the key is the problem
SOURCE_OF_SUPPLY >= ?source_of_supply and
PART_NUM >= ?part_num and
BIN_LOC >= ?bin_loc

The high value of the bin_loc restart value prevents any more rows
from being fetched, at least in this test data,
because th

On Thursday, August 17, 2023 at 5:34:45 PM UTC-7, JShepherd wrote:

In article <3a8f4387-ed26-450b...@googlegroups.com>,
abol...@gmail.com says...

On Tuesday, August 15, 2023 at 10:48:43=E2=80=AFAM UTC-7, JShepherd wrote: >> In article <9c035799-1510-429d...@googlegroups.com>,=20

abol...@gmail.com says...=20

=20
On Friday, August 11, 2023 at 2:59:09=3DE2=3D80=3DAFPM UTC-7, JShepherd =

wrote:=20

In article <e10c4470-30c4-437b...@googlegroups.com>,=3D20=20
abol...@gmail.com says...=3D20=20

=3D20=20
I have a SQL cursor that is used to retrieve records from parts table=

. T=3D=20

he =3D3D=3D20=20

program is designed to return no more than 5000 records per read, the=

n i=3D=20

t c=3D3D=3D20=20

losed the cursor.=3D3D20=3D20=20
=3D20=20
In case if there are more records than 5000, it saves off record numb=

er =3D=20

500=3D3D=20

1 and use it to reopen the cursor and get the rest of the records.=3D=

20=20

=3D20=20
The issue appears to be not all records is returned on the second rea=

d.=3D=20

=3D20=20

=3D20=20
The cursor look like this=3D20=20
=3D20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D3D20=20
, WAREHOUSE=3D20=20
, REPORT_NUMBER=3D20=20
, SOURCE_OF_SUPPLY=3D20=20
, PART_NUM=3D20=20
, BIN_LOC=3D20=20
=20
FROM =3D3D3DPARTS FOR BROWSE ACCESS=3D20=20
WHERE COMPANY =3D3D3D :SEARCH-COMPANY AND=3D20=20
WAREHOUSE =3D3D3D :SEARCH-WAREHOUSE AND=3D20=20
REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMBER AND=3D20=20
SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS AND=3D20=20
PART_NUM >=3D3D3D :SEARCH-PART-NUM AND=3D20=20
BIN_LOC >=3D3D3D :SEARCH-BIN-LOC AND=3D20=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_=

NUM=3D=20

,=3D3D=3D20=20

BIN_LOC, ALT_BIN ASC=3D20=20
END-EXEC.=3D20=20
=3D20
On the second read/Open cursor, it returns the record which was saved=

of=3D=20

f =3D3D=3D20=20

to use it to reopen the cursor. but then it skips a bunch of records.=

I =3D=20

was=3D3D=3D20=20

hoping someone would be able to tell me what am i doing wrong in the=

cu=3D=20

rso=3D3D

r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"=3D2=

0=20

=3D20=20
What are the keycols for the table ?=3D20=20
=3D20
What does an explain plan for the statement show ?=20

=20
Happy Monday,=20

Thanks for taking the time to reply back. The key is the selected column=

s=3D=20

=3D20=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D
T NULL=20
, WARE CHAR(3) NO DEFAULT NOT NULL=20
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=20
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=20
, PART_NUM CHAR(30) NO DEFAULT NOT NULL=20
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL=20
=20

Since you brought up the ORDER BY and explain plan, I noticed the key of=

th=3D=20

e table is in ascending order and exactly is my ORDER BY columns, so I r=

emo=3D=20

ved ORDER BY from the cursor but It still skipped records.=3D20
=20
Here is EXPLAIN plan:=20
=20
Operation 1.0 : Scan=20
Table PARTS=20
with correlation name PARTS=20
Access type : No locks, browse access=20
Lock mode : Chosen by the system=20
Column processing : Requires retrieval of 19 out of 20 columns
=3D20
Access path 1 : Primary, sequential cache=20
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D3D :SEARCH-COMPANY , WARE =3D3D :SEARCH-WAR=

=3D=20

E ,=3D20=20
REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMB=3D=20
ER=20
End key pred. : COMPANY =3D3D :SEARCH-COMPANY , WARE =3D3D=20
:SEARCH-WARE , REPORT_NUMBER =3D3D :SEARCH-REPORT-N=3D=20
UMBER
Index selectivity : Expect to examine 100% of rows from table=20
Index pred. : None=20
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS ) AND ( BIN_=3D
LOC >=3D3D :SEARCH-BIN-LOC )=20
AND ( PART_NUM >=3D3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=3D20
Executor pred. : None=20
Table selectivity : Expect to select 1.2341% of rows from table=20
Expected row count: 5 rows after the scan=20
Operation cost : 35
=3D20
Operation 1.1 : Sort=20
Requested : Explicitly in the query=20
Sort rows in the : Result of a Select=20
Purpose : To order rows for an Order By=20
Sort technique : FASTSORT=20
Sort type : Plan to use User Process Sort=20
UPS workspace : 34 Kbytes=20
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=20
PARTS.REPORT_NUMBER asc,=20
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,=3D20=20
Sort cost : 1=20
=3D20=20
Total cost : 36

--------------------------------------------------

In case if there are more records than 5000, it saves off record number >> >>and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part=20
of the where clause on reopening the cursor?

When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5= >000 record limit will cause the program to save off the data for record 50= >01 and close the cursor. The second read will use the saved off the data as=
a starting point to read the next 5000 records. So record number is not a =
part of the table and it's not used in the WHERE clause. It's an internal p=
rogram counter to know when to stop reading as the page limit is set to 500= >0 records. Thanks again for your help!

In the absence of any sample data,
I loaded a few rows with the variable part of the key as

SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
thru
SS P000000099 BIN000099

----------------------------
-- start the query

set param ?company 12;
set param ?warehouse "W12";
set param ?report_number 1234;

set param ?source_of_supply " ";
set param ?part_num " ";
set param ?bin_loc " ";

prepare s1 from

SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC
FROM =parts FOR BROWSE ACCESS
WHERE COMPANY = ?company and
WAREHOUSE = ?warehouse and
REPORT_NUMBER = ?report_number and
SOURCE_OF_SUPPLY >= ?source_of_supply and
PART_NUM >= ?part_num and
BIN_LOC >= ?bin_loc
;

--- SQL command prepared.

execute s1;

SOURCE_OF_SUPPLY PART_NUM BIN_LOC
---------------- ---------- ---------

SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
SS P000000004 BIN000004
SS P000000005 BIN000005
SS P000000006 BIN000006
SS P000000007 BIN000007
SS P000000008 BIN000008
SS P000000009 BIN000009

Assuming that 10 rows are fetched per open cursor
and the last row fetched was

set param ?source_of_supply "SS";
set param ?part_num "P000000010";
set param ?bin_loc "zzzzzzzzz";

The variable parts of the key is the problem
SOURCE_OF_SUPPLY >= ?source_of_supply and
PART_NUM >= ?part_num and
BIN_LOC >= ?bin_loc

The high value of the bin_loc restart value prevents any more rows
from being fetched, at least in this test data,
because the three cols are AND'd. .

With a more random bin_loc you would likely skip subsequent rows with
a bin_loc value less than the restart value

execute s1;

--- 0 row(s) selected.

Thank you again for your help. I noticed if I remove the bin-_loc I get more records back.
I am not sure what is the solution of this paging issue. In my mind I am giving the cursor a starting point on the second select. If both have the same order by to retrieve data, why the second select is not working. I still can not get it and that's
preventing me from solving the issue. Would you please shed any light on how to fix this. Thanks in advance and have a wonderful weekend!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, August 18, 2023 at 8:56:11 p.m. UTC-4, Adam “Adamlivinlife” Kamal wrote:

On Thursday, August 17, 2023 at 5:34:45 PM UTC-7, JShepherd wrote:

In article <3a8f4387-ed26-450b...@googlegroups.com>,
abol...@gmail.com says...

On Tuesday, August 15, 2023 at 10:48:43=E2=80=AFAM UTC-7, JShepherd wrote:

In article <9c035799-1510-429d...@googlegroups.com>,=20
abol...@gmail.com says...=20

=20
On Friday, August 11, 2023 at 2:59:09=3DE2=3D80=3DAFPM UTC-7, JShepherd =

wrote:=20

In article <e10c4470-30c4-437b...@googlegroups.com>,=3D20=20
abol...@gmail.com says...=3D20=20

=3D20=20
I have a SQL cursor that is used to retrieve records from parts table=

. T=3D=20

he =3D3D=3D20=20

program is designed to return no more than 5000 records per read, the=

n i=3D=20

t c=3D3D=3D20=20

losed the cursor.=3D3D20=3D20=20
=3D20=20
In case if there are more records than 5000, it saves off record numb=

er =3D=20

500=3D3D=20

1 and use it to reopen the cursor and get the rest of the records.=3D=

20=20

=3D20=20
The issue appears to be not all records is returned on the second rea=

d.=3D=20

=3D20=20

=3D20=20
The cursor look like this=3D20=20
=3D20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D3D20=20
, WAREHOUSE=3D20=20
, REPORT_NUMBER=3D20=20
, SOURCE_OF_SUPPLY=3D20=20
, PART_NUM=3D20=20
, BIN_LOC=3D20=20
=20
FROM =3D3D3DPARTS FOR BROWSE ACCESS=3D20=20
WHERE COMPANY =3D3D3D :SEARCH-COMPANY AND=3D20=20
WAREHOUSE =3D3D3D :SEARCH-WAREHOUSE AND=3D20=20
REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMBER AND=3D20=20
SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS AND=3D20=20
PART_NUM >=3D3D3D :SEARCH-PART-NUM AND=3D20=20
BIN_LOC >=3D3D3D :SEARCH-BIN-LOC AND=3D20=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PART_=

NUM=3D=20

,=3D3D=3D20=20

BIN_LOC, ALT_BIN ASC=3D20=20
END-EXEC.=3D20=20
=3D20
On the second read/Open cursor, it returns the record which was saved=

of=3D=20

f =3D3D=3D20=20

to use it to reopen the cursor. but then it skips a bunch of records.=

I =3D=20

was=3D3D=3D20=20

hoping someone would be able to tell me what am i doing wrong in the=

cu=3D=20

rso=3D3D

r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"=3D2=

0=20

=3D20=20
What are the keycols for the table ?=3D20=20
=3D20
What does an explain plan for the statement show ?=20

=20
Happy Monday,=20

Thanks for taking the time to reply back. The key is the selected column=

s=3D=20

=3D20=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D
T NULL=20
, WARE CHAR(3) NO DEFAULT NOT NULL=20
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=20
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=20
, PART_NUM CHAR(30) NO DEFAULT NOT NULL=20
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL=20
=20

Since you brought up the ORDER BY and explain plan, I noticed the key of=

th=3D=20

e table is in ascending order and exactly is my ORDER BY columns, so I r=

emo=3D=20

ved ORDER BY from the cursor but It still skipped records.=3D20
=20
Here is EXPLAIN plan:=20
=20
Operation 1.0 : Scan=20
Table PARTS=20
with correlation name PARTS=20
Access type : No locks, browse access=20
Lock mode : Chosen by the system=20
Column processing : Requires retrieval of 19 out of 20 columns
=3D20
Access path 1 : Primary, sequential cache=20
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D3D :SEARCH-COMPANY , WARE =3D3D :SEARCH-WAR=

=3D=20

E ,=3D20=20
REPORT_NUMBER =3D3D :SEARCH-REPORT-NUMB=3D=20
ER=20
End key pred. : COMPANY =3D3D :SEARCH-COMPANY , WARE =3D3D=20
:SEARCH-WARE , REPORT_NUMBER =3D3D :SEARCH-REPORT-N=3D=20
UMBER
Index selectivity : Expect to examine 100% of rows from table=20
Index pred. : None=20
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D3D :SEARCH-SOS ) AND ( BIN_=3D
LOC >=3D3D :SEARCH-BIN-LOC )=20
AND ( PART_NUM >=3D3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=3D20
Executor pred. : None=20
Table selectivity : Expect to select 1.2341% of rows from table=20
Expected row count: 5 rows after the scan=20
Operation cost : 35
=3D20
Operation 1.1 : Sort=20
Requested : Explicitly in the query=20
Sort rows in the : Result of a Select=20
Purpose : To order rows for an Order By=20
Sort technique : FASTSORT=20
Sort type : Plan to use User Process Sort=20
UPS workspace : 34 Kbytes=20
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=20
PARTS.REPORT_NUMBER asc,=20
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,
PARTS.BIN_LOC asc,=3D20=20
Sort cost : 1=20
=3D20=20
Total cost : 36

--------------------------------------------------

In case if there are more records than 5000, it saves off record number
and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part=20
of the where clause on reopening the cursor?

When the cursor is opened, it will fetch up to 5000 record, on 5001, the 5=
000 record limit will cause the program to save off the data for record 50=
01 and close the cursor. The second read will use the saved off the data as=
a starting point to read the next 5000 records. So record number is not a =
part of the table and it's not used in the WHERE clause. It's an internal p=
rogram counter to know when to stop reading as the page limit is set to 500=
0 records. Thanks again for your help!

In the absence of any sample data,
I loaded a few rows with the variable part of the key as

SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
thru
SS P000000099 BIN000099

----------------------------
-- start the query

set param ?company 12;
set param ?warehouse "W12";
set param ?report_number 1234;

set param ?source_of_supply " ";
set param ?part_num " ";
set param ?bin_loc " ";

prepare s1 from

SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC
FROM =parts FOR BROWSE ACCESS
WHERE COMPANY = ?company and
WAREHOUSE = ?warehouse and
REPORT_NUMBER = ?report_number and
SOURCE_OF_SUPPLY >= ?source_of_supply and
PART_NUM >= ?part_num and
BIN_LOC >= ?bin_loc
;

--- SQL command prepared.

execute s1;

SOURCE_OF_SUPPLY PART_NUM BIN_LOC
---------------- ---------- ---------

SS P000000000 BIN000000
SS P000000001 BIN000001
SS P000000002 BIN000002
SS P000000003 BIN000003
SS P000000004 BIN000004
SS P000000005 BIN000005
SS P000000006 BIN000006
SS P000000007 BIN000007
SS P000000008 BIN000008
SS P000000009 BIN000009

Assuming that 10 rows are fetched per open cursor
and the last row fetched was

set param ?source_of_supply "SS";
set param ?part_num "P000000010";
set param ?bin_loc "zzzzzzzzz";

The variable parts of the key is the problem
SOURCE_OF_SUPPLY >= ?source_of_supply and
PART_NUM >= ?part_num and
BIN_LOC >= ?bin_loc

The high value of the bin_loc restart value prevents any more rows
from being fetched, at least in this test data,
because the three cols are AND'd. .

With a more random bin_loc you would likely skip subsequent rows with
a bin_loc value less than the restart value

execute s1;

--- 0 row(s) selected.

Thank you again for your help. I noticed if I remove the bin-_loc I get more records back.
I am not sure what is the solution of this paging issue. In my mind I am giving the cursor a starting point on the second select. If both have the same order by to retrieve data, why the second select is not working. I still can not get it and that's

preventing me from solving the issue. Would you please shed any light on how to fix this. Thanks in advance and have a wonderful weekend!

You should be aware that BROWSE ACCESS does not necessarily give you accurate results. READ COMMITTED is more reliable. I cannot say one way or another whether this will make a difference but no production code (in my opinion) should use BROWSE ACCESS as
the results are questionable and not ever guaranteed to be consistent with what is in the TMF Audit Trail - assuming the tables are audited.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>, abolkini@gmail.com says...

On Thursday, August 17, 2023 at 5:34:45=E2=80=AFPM UTC-7, JShepherd wrote:

In article <3a8f4387-ed26-450b...@googlegroups.com>,=20
abol...@gmail.com says...=20

=20
On Tuesday, August 15, 2023 at 10:48:43=3DE2=3D80=3DAFAM UTC-7, JShepher= >d wrote:=20

In article <9c035799-1510-429d...@googlegroups.com>,=3D20=20
abol...@gmail.com says...=3D20=20

=3D20=20
On Friday, August 11, 2023 at 2:59:09=3D3DE2=3D3D80=3D3DAFPM UTC-7, J= >Shepherd =3D=20

wrote:=3D20=20

In article <e10c4470-30c4-437b...@googlegroups.com>,=3D3D20=3D20=20
abol...@gmail.com says...=3D3D20=3D20=20

=3D3D20=3D20=20
I have a SQL cursor that is used to retrieve records from parts ta= >ble=3D=20

. T=3D3D=3D20=20

he =3D3D3D=3D3D20=3D20=20

program is designed to return no more than 5000 records per read, = >the=3D=20

n i=3D3D=3D20=20

t c=3D3D3D=3D3D20=3D20=20

losed the cursor.=3D3D3D20=3D3D20=3D20=20
=3D3D20=3D20=20
In case if there are more records than 5000, it saves off record n= >umb=3D=20

er =3D3D=3D20=20

500=3D3D3D=3D20=20

1 and use it to reopen the cursor and get the rest of the records.= >=3D3D=3D=20

20=3D20=20

=3D3D20=3D20=20
The issue appears to be not all records is returned on the second = >rea=3D=20

d.=3D3D=3D20=20

=3D3D20=3D20=20

=3D3D20=3D20=20
The cursor look like this=3D3D20=3D20=20
=3D3D20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D3D3D20=3D20=20
, WAREHOUSE=3D3D20=3D20=20
, REPORT_NUMBER=3D3D20=3D20=20
, SOURCE_OF_SUPPLY=3D3D20=3D20=20
, PART_NUM=3D3D20=3D20=20
, BIN_LOC=3D3D20=3D20=20
=3D20=20
FROM =3D3D3D3DPARTS FOR BROWSE ACCESS=3D3D20=3D20=20
WHERE COMPANY =3D3D3D3D :SEARCH-COMPANY AND=3D3D20=3D20=20
WAREHOUSE =3D3D3D3D :SEARCH-WAREHOUSE AND=3D3D20=3D20=20
REPORT_NUMBER =3D3D3D3D :SEARCH-REPORT-NUMBER AND=3D3D20=3D20=20
SOURCE_OF_SUPPLY >=3D3D3D3D :SEARCH-SOS AND=3D3D20=3D20=20
PART_NUM >=3D3D3D3D :SEARCH-PART-NUM AND=3D3D20=3D20=20
BIN_LOC >=3D3D3D3D :SEARCH-BIN-LOC AND=3D3D20=3D20=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY, PA= >RT_=3D=20

NUM=3D3D=3D20=20

,=3D3D3D=3D3D20=3D20=20

BIN_LOC, ALT_BIN ASC=3D3D20=3D20=20
END-EXEC.=3D3D20=3D20=20
=3D3D20=20
On the second read/Open cursor, it returns the record which was sa= >ved=3D=20

of=3D3D=3D20=20

f =3D3D3D=3D3D20=3D20=20

to use it to reopen the cursor. but then it skips a bunch of recor= >ds.=3D=20

I =3D3D=3D20=20

was=3D3D3D=3D3D20=3D20=20

hoping someone would be able to tell me what am i doing wrong in = >the=3D=20

cu=3D3D=3D20=20

rso=3D3D3D

r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"= >=3D3D2=3D=20

0=3D20=20

=3D3D20=3D20=20
What are the keycols for the table ?=3D3D20=3D20=20
=3D3D20=20
What does an explain plan for the statement show ?=3D20=20

=3D20=20
Happy Monday,=3D20=20
=20
Thanks for taking the time to reply back. The key is the selected col= >umn=3D=20

s=3D3D=3D20=20

=3D3D20=3D20=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D3D=20
T NULL=3D20=20
, WARE CHAR(3) NO DEFAULT NOT NULL=3D20=20
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=3D20=20
, SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=3D20=20
, PART_NUM CHAR(30) NO DEFAULT NOT NULL=3D20=20
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL=3D20=20
=3D20=20
=20
Since you brought up the ORDER BY and explain plan, I noticed the key=

of=3D=20

th=3D3D=3D20=20

e table is in ascending order and exactly is my ORDER BY columns, so = >I r=3D=20

emo=3D3D=3D20=20

ved ORDER BY from the cursor but It still skipped records.=3D3D20=20
=3D20=20
Here is EXPLAIN plan:=3D20=20
=3D20=20
Operation 1.0 : Scan=3D20=20
Table PARTS=3D20=20
with correlation name PARTS=3D20=20
Access type : No locks, browse access=3D20=20
Lock mode : Chosen by the system=3D20
Column processing : Requires retrieval of 19 out of 20 columns
=3D3D20=20
Access path 1 : Primary, sequential cache=3D20
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D3D3D :SEARCH-COMPANY , WARE =3D3D3D :SEA= >RCH-WAR=3D=20

=3D3D=3D20=20

E ,=3D3D20=3D20=20
REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMB=3D3D=3D20=20
ER=3D20=20
End key pred. : COMPANY =3D3D3D :SEARCH-COMPANY , WARE =3D3D3D=3D20= >=20
:SEARCH-WARE , REPORT_NUMBER =3D3D3D :SEARCH-REPORT-N=3D3D=3D20=20
UMBER=20
Index selectivity : Expect to examine 100% of rows from table=3D20= >=20
Index pred. : None=3D20
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS ) AND ( BIN_=3D3D=20
LOC >=3D3D3D :SEARCH-BIN-LOC )=3D20=20
AND ( PART_NUM >=3D3D3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=3D3D20=20
Executor pred. : None=3D20=20
Table selectivity : Expect to select 1.2341% of rows from table=3D20= >=20
Expected row count: 5 rows after the scan=3D20=20
Operation cost : 35=20
=3D3D20=20
Operation 1.1 : Sort=3D20=20
Requested : Explicitly in the query=3D20=20
Sort rows in the : Result of a Select=3D20=20
Purpose : To order rows for an Order By=3D20=20
Sort technique : FASTSORT=3D20=20
Sort type : Plan to use User Process Sort=3D20=20
UPS workspace : 34 Kbytes=3D20=20
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=3D20=20
PARTS.REPORT_NUMBER asc,=3D20=20
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,=20
PARTS.BIN_LOC asc,=3D3D20=3D20=20
Sort cost : 1=3D20=20
=3D3D20=3D20
Total cost : 36=20

--------------------------------------------------=20

In case if there are more records than 5000, it saves off record num= >ber=20
and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part=3D20
of the where clause on reopening the cursor?=20

=20

When the cursor is opened, it will fetch up to 5000 record, on 5001, the=

5=3D=20

000 record limit will cause the program to save off the data for record = >50=3D=20
01 and close the cursor. The second read will use the saved off the data=

as=3D=20

a starting point to read the next 5000 records. So record number is not=

a =3D=20

part of the table and it's not used in the WHERE clause. It's an interna= >l p=3D=20
rogram counter to know when to stop reading as the page limit is set to = >500=3D
0 records. Thanks again for your help!

In the absence of any sample data,=20
I loaded a few rows with the variable part of the key as=20
=20
SS P000000000 BIN000000=20
SS P000000001 BIN000001=20
SS P000000002 BIN000002=20
SS P000000003 BIN000003=20
thru=20
SS P000000099 BIN000099=20
=20
----------------------------=20
-- start the query=20
=20
set param ?company 12;=20
set param ?warehouse "W12";=20
set param ?report_number 1234;=20
=20
set param ?source_of_supply " ";=20
set param ?part_num " ";=20
set param ?bin_loc " ";=20
=20
prepare s1 from=20

SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC=20
FROM =3Dparts FOR BROWSE ACCESS=20
WHERE COMPANY =3D ?company and=20
WAREHOUSE =3D ?warehouse and=20
REPORT_NUMBER =3D ?report_number and=20
SOURCE_OF_SUPPLY >=3D ?source_of_supply and=20
PART_NUM >=3D ?part_num and=20
BIN_LOC >=3D ?bin_loc=20
;=20

--- SQL command prepared.=20
=20
execute s1;=20
=20
SOURCE_OF_SUPPLY PART_NUM BIN_LOC=20
---------------- ---------- ---------=20
=20
SS P000000000 BIN000000=20
SS P000000001 BIN000001=20
SS P000000002 BIN000002=20
SS P000000003 BIN000003=20
SS P000000004 BIN000004=20
SS P000000005 BIN000005=20
SS P000000006 BIN000006=20
SS P000000007 BIN000007=20
SS P000000008 BIN000008=20
SS P000000009 BIN000009=20
=20
Assuming that 10 rows are fetched per open cursor=20
and the last row fetched was=20
=20
set param ?source_of_supply "SS";=20
set param ?part_num "P000000010";=20
set param ?bin_loc "zzzzzzzzz";=20
=20
The variable parts of the key is the problem=20
SOURCE_OF_SUPPLY >=3D ?source_of_supply and=20
PART_NUM >=3D ?part_num and=20
BIN_LOC >=3D ?bin_loc=20
=20
The high value of the bin_loc restart value prevents any more rows=20
from being fetched, at least in this test data,=20
because the three cols are AND'd. .=20
=20
With a more random bin_loc you would likely skip subsequent rows with=20
a bin_loc value less than the restart value=20
=20
execute s1;=20
=20
--- 0 row(s) selected.

Thank you again for your help. I noticed if I remove the bin-_loc I get mor= >e records back.
I am not sure what is the solution of this paging issue. In my mind I am g= >iving the cursor a starting point on the second select. If both have the sa= >me order by to retrieve data, why the second select is not working. I still=
can not get it and that's preventing me from solving the issue. Would you =
please shed any light on how to fix this. Thanks in advance and have a won= >derful weekend!=20

Similar issues will occur with varying values of source_of_supply
and part_num

Say source_of_supply goes from 'SS' to 'ST'
and all part_num from 'ST' are less than part_nums from 'SS' .

I was hoping this would treat the three variable cols
as one compound data item for the compare
but explain shows that is not the case

WHERE (COMPANY,WAREHOUSE,REPORT_NUMBER =
?company,?warehouse,?report_number)
and
(SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC >=
?source_of_supply,?part_num,?bin_loc);

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <uc59gn$2vq23$1@dont-email.me>, invalid@nowhere.com says...

In article <14f52dfa-a677-4f2d-b5a7-19847b68d565n@googlegroups.com>, >abolkini@gmail.com says...

On Thursday, August 17, 2023 at 5:34:45=E2=80=AFPM UTC-7, JShepherd wrote: >>> In article <3a8f4387-ed26-450b...@googlegroups.com>,=20

abol...@gmail.com says...=20

=20
On Tuesday, August 15, 2023 at 10:48:43=3DE2=3D80=3DAFAM UTC-7,

JShepher=

d wrote:=20

In article <9c035799-1510-429d...@googlegroups.com>,=3D20=20
abol...@gmail.com says...=3D20=20

=3D20=20
On Friday, August 11, 2023 at 2:59:09=3D3DE2=3D3D80=3D3DAFPM UTC-7,

J=

Shepherd =3D=20

wrote:=3D20=20

In article <e10c4470-30c4-437b...@googlegroups.com>,=3D3D20=3D20=20 >>> >> >> abol...@gmail.com says...=3D3D20=3D20=20

=3D3D20=3D20=20
I have a SQL cursor that is used to retrieve records from parts

ta=

ble=3D=20

. T=3D3D=3D20=20

he =3D3D3D=3D3D20=3D20=20

program is designed to return no more than 5000 records per read,

=

the=3D=20

n i=3D3D=3D20=20

t c=3D3D3D=3D3D20=3D20=20

losed the cursor.=3D3D3D20=3D3D20=3D20=20
=3D3D20=3D20=20
In case if there are more records than 5000, it saves off record

n=

umb=3D=20

er =3D3D=3D20=20

500=3D3D3D=3D20=20

1 and use it to reopen the cursor and get the rest of the

records.=

=3D3D=3D=20

20=3D20=20

=3D3D20=3D20=20
The issue appears to be not all records is returned on the second

=

rea=3D=20

d.=3D3D=3D20=20

=3D3D20=3D20=20

=3D3D20=3D20=20
The cursor look like this=3D3D20=3D20=20
=3D3D20
EXEC SQL DECLARE QUERY_PARTS_COUNT CURSOR FOR
SELECT COMPANY=3D3D3D20=3D20=20
, WAREHOUSE=3D3D20=3D20=20
, REPORT_NUMBER=3D3D20=3D20=20
, SOURCE_OF_SUPPLY=3D3D20=3D20=20
, PART_NUM=3D3D20=3D20=20
, BIN_LOC=3D3D20=3D20=20
=3D20=20
FROM =3D3D3D3DPARTS FOR BROWSE ACCESS=3D3D20=3D20=20
WHERE COMPANY =3D3D3D3D :SEARCH-COMPANY AND=3D3D20=3D20=20
WAREHOUSE =3D3D3D3D :SEARCH-WAREHOUSE AND=3D3D20=3D20=20
REPORT_NUMBER =3D3D3D3D :SEARCH-REPORT-NUMBER AND=3D3D20=3D20=20 >>> >> >> > SOURCE_OF_SUPPLY >=3D3D3D3D :SEARCH-SOS AND=3D3D20=3D20=20
PART_NUM >=3D3D3D3D :SEARCH-PART-NUM AND=3D3D20=3D20=20
BIN_LOC >=3D3D3D3D :SEARCH-BIN-LOC AND=3D3D20=3D20=20
ORDER BY COMPANY_GROUP, WARE ,REPORT_NUMBER, SOURCE_OF_SUPPLY,

PA=

RT_=3D=20

NUM=3D3D=3D20=20

,=3D3D3D=3D3D20=3D20=20

BIN_LOC, ALT_BIN ASC=3D3D20=3D20=20
END-EXEC.=3D3D20=3D20=20
=3D3D20=20
On the second read/Open cursor, it returns the record which was

sa=

ved=3D=20

of=3D3D=3D20=20

f =3D3D3D=3D3D20=3D20=20

to use it to reopen the cursor. but then it skips a bunch of

recor=

ds.=3D=20

I =3D3D=3D20=20

was=3D3D3D=3D3D20=3D20=20

hoping someone would be able to tell me what am i doing wrong in

=

the=3D=20

cu=3D3D=3D20=20

rso=3D3D3D

r. Thanks in advance and have a wonderful weekend !

The where clause shown seems to end with "AND ORDER BY <col list>"= >>=3D3D2=3D=20

0=3D20=20

=3D3D20=3D20=20
What are the keycols for the table ?=3D3D20=3D20=20
=3D3D20=20
What does an explain plan for the statement show ?=3D20=20

=3D20=20
Happy Monday,=3D20=20
=20
Thanks for taking the time to reply back. The key is the selected

col=

umn=3D=20

s=3D3D=3D20=20

=3D3D20=3D20=20
COMPANY NUMERIC( 2, 0) UNSIGNED NO DEFAULT NO=3D3D=20
T NULL=3D20=20
, WARE CHAR(3) NO DEFAULT NOT NULL=3D20=20
, REPORT_NUMBER NUMERIC( 9, 0) UNSIGNED NO DEFAULT NOT NULL=3D20=20 >>> >> > , SOURCE_OF_SUPPLY CHAR(2) NO DEFAULT NOT NULL=3D20=20
, PART_NUM CHAR(30) NO DEFAULT NOT NULL=3D20=20
, BIN_LOC CHAR(9) NO DEFAULT NOT NULL=3D20=20
=3D20=20
=20
Since you brought up the ORDER BY and explain plan, I noticed the

key=

of=3D=20

th=3D3D=3D20=20

e table is in ascending order and exactly is my ORDER BY columns, so

=

I r=3D=20

emo=3D3D=3D20=20

ved ORDER BY from the cursor but It still skipped records.=3D3D20=20 >>> >> >=3D20=20
Here is EXPLAIN plan:=3D20=20
=3D20=20
Operation 1.0 : Scan=3D20=20
Table PARTS=3D20=20
with correlation name PARTS=3D20=20
Access type : No locks, browse access=3D20=20
Lock mode : Chosen by the system=3D20
Column processing : Requires retrieval of 19 out of 20 columns
=3D3D20=20
Access path 1 : Primary, sequential cache=3D20
SBB for reads : Virtual, double buffer
Begin key pred. : COMPANY=3D3D3D :SEARCH-COMPANY , WARE =3D3D3D

:SEA=

RCH-WAR=3D=20

=3D3D=3D20=20

E ,=3D3D20=3D20=20
REPORT_NUMBER =3D3D3D :SEARCH-REPORT-NUMB=3D3D=3D20=20
ER=3D20=20
End key pred. : COMPANY =3D3D3D :SEARCH-COMPANY , WARE =3D3D3D=3D20= >>=20
:SEARCH-WARE , REPORT_NUMBER =3D3D3D :SEARCH-REPORT-N=3D3D=3D20=20
UMBER=20
Index selectivity : Expect to examine 100% of rows from table=3D20= >>=20
Index pred. : None=3D20
Base table pred. : Will be evaluated by the disk process
( SOURCE_OF_SUPPLY >=3D3D3D :SEARCH-SOS ) AND ( BIN_=3D3D=20
LOC >=3D3D3D :SEARCH-BIN-LOC )=3D20=20
AND ( PART_NUM >=3D3D3D :SEARCH-PART-NUM )
Pred. selectivity : Expect to select 1.2341% of rows from table
=3D3D20=20
Executor pred. : None=3D20=20
Table selectivity : Expect to select 1.2341% of rows from

table=3D20=

=20

Expected row count: 5 rows after the scan=3D20=20
Operation cost : 35=20
=3D3D20=20
Operation 1.1 : Sort=3D20=20
Requested : Explicitly in the query=3D20=20
Sort rows in the : Result of a Select=3D20=20
Purpose : To order rows for an Order By=3D20=20
Sort technique : FASTSORT=3D20=20
Sort type : Plan to use User Process Sort=3D20=20
UPS workspace : 34 Kbytes=3D20=20
Sort key columns : PARTS.COMPANY asc, PARTS.WARE asc,=3D20=20
PARTS.REPORT_NUMBER asc,=3D20=20
PARTS.SOURCE_OF_SUPPLY asc, PARTS.PART_NUM asc,=20
PARTS.BIN_LOC asc,=3D3D20=3D20=20
Sort cost : 1=3D20=20
=3D3D20=3D20
Total cost : 36=20

--------------------------------------------------=20

In case if there are more records than 5000, it saves off record

num=

ber=20

and use it to reopen the cursor and get the rest of the records.

Where does record number come from and how does it become part=3D20
of the where clause on reopening the cursor?=20

=20

When the cursor is opened, it will fetch up to 5000 record, on 5001,

the=

5=3D=20

000 record limit will cause the program to save off the data for record

=

50=3D=20

01 and close the cursor. The second read will use the saved off the

data=

as=3D=20

a starting point to read the next 5000 records. So record number is

not=

a =3D=20

part of the table and it's not used in the WHERE clause. It's an

interna=

l p=3D=20

rogram counter to know when to stop reading as the page limit is set to

=

500=3D

0 records. Thanks again for your help!

In the absence of any sample data,=20
I loaded a few rows with the variable part of the key as=20
=20
SS P000000000 BIN000000=20
SS P000000001 BIN000001=20
SS P000000002 BIN000002=20
SS P000000003 BIN000003=20
thru=20
SS P000000099 BIN000099=20
=20
----------------------------=20
-- start the query=20
=20
set param ?company 12;=20
set param ?warehouse "W12";=20
set param ?report_number 1234;=20
=20
set param ?source_of_supply " ";=20
set param ?part_num " ";=20
set param ?bin_loc " ";=20
=20
prepare s1 from=20

SELECT SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC=20
FROM =3Dparts FOR BROWSE ACCESS=20
WHERE COMPANY =3D ?company and=20
WAREHOUSE =3D ?warehouse and=20
REPORT_NUMBER =3D ?report_number and=20
SOURCE_OF_SUPPLY >=3D ?source_of_supply and=20
PART_NUM >=3D ?part_num and=20
BIN_LOC >=3D ?bin_loc=20
;=20

--- SQL command prepared.=20
=20
execute s1;=20
=20
SOURCE_OF_SUPPLY PART_NUM BIN_LOC=20
---------------- ---------- ---------=20
=20
SS P000000000 BIN000000=20
SS P000000001 BIN000001=20
SS P000000002 BIN000002=20
SS P000000003 BIN000003=20
SS P000000004 BIN000004=20
SS P000000005 BIN000005=20
SS P000000006 BIN000006=20
SS P000000007 BIN000007=20
SS P000000008 BIN000008=20
SS P000000009 BIN000009=20
=20
Assuming that 10 rows are fetched per open cursor=20
and the last row fetched was=20
=20
set param ?source_of_supply "SS";=20
set param ?part_num "P000000010";=20
set param ?bin_loc "zzzzzzzzz";=20
=20
The variable parts of the key is the problem=20
SOURCE_OF_SUPPLY >=3D ?source_of_supply and=20
PART_NUM >=3D ?part_num and=20
BIN_LOC >=3D ?bin_loc=20
=20
The high value of the bin_loc restart value prevents any more rows=20
from being fetched, at least in this test data,=20
because the three cols are AND'd. .=20
=20
With a more random bin_loc you would likely skip subsequent rows with=20 >>> a bin_loc value less than the restart value=20
=20
execute s1;=20
=20
--- 0 row(s) selected.

Thank you again for your help. I noticed if I remove the bin-_loc I get

mor=

e records back.
I am not sure what is the solution of this paging issue. In my mind I am

g=

iving the cursor a starting point on the second select. If both have the

sa=

me order by to retrieve data, why the second select is not working. I

still=

can not get it and that's preventing me from solving the issue. Would you

=

please shed any light on how to fix this. Thanks in advance and have a

won=

derful weekend!=20

Similar issues will occur with varying values of source_of_supply
and part_num

Say source_of_supply goes from 'SS' to 'ST'
and all part_num from 'ST' are less than part_nums from 'SS' .

I was hoping this would treat the three variable cols
as one compound data item for the compare
but explain shows that is not the case

WHERE (COMPANY,WAREHOUSE,REPORT_NUMBER =
?company,?warehouse,?report_number)
and
(SOURCE_OF_SUPPLY,PART_NUM,BIN_LOC >=
?source_of_supply,?part_num,?bin_loc);

I think this gets you closer but it is probably incomplete


WHERE (COMPANY,WAREHOUSE,REPORT_NUMBER = ?company,?warehouse,?report_number)
and
(
(SOURCE_OF_SUPPLY = ?source_of_supply
and PART_NUM = ?PART_NUM
and BIN_LOC >= ?BIN_LOC
)
or
(SOURCE_OF_SUPPLY = ?source_of_supply
and PART_NUM > ?PART_NUM
)
or
(SOURCE_OF_SUPPLY > ?source_of_supply )
);

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)