I'm running a stored procedure to write a summary record to a table after selecting rows from two other tables and creating the record which is subsequently written. I put in an error message trap where the program was terminating. It stops with the message "Executed to line 71", therefore the select statement is not working.

It also terminates with a fatal runtime error:

ENG0010: EXECNPHANDLER, line 1. Variable has not been assigned a value.

I am having trouble with the "SELECT … by " statements which I have used to replace the Perform statements that I used to use in AREV.

Here is a partial listing of the code starting at line 62

* Pass 1 Open ,"VISITS" To visits Else FSMSG( 'NO VISITS' ) Return End * this is the old perform statement that I had to replace with the Select … by … * Perform "SELECT VISITS by ID WITH VISITDATE >= " :QUOTE(OSDATE):" AND WITH VISITDATE ⇐ " :QUOTE(OEDATE):" (S)" sort_list = "ID" Select "VISITS" by sort_list Setting Cursor Else Call Message_Box(@window, " Executed to Line 71", "Show Error Message", MSG_ICON_EXCLAM$) Return End Gosub LOAD.STATS: * Pass 2 * Open ,"VISITS_HISTORY" To visits Else FSMSG ( 'NO VISITS_HISTORY' ) Return End * this is the SECOND old perform statement that I had to replace with the Select … by * Perform "SELECT VISITS_HISTORY by ID WITH VISITDATE >= " :QUOTE(OSDATE):" AND WITH VISITDATE ⇐ " :QUOTE(OEDATE):" (S)" sort_list = "ID" Select "VISITS_HISTORY" by sort_list Setting Cursor Else Call Message_Box(@window, " Executed to Line 85", "Show Error Message", MSG_ICON_EXCLAM$) Return End Gosub LOAD.STATS: Gosub CALC.MINUTES: Write stat On stats,stat.id dna<6> = stat.id dna<7> = sdate dna<8> = edate WRITE dna ON dnas,client disp1 = ' DONE' If err.count = 0 Then disp2 = err.count "R#4" disp3 = 'Create Stats complete' Call Message_Box(@window, valMessage, "Show error message.", MSG_ICON_EXCLAM$) End Else disp2 = err.count "R#4" disp3 = 'ERRORS FOUND' Call Message_Box(@window, valMessage, "Show error message", MSG_ICON_EXCLAM$) End Unlock ALL Return 1 * LOAD STATS SUBROUTINE LOAD.STATS: 100 loop Readnext id else Return LOCK_IT(visits,id) Read visit From visits,id Else Goto 190 user.id = visit<1> vdate = visit<2> vstart = visit<3> vend = visit<4> vsecs = vend-vstart If vdate >= sdate And vdate ⇐ edate Then * do nothing - continue End Else Goto 190 End * load.stats continues ……. 190 Repeat 200 Return

Perform "SELECT VISITS by ID WITH VISITDATE >= " :QUOTE(OSDATE):" AND WITH VISITDATE ⇐ " :QUOTE(OEDATE)

You can use your statement with the Rlist subroutine

It will have the same effect as the perform statement, namely leaving an active select in cursor 0

You can then use loop / readnext. Below is code which is roughly equivalent to yours, using rlist

* Construct your statement

statement = "SELECT VISITS by ID WITH VISITDATE >= " :QUOTE(OSDATE):" AND WITH VISITDATE <= " :QUOTE(OEDATE)



* Call rlist with a target_activelist



equ target_Activelist$ to 5



call rlist(statement, target_activelist ,'','','')

done = 0

loop

readnext id else done = 1

until done



    LOCK_IT(visits,id)

    Read visit From visits,id then

        user.id = visit<1>

        vdate = visit<2>

        vstart = visit<3>

        vend = visit<4>

        vsecs = vend-vstart

        If vdate >= sdate And vdate <= edate Then



               * load.stats continues .......



         end /* if vdate */



   end /* read */



Repeat

Regarding the error message at the end, are you executing using test_run ?

Or is this an event handler?

I think your error is triggered by the Return statement in your error handling. It is causing your program to exit without returning a value.

Thanks, Bob, I'll try that.

It was an event handler on the Write property of a window (for the "DNA" table) that contained a flag that would cause it to execute.

I think you are right on the "Return" statement. I took that out and it compiled and executed, but …

If I use the "Select filevar " statement option, instead of the 'Select "{File_Name}" By …,' then it executes correctly and yields the expected results. The 'Select "{File_Name}" By ….' causes it to terminate with the else clause(s), so I presume that it is not setting the cursor. However, I will use your recommendations because the way I have it written, without reducing the list by the "with" statements, causes it to read the entire "Visits_History" file needlessly. Just for interest sake, why doesn't the "Select … By …" statement work?

