sqrvariables - aaron whincop · $sqr-hostname name of the machine the sqr is running on ......

iI_ •••etNe language used for programming and reporting - written using text editors. With theIlII!Ilease lDfApplcation Engine 8, PeopleSoft now recommends that SOR only be used for reporting. AnyI..,ogrammir· tQ should be done using the Application Engine tool

SQR VariablesAll variables scope are global to the program$varname - string / date&record.field - database record and field

• names end with '.sqr' while common functions are placed in a file ending with '.Sqc'...- -L-!!:#v!.!a::.:.r!.!:na::.:m.!.!e:::.-_-..:.n:.:::u::..:m.:::e:!.:ri~C...l:(i::..:n~t,..:.:f1~oa:::;t:!..,.::.de:::;c:::im~a:.LI)_--Iare preceeded by an exclamation point - !this is a comment

File FunctionsDelete Deletes the file, Returns a 0 to indicate success

let #filestatus = delete($fiIename)Exists Returns a 0 if the file exists

let #filestatus = eXists($filename)Rename Renames $filename1 to $filename2, Returns a 0 to

indicate successlet #filestatus = rename($filename1, $filename2)Closes a fileclose 5Opens a file for reading or writingopen $file as #filenum for-writing record=11 0open $file as #filenum for-append record=100:fixedSpecial Commandsfor-reading allows reading of filefor-writing allows writing of new filefor-append allows writing of existing filerecord Specify record sizeFixed Sets fixed lengthFixed_nolf Setsfixed no line feedsReads the next record of a fileread #file into $string1:10 $string2:20 #number:15W~ites a record to filewrite #file from $string1:10 $string2:20 $string3:15

Flow ControlIF condition

...code ...

...SOL. ..ELSE

...code ...

...SOL...END-IF

