[sqr-users] Dynamic SQL in SQR

["Myers, Roda" <RMYERS@co.napa.ca.us>]

SQR for PeopleSoft/8.48.09/PC/Windows NT/ODBC/Mar 22 2007

MS-SQL Server 2003

PeopleSoft 8.9

I am trying to update a custom table using dynamic SQL. Below is the
text from my SQC that is called by another program. I am passing the
$prcsname from my calling program. It's the same as the column name.

!***********************************************************************
*******
! NAPA-PAY-CALENDAR-CHECK
*
! Procedure checks custom calendar to insure process has not been run
for     *
! the pay run id that was entered
*
!***********************************************************************
*******
begin-procedure NAPA-CALENDAR-CHECK($prcsname,$SLCTRUNID)
display 'begin NAPA-CALENDAR-CHECK'
 SHOW 'Run ID ... '  $SLCTRUNID

begin-SELECT 
CAL.RUN_ID &CAL.RUN_ID
[$prcsname] &process=char


FROM  PS_NAPA_PAY_CALNDR CAL
WHERE CAL.RUN_ID = $SLCTRUNID

    
end-SELECT

display $SLCTRUNID

display &process

!  if $prcsname = 'PAYACRL' and &CAL.PAYACRL = 'Y'
if &process = 'Y'

    let $Msg = 'ERROR-Process has already been run for this pay run id
'||
               &CAL.RUN_ID
    display $Msg
    stop
       
     else
        do NAPA-CALENDAR-UPDATE
              
  end-if

end-procedure


!***********************************************************************
**********
!Update Napa Pay Calendar
!***********************************************************************
**********

begin-procedure NAPA-CALENDAR-UPDATE


BEGIN-SQL ON-ERROR=SQL-ERROR
UPDATE PS_NAPA_PAY_CALNDR CAL1
SET [$prcsname] = 'Y'
WHERE CAL1.RUN_ID = $SLCTRUNID
END-SQL

end-procedure

It stops correctly if the process has been run but it's failing on the
update with the following error: 
+SQL Status =   170, SQL Error  = [Microsoft][ODBC SQL Server
Driver][SQL Server]Line 1: Incorrect syntax near 'CAL1'.



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users