Re: Repetitive updates on an SQL routine inside a SQR - revisited

["Arosemena, Efrain" <Earosem@TOYOTARP.COM>]

If you try with rtrim(ltrim(field)) --> reference to idnumber just if the
field is char or assign the pseudonim to a $char_var to
convert(int,$char_var).   The COMMIT instruction necessary use a BEGIN
TRANSACTION ?

-----Mensaje original-----
De: Chuck.L.King@SEALEDAIR.COM [mailto:Chuck.L.King@SEALEDAIR.COM]
Enviado el: Wednesday, March 08, 2000 2:47 PM
Para: SQR-USERS@list.iex.net
Asunto: Repetitive updates on an SQL routine inside a SQR - revisited


Last week I posted that only one record was being update by my
"UPDATE_PRINTED_FIELD" routine and several people noticed I was not calling
this
routine from within the main select procedure.  I corrected this and upon
re-testing the SQR found that instead of taking 2-3 seconds as before, it
continues to run until I cancel the report.  The first record from many that
are
queued up is updated with a "Y" however none of the other records in the
queue
are being updated.  I have pasted the code below.  Any ideas why this
program
continues to run (until you cancel it) and is only updating one record from
the
queue?

Thanks,

Chuck L. King

! WCM001.SQR
!--------------------------------------------------------------------------!
! Description:  This routine prints an appreciation form letter for the WCM
!               suggestions from employees. It will populate the (hidden)
!               XTRA29 field with a 'Y' indicating that a Merit letter has
!               already been printed. These letters will be distributed by
!               the Department Supervisors. This report can be printed on
!               dot-matrix, inkjet, or laser printers.
!--------------------------------------------------------------------------!
! Revision   By    Date      Description
!--------------------------------------------------------------------------!
!    0      King  08/26/99   Original report created from specifications
!                            outlined by the WCM committe.
!    1      King  09/08/99   Rewrote code in SQR3 format after committe
!                                   decided they preferred that format.
!--------------------------------------------------------------------------!

BEGIN-SETUP
   DECLARE-PRINTER default-hp
      POINT-SIZE = 12
      FONT-TYPE  = fixed
      SYMBOL-SET = 10U
      PITCH      = 10
      FONT       = 3
   END-DECLARE

   DECLARE-LAYOUT KING
      PAPER-SIZE  = (8.5,11)          ! Calling this anything but DEFAULT
      FORMFEED    = yes               ! doesn't work
      ORIENTATION = portrait
      LEFT-MARGIN = 0.5
      TOP-MARGIN  = 0.25
      MAX-COLUMNS = 80
      MAX-LINES   = 60
      LINE-HEIGHT = 12
   END-DECLARE

     DECLARE-REPORT WORKORDER
          LAYOUT=KING
          PRINTER-TYPE=HP
     END-DECLARE

END-SETUP

Begin-Program
 USE-PRINTER-TYPE hp
 INPUT $WHERE
 Do GET_DATE
 Do Main
End-Program

BEGIN-PROCEDURE GET_DATE
BEGIN-SELECT
@DATETOCHAR(SYSDATE,'MM/DD/YYYY')        &REFDATE
FROM DUMMY_TABLE
END-SELECT
END-PROCEDURE

Begin-Procedure Letter
 Print-Image (2,1)
     type=bmp-file
     source='M:\MAXIMO\CRLOGOBW.BMP'
     image-size=(11,2)

   ALTER-PRINTER                    ! Set text size back to default
      POINT-SIZE = 12

   PRINT 'C R Y O V A C'               (2,) CENTER BOLD
   PRINT 'W O R L D  C L A S S  M A N U F A C T U R I N G' (4,) CENTER BOLD
   GRAPHIC                             (5,1,75) HORZ-LINE 10

   PRINT 'IPT'                       (2,70)
   DATE-TIME                           (3,65)  MON-DD-YYYY
   DATE-TIME                           (4,67) 'HH:MI PM'

 print 'WCM Appreciation Letter' (+3,) CENTER BOLD
 print &REFDATE (+2,) CENTER BOLD
 print 'Thank you for your WCM Suggestion. Your suggestion has been' (+2,1)
BOLD
 print 'approved for further consideration by the department review' (+1,1)
BOLD
 print 'committee.' (+1,1) BOLD
 print 'Record Number: ' (+2,10) BOLD
 print &idnumber (,) BOLD
 print 'Suggestion Type: ' (+2,8) BOLD
 print &sugtype (,) BOLD
 print 'Open Date: ' (+2,14) BOLD
 print &opendate (,) BOLD
 print '  Approving Supervisor: ' (+2,1) BOLD
 print $superfnam (,) BOLD
 print 'Employees contributing: 1)' (+2,1) BOLD
 print $EMPFNAM (,27) BOLD
 print '2)' (+1,25) BOLD
 print $XTRA2 (,27) BOLD
 print '3)' (+1,25) BOLD
 print $XTRA3 (,27) BOLD
 print '4)' (+1,25) BOLD
 print $XTRA4 (,27) BOLD
 print '5)' (+1,25) BOLD
 print $XTRA5 (,27) BOLD
 print '6)' (+1,25) BOLD
 print $XTRA6 (,27) BOLD
 print 'Concern/Suggestion:' (+2,1) BOLD UNDERLINE
 do list_concern
 DO long_descrip                              ! Fetch long descriptions

   IF $long_problem != ''
      PRINT 'Long Description:' (+2,1) BOLD UNDERLINE
      PRINT $long_problem (+1,1) WRAP 70 25
   END-IF

 DO PRINT_FEEDBACK