daIa on the page$sIri1g (fine,#column)tnunmer (IIine,#column) edit 9.999

QJnna1dsin Bold

Places Box around dataCenter margin

NJICfwsto print non-display charactersdaae using defined mask

using defined maskOJatacIerDescriptionX Use character in field.S Insert blank.

Skip character in fieldDigit, zero fill to the right of the decimal

point, trim leading blanks (left justify thenumber).Digit, zero fill to the right of the decimal

point, space fill to the left.Digit, zero fill to the left.Dollar sign, optionally floats to the rightTreated as a "9", but if a value is zero,

the field is converted to blanks . .....----L--------...L..-------------------------t4 digit yearDay of month (1 - 31).Day of week (1-7)Name of day.Abbreviated name of month.Month (01-12; JAN=01).24 hour clockMinute (0-59)

SSSSS SecondsTme indicator.

specified characterMoney edi maskAi number edit mask

10-.11_11I: P'IW bmating

Close

EVALUATE variableWHEN = value

...code ...

...SOL...break

WHEN-other...code ......SOL...

END-EVALUATE

Open

8

WHILE condition...code ......SOL...

END-WHILE

Read

Write

Operators ExplanationII Concatenate strings or dates1\ Exponent two numbers*, /, % Multiply, Divide or Remainder+, - Plus or Minus

Comparison Operators> greater than< less than>= greater or equal to<= less than or equal to<> or!= not equal

equalLogical NOTLogical ANDLogical OR, Exclusive Or

SQR System Variables#current-column current column on page$current-date current date time on local machine#current-line physical line number on page#end-file 0 = false, 1 = true#page-count current page number#return-status value returned to OS when SOR exits#sql-count count of rows DML effected$sql-error text message of last sql error#sql-status status of sql database after each query$sqr-hostname name of the machine the sqr is running on#sqr-max-columns maximum columns of current report#sqr-max-Iines maximum lines with current report layout#sqr-pid process id for current sqr process$sqr-program name of sqr program file$sqr-report name of report output file#sqr-toc-Ievel current TOC entry level#sqr-toc-page current TOC page$sqr-toc-text text of current TOC entry

notandor, xor

Prints alwaysf'rR::aIange Prints only when changesPriRI=OIangefTQJrPage Prints on change and

top of pageHow many lines to skip

PRM:eduIe= Procedure called when breakPIocdure caIed after break

BIfDIe= Procedure caIed before breakLBIEL DeBmines level of breakPIaI:es shaded boax around data

Ila.!•••• ePlillls lMdIri1e••••• iell ~ area defined

Sqr Data TypesDecimalFloatIntegerDateText/CharList

- I Yariables PeopleSoft Special Built Functionsdo getsetid(Table Name, Tree Name, $SetidReturned)

Requires $SetCntrlValue to be set with BUdo format-datetime($Clatein, $dateout, FORMAT, TIME, DIRECTION)

Format: DEFCMP, DEFMDY, DEFYMD, DEFDATEHandy date format routine for converting and calculating

do diff-date($date1, $date2, :#years, :#months, :#days)Number of years or months or days between two datessee diff-dateelapsed for total time

do dtu-add-days($dtudate, #days, :$dtudatereturn)do dtu-subtract-days($dtudate, #days, :$dtudatereturn)do dtu-add-months($dtudate, #months, :$dtudatereturn)do dlu-add-years($dtudate, #years, :$dtudatereturn)do dIu-monBHInd($cI:udaIe, :$dtudatereturn)do CIIIIIWI1i-InnI Conmit in database formatCIIH!IIUFSQL<nur at SQL Error rouIiIle for Begin-SQL

Operator 10Run Control 10Program Status outputProgram Status outputDale in native formatTme in native formatDaIetirne in native formatcae in report fonnatTme in report fonnatText oulPUl when SOL error•••• _- of Process InstanceChararclenasion of Pt

~---- -- -

••••• ic ••• e..IlcaI ••••~ ••• Ih) box Iboxwidth #rulew...:id::t::.:.h..::#.:sh:..:.:a:.:d:..:.:in..:.::g~ -I"-.lIaJ_",,_III1) horz-line #rulewidth d t U

~

c;iiilEi;;~~~~~~~~~)vert~~-line~#ru~Iewidth~'~l Standard Inclu es 0 se#include 'reset.sqc'#include 'sfgetshr.sqc'

I~E~~~~::::=:~::':J3I]I5.~;aulain' Update' #include 'readxlat.sqc'#include 'fsbcal.sqc'#include 'curdttim.sqc'#include 'number.sqc'#include 'datetime.sqc'#include 'prcsdef.sqc'#include 'prcsapi.sqc'

'nclude 'datemath.sqc'. dude 'tranctrt.sqc'

mclu<ie'timemath.sqc'

jAlJS:IRatunlS the absolute value - abs(#number)Returns the arccosine in the range of 0 to p radianacos(lnumber)

lAIR-Pl:DI!r:. Alters printer parameters at run timePom-5ize = Font point sizeFont-Type = PROPORTIONAL or FIXEDSymbol-Set = identifierFont = number(3 = Courier, 4 = Helvetica.)Pik:h = Characters per inch

:-Report: Alters Report infoHeading - Name of the BEGIN-HEADING section to useHeading-5ize - Amount of space occupies in the pageFooting - Name of the B~GIN-FOOTING section to useFooting-Size - Amount of space occupies in the pageReturns the ASCII value for the first character - ascii($string)Returns the numeric value for the first character- asciic($string)Returns the arcsine - asin(#number)Returns the arctangent - atan(#number)Returns the smallest integer that is greater than or equal to

umber)Returns character with the ASCII value of num_value

umber)Issues a database commit, not recommended to use- see PeopleSoft special Built Functions

lnum2 if#num1 is nonzero (0) otherwise returns #num3oond( umber1, #number2, #number3)

the cosine - cos(#number)the hyperbolic cosine of number - cosl:l(#numeer)a date after adding the specified units

'year", 'quarter', 'week', 'month', 'day', 'hour', 'minute', 'second'daleatld($date, 'day', #number)

the difference between the specified dates)ear'. 'quarter', 'week', 'month', 'day', 'hour', 'minute', 'second'___ ••,$date 1, $date2, 'day')

10•••• .-.:IRaUlI5 the current local date and time from the server - datenowO1DJ__ :CcllMertsto a string - datetostr($date, 'MM-DD-YYYY')

expressed in degrees - deg(#number)SQR log - display $string

user procedure - do procedure-nameAID~.,••••"", value of 10 raised - e10(#number)

edi definition - edit($phone, '(xxx) xxx-xxxxx')ASI_15 a non-display to a string

<56J45I89' to $string 1a Begin-Select - exit-select

•••••••_-- value of e raised - exp(#number)01 a string into a string

. 1 from $string2 #nstart #nlengtha c:haracIer sequence within a string. 1~ fioundlocation

Aa ••• illelalgest inIeger that is less than or equal to

•••••••• ~ SlB:i1ed envirorment variable - getenv('DBASE')

Unstring: Breaks a string into smaller stringsunstring $string4 by',' $string1 $string2 $string3

Upper: Converts to uppercase - upper($string)Wrapdepth: Returns the number of print lines requir~d .

wrapdepth($string,#number1,#number2,$stnng2,$stnng3)

Instr: Returns the position of string beginning at #numberinstr($string, 'STARR', 5) . .

Isblank: Returns 1 if $string is empty otherwise 0 - isblank(&descnptlon)Isnull: Returns 1 if $string is null otherwise 0 - isnulI($string)Last-Page: Prints last page number - last-page 0 ' of' '.'Length: Returns the number of characters - length($string)Let: Assigns the value of an expression - let $string = 'STARR'Log: Returns the natural logarithm - log(#number)Log10: Returns the base-10 logarithm - log10(#number)Lower: Converts to lowercase - lower($string)Lpad:' Pads to left #number of times - Ip~d($strnig, #nu~ber, '-')Ltrim: Trims characters from the left - Itnm($stnng1, $stnng2)Mod: Returns the fractional remainder - mod(#number1, #number2)Move: Assigns the value of an expression - move 'STARR' to $stringNew-Page: Issues a New Page to Printer - new-page .Nvl: Returns $string2 if $string1 is null otherwise $stnng1

nvl($string1, $string2)Page-Number: Prints current page number - pa.ge-nu~ber(1 ,37) 'Page'Position: Sets the position on the page - position (#hne,#column)Power: Returns the value of #number1 raised to the power of #number2

power(#number1, #number2)Rad: Returns a value expressed in radians - rad(#number)Range: Returns 1 if #number1 is between #number2 and #number3

otherwise 0range(#number1, #number2, #number3)

Replace: Replaces all occurrences of $string2 with $string3replace($string1, $string2, $string3)

Round: Returns a value rounded to #number2 - round(#number1,#number2)Rpad: Pads to right #number of times - rpad($string, #nu~ber, '-')Rtrim: Trims characters from the right - rtrim($string1, $strmg2)Show: Writes information to SQR log - show $stringSign: Returns a -1, 0, or +1 depending on the sign - sign(#number)Sin: Returns the sine - sin(#number)Sqrt: Returns the square root - sqrt(#number)Stop: Halts SQR - non abort - StopString: Concatenates a list of variables

string $string1 $string2 $string3 by',' into $string4Strtodate: Converts to date - strtodate($date, 'MM-DD-YYYY')Substr: Substrings start at #number 1 out #number 2

substr($string, #number1 , #number2)Tan: Returns the tangent - tan(#fvar)To char: Converts to a string - to_char(#number)To-number: Converts to a number - to_number($string)Translate: Converts characters from $string2 to $string3

translate($string1, $string2, $string3)Trunc: Value truncated to #number2 - trunc(#number1, #number2)

Standard Start Includes to Use#include 'setenv.sqc'#include'setup32.sqc' or'setup31.sqc'

!! 32 is for portrait - 31 is landscape !!#include 'sqrtrans.sqc'

Standard Main Routine to Usebegin-REPORT

do Init-DateTime !setup datetime variablesdo Init-Number !setup number variablesdo Get-Current-DateTime !setup current datesdo Init-Report !get run control infodo !this is your procedure call to the main programdo Reset !prints end of reportdo Commit-Transaction !database independent commitlet #prcs_run_status = #prcs_run_status_successful,

let $prcs_message_parm1 = 'Successful Completiondo Update-Prcs-Run-Status !program done SUCCESS!

end-REPORT

:::::=:::=~::~::~'at pre-print logic section ''Slarr Software Inc'= 'Fteds Garage'

Checkout www.stansoftware.comonIiIe you find a basic core start program