Here is the revised code:

Call Clear_table( "ERRS" )

ClearSelect

* Pass 1 Open ,"VISITS" To visits Else FSMSG('NO VISITS') Return End * Select visits SORT_LIST = "ID" SELECT "VISITS" BY SORT_LIST SETTING CURSOR Else Call Message_Box(@window, " Executed to Line 72", "Show Error Message", MSG_ICON_EXCLAM$) err.id = 72 msg.id = 'C001' mssg = 'TERMINATED AT LINE 72' Gosub ERR: Goto 10 End Gosub LOAD.STATS: * Pass 2 * 10 ClearSelect Open ,"VISITS_HISTORY" To visits Else FSMSG('NO VISITS_History') Return End * Select visits SORT_LIST = "ID" SELECT "VISITS_HISTORY" BY SORT_LIST SETTING CURSOR Else Call Message_Box(@window, " Executed to Line 93", "Show Error Message", MSG_ICON_EXCLAM$) err.id = 93 msg.id = 'C001' mssg = 'TERMINATED AT LINE 93' Gosub ERR: Goto 20 End Gosub LOAD.STATS: Gosub CALC.MINUTES: Write stat On stats,stat.id dna<6> = stat.id dna<7> = sdate dna<8> = edate WRITE dna ON dnas,client disp1 = ' DONE' 20 If err.count = 0 Then disp2 = err.Count disp3 = 'Create Stats complete' Call Message_Box(@window, valMessage, "Show error message.", MSG_ICON_EXCLAM$) End Else disp2 = err.Count disp3 = 'ERRORS FOUND' Call Message_Box(@window, valMessage, "Show error message", MSG_ICON_EXCLAM$) End Unlock ALL Return 1 </QUOTE> —- === At 08 MAY 2020 12:35PM bob carten wrote: === <QUOTE> Hi Robert, Select by should work. Checking for errors might tell you why it is failing. The code example works for me using the latest 10.0.8 beta. Edit it for the approriate table and olumn <code> Subroutine test_select_by(void) #pragma format_indent_comments $Insert logical $INSERT FSERRORS_HDR $INSERT FSERRORS_0 $Insert FSERRORS_100 * ———————————- * Note: Edit table, column names for your system * ———————————- table = 'PERSON' sort_col = 'LNAME' cursor = "" ErrText = "" Use a flag to drop through to the exit when something is wrong isOK = true$ Open table To f_table Else Gosub onFsError end * Select If isOk then Select table BY sort_col setting cursor Else Gosub onFsError End end read_Cnt = 0 done = false$ Loop While isOk And Not(done) Readnext id using cursor by AT Then Read record From f_table, id Then * Do processing … * Have it set isOk = false$ if there is a problem read_Cnt += 1 End Else Gosub onFsError End /* read */ End Else * Readnext done If @FILE.ERROR<FSCODE$> == FS_READNEXT_DONE$ Then * Normal ending done = true$ End Else * had an error Gosub onFsError end End /* readnext */ repeat Return isOk onFsError: * Set the flag to drop through isOk = false$ * Display the error call FSMsg() * Clear the global error, we have handled it locally call Set_Status( 0 ) </code> </QUOTE> —- === At 08 MAY 2020 12:39PM rdhull50 wrote: === <QUOTE>Bob, the Select statements return zero records. It should return 10 records in the first pass, and zero in the second. If I put in a message trap after the Readnext, I get an unassigned error, so ID in the Readnext is not being assigned a value, which is consistent with no records returned by the select. If I comment out that Readnext message trap, then it processes through the statements and writes the stats record out, with zeros in fields 3 and 7, sets the value of DNA<13> to 0 and writes out the DNA record. The final Message indicates that there are no errors and that "Create Stats complete." The second Call Message_Box after the Readnext never fires, so it doesn't pass through the loop. So everything executes as it should, except no records are passed. If you'd like to PM me at robertdhull@gmail.com, I'll send the entire code, formatted. Thanks * Pass 1 Open ,"VISITS" To visits Else FSMSG('NO VISITS') Return End Equ target_ActiveList$ To 5 statement = "SELECT VISITS by ID WITH VISITDATE >= " :QUOTE(OSDATE):" AND WITH VISITDATE ⇐ " :QUOTE(OEDATE):" (S)" Call Message_Box(@window, "OStart Date = :":quote(osdate):": OEnd Date = :":quote(oedate):":", "Show error message.", MSG_ICON_EXCLAM$) * this returns 'OStart Date = :"01Apr20": Endate = :"30Apr20": …' call rlist(statement, target_ActiveList$ ,,,) Gosub LOAD.STATS: * Pass 2 * Open ,"VISITS_HISTORY" To visits Else FSMSG('NO VISITS_History') Return End statement = "SELECT VISITS_HISTORY by ID WITH VISITDATE >= " :QUOTE(OSDATE):" AND WITH VISITDATE ⇐ " :QUOTE(OEDATE):" (S)" Call Message_Box(@window, "OStart Date = :":quote(osdate):": OEnd Date = :":quote(oedate):":", "Show error message.", MSG_ICON_EXCLAM$) * this returns 'OStart Date = :"01Apr20": Endate = :"30Apr20": …' call rlist(statement, target_ActiveList$ ,,,) Gosub LOAD.STATS: Gosub CALC.MINUTES: Write stat On stats,stat.id dna<6> = stat.id dna<7> = sdate dna<8> = edate dna<13> = 0 WRITE dna ON dnas,client disp1 = ' DONE' 20 If err.count = 0 Then disp2 = err.Count disp3 = 'Create Stats complete' Call Message_Box(@window, valMessage, "Show error message.", MSG_ICON_EXCLAM$) End Else disp2 = err.Count disp3 = 'ERRORS FOUND' Call Message_Box(@window, valMessage, "Show error message", MSG_ICON_EXCLAM$) End Unlock ALL Return 1 * LOAD STATS SUBROUTINE LOAD.STATS: done = 0 pass = 0 Loop Readnext ID else done = 1 Call Message_Box(@window, " ID = :":ID:": Pass = :":pass:": Terminated after Readnext.", "Show Error Message", MSG_ICON_EXCLAM$) end Until done pass += 1 Call Message_Box(@window, " ID = :":ID:": Pass = :":pass:": Terminated after Readnext.", "Show Error Message", MSG_ICON_EXCLAM$) </QUOTE> —- === At 08 MAY 2020 12:41PM rdhull50 wrote: === <QUOTE>That last message of mine was before your last one. </QUOTE> —- === At 11 MAY 2020 08:25AM Brad Bishop wrote: === <QUOTE>TRy taking the (S) off of the R\List. </QUOTE> —- === At 18 MAY 2020 12:38PM rdhull50 wrote: === <QUOTE>Thanks Brad, that did it. I actually thought I had removed that quite some time ago, but I guess during the various iterations, I enabled a line with it still in. Also, I tried running the stored proc using a reduce_script in the "Reduce" statement. It runs, but the selection criteria in the reduce script does not work in that all records are returned to the select. Here is a partial listing of the proc. * Pass 1 pass = 1 table_name = "VISITS" flag = "" done = False$ CursorVar = "" * Clears all cursors For Counter = 0 To 8 ClearSelect counter Next Counter sort_list = "VISITDATE" * Reduce_Script = "WITH {VISITDATE} >= ":SDATE:" AND WITH {VISITDATE} ⇐ ":EDATE * Reduce_Script = "WITH {VISITDATE} >= " :QUOTE(OSDATE):" AND WITH {VISITDATE} ⇐ " :QUOTE(OEDATE) * Reduce_Script = "WITH ":Oconv({VISITDATE},"DE"):" >= ":QUOTE(OSDATE):" AND WITH ":Oconv({VISITDATE},"DE"):" ⇐ ":QUOTE(OEDATE) Reduce_Script = "WITH {VISITDATE} >= '01Apr20' AND WITH {VISITDATE} ⇐ '30Apr20'" mode = Next_Cur$ Reduce(Reduce_Script, sort_list, mode, table_name, CursorVar, flag) isOK = True$ ctr = 0 If flag Then Select table_name BY sort_list using CursorVar Then pass = 1 Gosub Load.Stats: End Else Gosub onFsError End End * more processing …………………. * LOAD STATS SUBROUTINE LOAD.STATS: Open '', table_name To visits Else FSMSG('NO':table_name) Return End ctr = 0 Loop Readnext ID using CursorVar BY AT Else done = True$ Until done LOCK_IT(visits,id) Read visit From visits,id Then If pass = 1 Then vcount += 1 Else vhcount += 1 user.id = visit<1> vdate = visit<2> * sub-routine continues …………….. Complete copy of Stored Proc. </QUOTE> —- === At 18 MAY 2020 12:50PM rdhull50 wrote: === <QUOTE>Hopefully this link will have the formatting in tact. Link to complete Stored Proc </QUOTE> View this thread on the Works forum...