End-Procedure

Begin-Procedure Main
Begin-Select
IDNUMBER             &idnumber
EMPLNAM
SUGTYPE
@DATETOCHAR(OPENDATE,'MM/DD/YYYY')       &OPENDATE
CONCERN
EMPFNAM
CONTACTNAM
SUPERFNAM
SUPERLNAM
XTRA2
XTRA3
XTRA4
XTRA5
XTRA6
XTRA7
XTRA8
XTRA9
XTRA10
XTRA11
XTRA22
XTRA29
ldkey     &wcmldkey

 MOVE &EMPFNAM TO $EMPFNAM
 MOVE &EMPLNAM TO $EMPLNAM
 MOVE ' ' TO $SEPARATOR1
 CONCAT $SEPARATOR1 WITH $EMPFNAM
 CONCAT $EMPLNAM WITH $EMPFNAM

 MOVE &XTRA2 TO $XTRA2
 MOVE &XTRA7 TO $XTRA7
 MOVE ' ' TO $SEPARATOR2
 CONCAT $SEPARATOR2 WITH $XTRA2
 CONCAT $XTRA7 WITH $XTRA2

 MOVE &XTRA3 TO $XTRA3
 MOVE &XTRA8 TO $XTRA8
 MOVE ' ' TO $SEPARATOR3
 CONCAT $SEPARATOR3 WITH $XTRA3
 CONCAT $XTRA8 WITH $XTRA3

 MOVE &XTRA4 TO $XTRA4
 MOVE &XTRA9 TO $XTRA9
 MOVE ' ' TO $SEPARATOR4
 CONCAT $SEPARATOR4 WITH $XTRA4
 CONCAT $XTRA9 WITH $XTRA4

 MOVE &XTRA5 TO $XTRA5
 MOVE &XTRA10 TO $XTRA10
 MOVE ' ' TO $SEPARATOR5
 CONCAT $SEPARATOR5 WITH $XTRA5
 CONCAT $XTRA10 WITH $XTRA5

 MOVE &XTRA6 TO $XTRA6
 MOVE &XTRA11 TO $XTRA11
 MOVE ' ' TO $SEPARATOR6
 CONCAT $SEPARATOR6 WITH $XTRA6
 CONCAT $XTRA11 WITH $XTRA6

 MOVE &SUPERFNAM TO $SUPERFNAM
 MOVE &SUPERLNAM TO $SUPERLNAM
 MOVE ' ' TO $SEPARATOR7
 CONCAT $SEPARATOR7 WITH $SUPERFNAM
 CONCAT $SUPERLNAM WITH $SUPERFNAM

  Do Letter
  Do UPDATE_PRINTED_FIELD

 new-page

>From  MAXIMO.WCM
Where [$WHERE]
Order By MAXIMO.WCM.OPENDATE
End-Select
End-Procedure

begin-procedure List_Concern
begin-select
id.idnumber
id.concern    &txtconcern
id.concerntxt &concerntxt
 print &txtconcern (+1,1) wrap 70 10
 print &concerntxt (+2,1) wrap 70 10
     from wcm id
     where id.idnumber = &idnumber
end-select
end-procedure


BEGIN-PROCEDURE long_descrip
   MOVE '' TO $long_problem                            ! Initialize long
string
to null
BEGIN-SELECT
l.ldtext   &ldtext                                                         !
Read in a line
   MOVE &ldtext TO $ldtext2                                ! Convert to text
string
   CONCAT $ldtext2 WITH $long_problem    ! Add to end of string we're
building
   CONCAT ' ' WITH $long_problem                ! Add a space before adding
next
 section to
   FROM longdescription l                                    ! keep from
butting
 two words together
   WHERE l.ldkey = &wcmldkey
     and l.ldownercol = 'CONCERNTXT'
   order by ldkey, ldlinenum
END-SELECT
END-PROCEDURE

BEGIN-PROCEDURE PRINT_FEEDBACK
 print 'We appreciate your effort in submitting this suggestion.' (+3,1)
BOLD
 print 'Your input is invaluable in making the WCM program work.' (+1,1)
BOLD
 print 'Thanks again,' (+3,10) BOLD
END-PROCEDURE

BEGIN-PROCEDURE UPDATE_PRINTED_FIELD
BEGIN-SQL
UPDATE WCM SET WCM.XTRA29 = 'Y' WHERE WCM.IDNUMBER = &IDNUMBER;
END-SQL
COMMIT
END-PROCEDURE


BEGIN-FOOTING 2
   ALTER-PRINTER                    ! Set text size back to default
      POINT-SIZE = 12
      FONT-TYPE  = fixed
      SYMBOL-SET = 10U
      PITCH      = 10
      FONT       = 3
   GRAPHIC                                   (1,1,75) HORZ-LINE 10
   UPPERCASE $sqr-program
   PRINT $sqr-program             (2,1)
   PRINT &idnumber            (,40)
 UPPERCASE $username
   PRINT $username                  (,65)
END-FOOTING