Server Module erroring using Select with option(*STARTKEY *BACKWARDS)

[John]

I'm wanting to get a list of records in descending date order from a logical called ITMBYDTE.

Keys
CONO15 ASCEND
CUSN15 ASCEND
DOCD15 ASCEND
ETYP15 ASCEND
LREF15 ASCEND

Srvroutine Name(GetARTransactionList)
Field_Map For(*INPUT) Field(#CONO15) Parameter_Name(Company)
Field_Map For(*INPUT) Field(#CUSN15) Parameter_Name(Dealer)
List_Map For(*OUTPUT) List(#ARTRansactionsList)

* Get all records from the file using some or all of the key
Select Fields(#SLP15) From_File(ITMBYDTE) Where((#ETYP15 <> 'UC') *And (#ETYP15 <> 'JL') *And (#LREF15.Substring( 1 1 ) <> 'W')) With_Key(#CONO15 #CUSN15 9999999) Generic(*YES) Io_Status(#Io$sts) Io_Error(*NEXT) Issue_Msg(*YES) Options(*STARTKEY *BACKWARDS)

Add_Entry To_List(#ARTRansactionsList)

Endselect

Endroutine

I'm selecting customer code 'PBENDI01' from the webpage but the code keeps reading past 'PBENDI01' reading other dealers then fails back in the View:

Evtroutine Handling(#GetList.Failed) Handled(#handled)
#handled := true
#sys_web.console.log( ('load Of AR Transaction List failed') )
Endroutine

What do I need to do to get this working please?

[MarkDale]

Its not surprising that the SELECT reads past the dealers you want, because that is what the *STARTKEY option does. It positions for the first read, and you have to have a test to stop it - either an *ENDWHERE or something in the SELECT loop.

Maybe the reason the server module is failing is because you have added too many records to the list. You might be able to see that from the job log on the server.

Since you probably want to read all the records for a given key, and test them all, would something like this work?:

(Note no *STARTKEY, no Generic)

* Get all records from the file using some or all of the key
Select Fields(#SLP15) From_File(ITMBYDTE) With_Key(#CONO15 #CUSN15) Io_Status(#Io$sts) Io_Error(*NEXT) Issue_Msg(*YES) Options(*BACKWARDS)

If ((#ETYP15 <> 'UC') *And (#ETYP15 <> 'JL') *And (#LREF15.Substring( 1 1 ) <> 'W'))

Add_Entry To_List(#ARTRansactionsList)

Endif
Endselect

[John]

Hi Mark,

I did as you suggested and it worked. Also, I don't think it liked the date 9999999 (date is a 7 digit date CYYMMDD) as part of the key.

I really appreciate your help
Cheers
John.