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);