[sqr-users] Simple text file

["Nanci Lawson" <lawson@soka.edu>]

Hello,

I am a novice to sqr and have written a fairly simple one to extract a
text file.  The problem I am having is that there is a space between my
heading and the first line of data and I really don't have a good way of
knowing how many lines of data I will end up with.  Thus the use of
max-lines.  I would appreciate any help with refinement.
Thanks,
Nanci 


!***********************************************************************
!  SALLIE_MAE - produces file for payment plan
!***********************************************************************
!***********************************************************************
!***********************************************************************
!                                                                      *
!       $Release:                                                      *
!      $Revision:  2                                                   *
!    $Version-ID:                                                      *
! NOTES:  need to change the record count down in the trailer each     *
!         time and to make sure the max_lines are set to include all   *
!         in the first pass                                            *
!                                                                      *
!***********************************************************************

#Include 'SETENV1.SQC'
!#Include 'curdttim.sqc'  !Get-Current-DateTime procedure
#Include 'datetime.sqc'  !Routines for date and time formatting




Begin-Setup

  
Declare-Layout Default
 
  Line-width=320
  Left-margin=0
  Top-margin=0
  Formfeed=NO
  Max-lines=401
 
End-Declare


End-Setup


begin-report
 ! do Get-Current-DateTime  
  do Process-Main


begin-SELECT
{DateTimeOut-Prefix}GETDATE(){DateTimeOut-Suffix} &_SysDateTime
FROM PSCLOCK
end-SELECT
            let $tempDate = datetostr(&_SysDateTime,'YYYYMMDDHHMISS')

  
end-report


#define C1 1
#define C2 96
#define C3 61
#define C4 313
#define C5 45


begin-heading 1
Let $space = '*'
Let $header =
'HR002101500000899984002101500'||$tempDate||RPAD('PIL',276,' ')||$space
 
print $header     (0,1)
    
   
end-heading 

!***********************************************************************
!  PROC PROCESS-MAIN
!         Main Procedure
!***********************************************************************

BEGIN-PROCEDURE Process-Main
BEGIN-SELECT 
'01'                (+1,{C1})
B.FIRST_NAME_SRCH   (0,{C2})   
B.LAST_NAME_SRCH    (0,{C3})  
B.BIRTHDATE         (0,{C4}) edit YYYYMMDD
   
B.EMPLID            (0,{C5})
FROM PS_ACAD_PROG A, PS_PERS_DATA_SA_VW B
WHERE  A.EFFDT = 
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED 
WHERE A.EMPLID = A_ED.EMPLID 
AND A.ACAD_CAREER = A_ED.ACAD_CAREER 
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR 
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10)) 
AND A.EFFSEQ = 
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES 
WHERE A.EMPLID = A_ES.EMPLID 
AND A.ACAD_CAREER = A_ES.ACAD_CAREER 
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR 
AND A.EFFDT = A_ES.EFFDT) 
AND A.EMPLID = B.EMPLID 
AND A.PROG_STATUS = 'AC'

END-SELECT
END-PROCEDURE

begin-footing 1
   Let $space = '*'  
   Let $footer = RPAD('TR003790000020030424185700',319,' ')||$space

 
print $footer     (0,1)
    
end-footing 





Nanci Lawson
ANGEL Administrator
PeopleSoft Programmer/Analyst
Soka University 
949-480-4222


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