SQR Command Reference

This chapter describes and demonstrates each command in the SQR lexicon.

SQR Command Overview

The commands in this section follow the conventions listed in the section SQR Syntax Conventions in the previous chapter and use the abbreviations described in the following table:

Warning! If you are copying code directly from the examples in the PDF file, make sure that you change the slanted quotes to regular quotes or you will receive an error message.

SQR Commands

The following sections discuss the SQR commands in alphabetical order.

ADD

Syntax

ADD{src_num_lit | _var | 
_col} TO dst_num_var 
[ROUND=nn]

Description

Adds one number to another.

The source value is added to the destination variable and the result is placed in the destination. The source is always first and the destination is always second.

When dealing with money-related values, use decimal variables rather than float variables . Float variables are stored as double precision floating point numbers , and small inaccuracies can occur when a program is adding many numbers in succession. These inaccuracies can appear due to the way floating point numbers are represented by different hardware and software implementations and also due to inaccuracies that can be introduced when a program is converting between floating point and decimal.

Parameters

Example

To add 10 to #counter:

add  #counter  to  #new_count
add  &price  to  #total  round=2

See Also

The LET command for information about complex arithmetic expressions.

ALTER-COLOR-MAP

Syntax

ALTER-COLOR-MAP
      NAME = {color_name_lit | _var | _col}
VALUE = ({color_name_lit | _var | _col} | {rgb})

Description

Dynamically alters a defined color.

The ALTER-COLOR-MAP command is allowed wherever the PRINT command is allowed. This command enables you to dynamically alter a defined color. You cannot use this command to define a new color.

Parameters

The default colors implicitly installed with SQR include:

• black=(0,0,0)

• white=(255,255,255)

• gray=(128,128,128)

• silver=(192,192,192)

• red=(255,0,0)

• green=(0,255,0)

• blue=(0,0,255)

• yellow=(255,255,0)

• purple=(128,0,128)

• olive=(128,128,0)

• navy=(0,0,128)

• aqua=(0,255,255)

• lime=(0,128,0)

• maroon=(128,0,0)

• teal=(0,128,128)

• fuchsia=(255,0,255)

Example

The following example illustrates the ALTER-COLOR-MAP command:

begin-setup
   declare-color-map
   light_blue = (193, 222, 229)
   end-declare
end-setup

begin-program
   alter-color-map name = 'light_blue' value = (193, 233, 230)

   print 'Yellow Submarine' ()
      foreground = ('yellow')
      background = ('light_blue')

   get-color print-text-foreground = ($print-foreground)  
   set-color print-text-foreground = ('purple')
   print 'Barney' (+1,1)
   set-color print-text-foreground = ($print-foreground)
end-program

See Also

DECLARE-COLOR-MAP, SET-COLOR, GET-COLOR

ALTER-LOCALE

Syntax

ALTER-LOCALE
[LOCALE={txt_lit _var|DEFAULT|SYSTEM}]
[NUMBER-EDIT-MASK={txt_lit|_var|DEFAULT|SYSTEM}]
[MONEY-EDIT-MASK={txt_lit|_var|DEFAULT|SYSTEM}]
[DATE-EDIT-MASK={txt_lit|_var|DEFAULT|SYSTEM}]
[INPUT-DATE-EDIT-MASK={txt_lit|_var|DEFAULT|SYSTEM}]
[MONEY-SIGN={txt_lit|_var|DEFAULT|SYSTEM}]
[MONEY-SIGN-LOCATION={txt_var|DEFAULT|SYSTEM|LEFT
|RIGHT}]
[THOUSAND-SEPARATOR={txt_lit|_var|DEFAULT|SYSTEM}]
[DECIMAL-SEPARATOR={txt_lit|_var|DEFAULT|SYSTEM}]
[DATE-SEPARATOR={txt_lit|_var|DEFAULT|SYSTEM}]
[TIME-SEPARATOR={txt_lit|_var|DEFAULT|SYSTEM}]
[EDIT-OPTION-NA={txt_lit|_var|DEFAULT|SYSTEM}]
[EDIT-OPTION-AM={txt_lit|_var|DEFAULT|SYSTEM}]
[EDIT-OPTION-PM={txt_lit|_var|DEFAULT|SYSTEM}]
[EDIT-OPTION-BC={txt_lit|_var|DEFAULT|SYSTEM}]
[EDIT-OPTION-AD={txt_lit|_var|DEFAULT|SYSTEM}]
[DAY-OF-WEEK-CASE={txt_var|DEFAULT|SYSTEM|UPPER|LOWER
|EDIT|NO-CHANGE}]
[DAY-OF-WEEK-FULL=({txt_lit1|_var1}...{txt_lit7
|_var7})]
[DAY-OF-WEEK-SHORT=({txt_lit1|_var1}...{txt_lit7
|_var7})]
[MONTHS-CASE={txt_var|DEFAULT|SYSTEM|UPPER|LOWER|EDIT
|NO-CHANGE}]
[MONTHS-FULL=({txt_lit1|_var1}...{txt_lit12|
_var12})]
[MONTHS-SHORT=({txt_lit1|_var1}...{txt_lit12|_var12})]

Description

Selects a locale or changes locale parameters used for printing date, numeric, and money data and for data accepted by the INPUT command. A locale is a set of preferences for language, currency, and presentation of charts and numbers.

The SYSTEM locale represents the behavior of older versions of SQR prior to Version 4.0. When you install SQR for PeopleSoft Version 4.0 or later, the default locale is set to SYSTEM. This provides upwards compatibility for older SQR programs. This table describes the SYSTEM locale settings:

Parameters

The following table lists and describes the parameters:

Note. Many of the settings can have a value of DEFAULT or SYSTEM . For a given setting, specifying DEFAULT retrieves the value from the corresponding setting of the default locale as identified in the Default-Settings section of the sqr.ini file. Similarly, specifying SYSTEM retrieves the value from the corresponding setting of the system locale. You can alter the system locale using the ALTER-LOCALE command; however, you cannot define it in the sqr.ini file.

Example

The following example illustrates the ALTER-LOCALE command:

!
! The following program segments will illustrate the various
! ALTER-LOCALE features.
!
begin-setup
 declare-variable
  date $date $date1 $date2 $date3
 end-declare
end-setup

 !
 ! Set default masks
 !
 alter-locale
  number-edit-mask = '9,999,999.99'
  money-edit-mask  = '$999,999,999.99'
  date-edit-mask   = 'Mon DD, YYYY'
 
 let #value = 123456
 let $edit = 'Mon DD YYYY HH:MI:SS'
 let $date = strtodate('Jan 01 2004 11:22:33', $edit)
 show 'With NUMBER option    #Value = ' #value number
 show 'With MONEY  option    #Value = ' #value money
 show 'Without NUMBER option #Value = ' #value
 show 'With DATE option      $Date  = ' $date date
 show 'Without DATE option   $Date  = ' $date 

Produces the following output:

With NUMBER option    #Value =   123,456.00
With MONEY  option    #Value = $    123,456.00
Without NUMBER option #Value = 123456.000000
With DATE option      $Date  = Jan 01, 2004
Without DATE option   $Date  = 01-JAN-04

 !
 ! Reset locale to SQR defaults and assign a multi-character
 ! money-sign.
 !
 alter-Locale
  locale = 'System'
  money-sign = 'AU$'                ! Australian dollars
 
 let #value = 123456
 show #value edit '$999,999,999,999.99'
 show #value edit '$$$$,$$$$999,999.99'

Produces the following output:

AU$      123,456.00
      AU$123,456.00

 !
 ! Move the money-sign to the right side of the value.  Note
 ! the leading space.
 !
 alter-locale
  money-sign = ' AU$'               ! Australian dollars
  money-sign-location = right

 let #value = 123456
 show #value edit '$999,999,999,999.99'
 show #value edit '$$$$,$$$$999,999.99'

Produces the following output:

        123,456.00 AU$
        123,456.00 AU$

 !
 ! Reset locale to SQR defaults and flip the thousand and
 ! decimal separator characters.
 !
 alter-locale
  locale = 'System'
  thousand-separator = '.'
  decimal-separator = ','
 
 let #value = 123456
 show #value edit '999,999,999,999.99'

Produces the following output:

123.456,00

 !
 ! Reset locale to SQR defaults and change the date and time
 ! separators
 !
 alter-locale
  locale = 'System'
  date-separator = '-'
  time-separator = '.'
 
 let $edit = 'Mon/DD/YYYY HH:MI:SS'
 let $date = strtodate('Jan/01/2004 11:22:33', $edit)
 show $date edit :$edit

Produces the following output:

Jan-01-2004 11.22.33

 !
 ! Reset locale to SQR defaults and change the text used with
 ! the edit options 'na', 'am', 'pm', 'bc, 'ad'
 !
 alter-locale
  locale = 'System'
  edit-option-na = 'not/applicable'
  edit-option-am = 'a.m.'
  edit-option-pm = 'p.m.'
  edit-option-bc = 'b.c.'
  edit-option-ad = 'a.d.'
 
 let $value = ''
 let $edit = 'Mon DD YYYY HH:MI'
 let $date1 = strtodate('Jan 01 2004 11:59', $edit)
 let $date2 = strtodate('Feb 28 2004 12:01', $edit)
 show $value edit '999,999,999,999.99Na'
 show $date1 edit 'Mon DD YYYY HH:MI:SS PM'
 show $date2 edit 'Mon DD YYYY HH:MI:SS pm'

Produces the following output:

    Not/Applicable
Jan 01 2004 11:59:00 A.M.
Feb 28 2004 12:01:00 p.m.

 !
 ! Input some dates using the 'system' locale and
 ! output using other locales from the SQR.INI file.
 !
 alter-locale
  locale = 'System'
 let $date1 = strtodate('Jan 01 2004', 'Mon DD YYYY')
 let $date2 = strtodate('Feb 28 2004', 'Mon DD YYYY')
 let $date3 = strtodate('Mar 15 2004', 'Mon DD YYYY')
 show 'System:'
 show
 show $date1 edit 'Month DD YYYY' ' is ' $date1 edit 'Day'
 show $date2 edit 'Month DD YYYY' ' is ' $date2 edit 'Day'
 show $date3 edit 'Month DD YYYY' ' is ' $date3 edit 'Day'
 alter-locale
  locale = 'German'
 show
 show 'German:'
 show 
 show $date1 edit 'DD Month YYYY' ' ist ' $date1 edit 'Day'
 show $date2 edit 'DD Month YYYY' ' ist ' $date2 edit 'Day'
 show $date3 edit 'DD Month YYYY' ' ist ' $date3 edit 'Day'
 alter-locale
  locale = 'Spanish'
 show
 show 'Spanish:'
 show
 show $date1 edit 'DD Month YYYY' ' es ' $date1 edit 'Day'
 show $date2 edit 'DD Month YYYY' ' es ' $date2 edit 'Day'
 show $date3 edit 'DD Month YYYY' ' es ' $date3 edit 'Day'

Produces the following output:

System:

January 01 2004 is Thursday
February 28 2004 is Saturday
March 15 2004 is Monday

German:

01 Januar 2004 ist Donnerstag
28 Februar 2004 ist Samstag
15 März 2004 ist Montag

Spanish:

01 enero 2004 es jueves
28 febrero 2004 es sábado
15 marzo 2004 es lunes

See Also

Using the PSSQR.INI File and the PSSQR Command Line.

DISPLAY, LET, MOVE, PRINT, SHOW

ALTER-PRINTER

Syntax

ALTER-PRINTER
[POINT-SIZE={point_size_num_lit|_var}]
[FONT-TYPE={font_type|txt_var}]
[SYMBOL-SET={symbol_set_id|txt_var}]
[FONT={font_int_lit|_var}]
[PITCH={pitch_num_lit|_var}]

Description

Alters printer parameters at runtime.

You can place the ALTER-PRINTER command in any part of an SQR program except the SETUP section.

ALTER-PRINTER attempts to change the attributes of the current printer for the current report. If an attribute does not apply to the current printer, it is ignored. For example, ALTER-PRINTER is ignored if it specifies proportional fonts for a report printed on a line printer. When your program is creating multiple reports and the printer is shared by another report, the attributes are changed for that report as well.

Parameters

Example

Change the font and symbol set for the current printer:

alter-printer

font=4   ! Helvetica

symbol-set=12U   ! PC-850 Multilingual

If the output prints to a PostScript printer, the SYMBOL-SET argument is ignored; however, if the .spf file is kept (-KEEP) and later printed on an HP LaserJet, the symbol set 12U can be used.

See Also

The DECLARE-PRINTER command and the -KEEP command-line flag.

ALTER-REPORT

Syntax

ALTER-REPORT
 [HEADING={heading_name_txt_lit|_var|_col}]
 [HEADING-SIZE={heading_size_int_lit|_var|_col}]
 [FOOTING={footing_name_txt_lit|_var|_col}]
 [FOOTING-SIZE={heading_size_int_lit|_var|_col}]

Description

Alters some of the report-specific functionality.

This command enables you to dynamically change the heading or footing sections that are active for the current report. You can also change how much space the heading or footing sections occupy.

If the HEADING or FOOTING value is set to NONE, the section is disabled for the current report.

If the HEADING or FOOTING value is set to DEFAULT, the section reverts to the setting that was in effect when the report was initiated.

If no HEADING or FOOTING value is set, the HEADING-SIZE or FOOTING-SIZE values affect the HEADING/FOOTING currently being used.

If the ALTER-REPORT command was not invoked from within a BEGIN-HEADING or BEGIN-FOOTING section and the page has not been written to, the assignment takes effect immediately; otherwise, it takes effect for the next page.

Parameters

Example

The following example illustrates the ALTER-REPORT command:

begin-footing 2 name=confidental
  print 'Company Confidential' (1,1,0) center
  page-number (2,37,0)
end-footing

begin-footing 2       name=proprietary
  print 'Company Proprietary' (1,1,0) center
  page-number (2,37,0)
end-footing

begin-report
 alter-report 
   footing = 'Proprietary'
   footing-size = 6                     ! Increase depth

.
.
.
end-report

See Also

The BEGIN-FOOTING and BEGIN-HEADING commands in this section.

ARRAY-ADD, ARRAY-DIVIDE, ARRAY-MULTIPLY, ARRAY-SUBTRACT

Syntax

ARRAY-ADD{src_num_lit|_var|_col}...TO
dst_array_name (element_lit|_var|_col)[field
[(occurs_lit|_var|_col)]]...
ARRAY-DIVIDE{src_num_lit|_var|_col}...INTO
dst_array_name (element_int_lit|_var|_col)[field
[(occurs_lit|_var|_col)]]...
ARRAY-MULTIPLY{src_num_lit|_var|_col}...TIMES
dst_array_name (element_int_lit|_var|_col)[field
[(occurs_lit|_var|_col)]]...
ARRAY-SUBTRACT{src_num_lit|_var|_col}...FROM
dst_array_name (element_int_lit|_var|_col)[field
[(occurs_lit|_var|_col)]]...

Description

These four commands perform arithmetic operations on one or more elements in an array.

The following information applies to the array arithmetic commands:

• The array must first be created with the CREATE-ARRAY command.

• The four array arithmetic commands perform on one or more source numbers, placing the result into the corresponding field in the array.

• Array element and field occurrence numbers can be numeric literals (123) or numeric variables (#j) and can be from zero (0) to one less than the size of the array.

• If fields are not listed, the results are placed into consecutively defined fields in the array. If fields are listed, results are placed into those fields at the specified occurrence of the field. If an occurrence is not specified, the zeroth (0) occurrence is used.

• All fields must be of type number, decimal, float, or integer. They cannot be of type date, char, or text.

• If division by zero is attempted, a warning message appears, the result field is unchanged, and SQR continues running.

Parameters

Example

The following example adds &salary and #comm to the first two fields defined in the emps array. The #j'th element of the array is used:

array-add &salary #comm to emps(#j)

The following example subtracts #lost, #count, and 1 from the fields loses, total, and sequence of the #j2'th element of the stats array:

array-subtract #lost #count 1 from stats(#j2) loses total sequence

The following example multiplies occurrences 0 through 2 of the field p in the #i'th element of the percentages array by 2:

array-multiply 2 2 2 times percentages(#i) p(0) p(1) p(2)

The following example divides the #i2'th occurrence of the salesman field of the #j'th element of the commissions array by 100:

array-divide 100 into commissions(#j) salesman(#i2)

The following example uses the ARRAY-ADD command in an SQR program:

begin-setup
! declare arrays
create-array name=emps size=1              ! one row needed for this example
   field=Salary:number=35000               ! initialize to 35,000
   field=Comm:number=5000                  ! initialize to 5,000
end-setup

begin-program
do Main
end-program

begin-procedure Main local
! Show original contents of the arrays, then the modified arrays
! array-add
! retrieve values from the only row of array "emps"
get #sal #com FROM emps(0) Salary Comm
print 'Array-Add'         (+1, 1)

print 'Add 1000 to each column'  (+1, 1)
print 'Salary'  (+1, 3) bold underline
print 'Comm'  (,25) bold underline

print #sal   (+1, 1) money
print #com   (,22) money

let #salary = 1000
let #commission = 1000
let #j = 0  ! address the array row with variable "#j"
! Add 1000 (in variables) to each column of row 0 (the 1st and only row)
array-add #salary  #commission  TO emps(#j)
! retrieved the new "added" values
get #sal #com FROM emps(0) Salary Comm
print #sal  (+1,1) money
print #com  (,22) money
end-procedure

See Also

The CREATE-ARRAY command for information about creating an array.

The CLEAR-ARRAY command for information about clearing or initializing an array.

The GET, PUT, and LET commands for information about using arrays.

ASK

Syntax

ASK substitution_variable [prompt]

Description

Retrieves values for a compile-time substitution variable. The retrieval can be by user input or command-line arguments, or as entries in the @file on the command line.

The value of the substitution variable replaces the reference variable in the program. Variables are referenced by enclosing the variable name in braces, for example, '{state_name}'. When the substitution variable is text or date, enclose the brackets with single quotes. Substitutions are made when the program is compiled and are saved in the .sqt file. Each variable can be referenced multiple times.

ASK is used only in the SETUP section and must appear prior to any substitution variable references.

You cannot break the ASK command across program lines.

Parameters

Example

In the following example, state takes the value entered by the user in response to the prompt Enter state for this report:

begin-setup
  ask state 'Enter state for this report'
end-setup
  ...
begin-select
name, city, state, zip
from customers where state = '{state}'
end-select

See Also

Compiling Programs and Using SQR Execute.

The INPUT command for information about input at runtime.

BEGIN-DOCUMENT

Syntax

BEGIN-DOCUMENT position
END-DOCUMENT

Description

Begins a document paragraph . A document paragraph enables you to write free-form text to create form letters, invoices, and so on.

You can reference database columns, SQR variables, and document markers within a document. Their locations in the document determine where they print on the page. You should not use tabs inside a document paragraph. To indent text or fields, use the spacebar. Note also that if the variables being printed inside a document paragraph are variable in length, you might need to manipulate the variable outside the document paragraph.

Note. A document must be run before you can reference its document markers. Because documents can be printed at relative positions on the page, the actual location of a document marker may not be known by SQR until the document itself has been run.

Parameters

Example

The following example illustrates the BEGIN-DOCUMENT command

begin-document (1,1)
.b
Dear $firstname
  ...
end-document

See Also

Creating Form Letters.

END-DOCUMENT

BEGIN-EXECUTE

Syntax

BEGIN-EXECUTE
[CONNECTION=uq_txt_lit]
[ON-ERROR=procedure[(arg1[,argi]...)]]
[RSV=num_var]
[STATUS=list_var|num_var|txt_var]
[SCHEMA=txt_lit|txt_var]
[PROCEDURE=txt_lit|txt_var
[PARAMETERS=(arg1[IN|INOUT|NULL[,argi[IN|INOUT]]...]])]
|COMMAND=txt_lit|txt_var
|GETDATA=txt_lit|txt_var]
[BEGIN-SELECT[BEFORE=sqr_procedure[(arg1[,argi]...]])]]
[AFTER=sqr_procedure[(arg1[,argi]...]])]]]
col-name type=char|text|number|date[edit-mask]
[on-break]...
FROM ROWSETS=({m,-n,n-m,m-|all})
|FROM PARAMETER=txt_lit|txt_var
END-SELECT]
END-EXECUTE

Description

Begins a new construct. In a BEGIN-EXECUTE paragraph, the syntax of BEGIN-SELECT varies as shown in the following syntax:

Parameters

Note. This is a similar concept to the PARAMETERS = statement in DECLARE-CONNECTION, with the minor difference that the properties specified here alter the flow of returned information, as opposed to just setting login properties. Can be used in conjunction with any data-access model (Procedure, Command, Getdata). An application of this statement would be in the MDB setting, where it might be used to specify such things as Level, Generation, or Include-Column, for example, PROPERTIES = ( 'SetColumn' = 5 )

Example

The following example illustrates the BEGIN-EXECUTE command

begin-setup
   declare-variable 
      date $when_ordered 
      text $ship_method
      integer #theRow
      integer #theStatus
      integer #howMany
   end-declare
end-setup

input #howMany type=integer 
input $pword
let %parm1 = list($when_ordered, $ship_method, #howMany)

declare-connection SAPR3
user=scott 
parameters=clientno=5;node=starfish;
end-declare

alter-connection 
   name=SAPR3
   password=$pword

Begin-Execute 
   connection=SAPR3 
      rsv=#theRow 
         status=#theStatus
            on-error=it_failed(#theStatus)
            procedure='CreditHistory version 5'
                parameters=(%parm1,'recalculate')

   print 'proc ran OK, status is ' (+1,1)
   print #theStatus (,+5) edit 999

Begin-Select before=do_eject after=cleanup
city &col=char (1,1) on-break level=1 after=city-tot
keyval type=number (1,+1) 
rcvd type=date (0,+2)
from Rowsets=(1)
End-Select

End-Execute

See Also

EXECUTE

BEGIN-FOOTING

Syntax

BEGIN-FOOTING footing_lines_int_lit
[FOR-REPORTS=(ALL|report_name1[,report_namei]...)]
[FOR-TOCS=(ALL|toc_name1[,toc_namei]...)]
[NAME={footing_name}]
END-FOOTING

Description

Begins the FOOTING section.

The FOOTING section defines and controls the information to be printed at the bottom of each page.

You must define the report_name in a DECLARE-REPORT paragraph. If you do not use DECLARE-REPORT, the footing is applied to all reports. You can also specify FOR-REPORTS=(ALL). Note that the parentheses are required.

You can specify more than one BEGIN-FOOTING section; however, only one can exist for each report. A BEGIN-FOOTING section with FOR-REPORTS=(ALL) can be followed by other BEGIN-FOOTING sections for specific reports, which override the ALL setting.

You must define the toc_name in a DECLARE-TOC paragraph. You can also specify FOR-TOCS=(ALL). Note that the parentheses are required.

You can specify more than one BEGIN-FOOTING section; however, only one section can exist for each table of contents. A BEGIN-FOOTING section with FOR-TOCS=(ALL) can be followed by other BEGIN-FOOTING sections for a specific table of contents, which override the ALL setting.

The BEGIN-FOOTING section can be shared between reports and tables of contents.

You can print outside the footing area of the report—that is, in the body area—from the footing, but you cannot print in the footing area from the body.

Parameters

Example

The following example illustrates the BEGIN-FOOTING command

begin-footing 2 for-reports=(customer, summary)
  print 'Company Confidential' (1,1,0) center
  page-number (2,37,0)
end-footing
begin-footing 2                      ! For all reports
  print 'Division Report' (1,1,0) center
  page-number (2,37,0)
end-footing
begin-footing 2 for-tocs=(all) 
  print 'Table of Contents' (2,1) 
  let $page = roman(#page-count)     ! ROMAN numerals 
  print $page (,64) 
end-footing

See Also

The ALTER-REPORT command for information about dynamic headings and footings.

The DECLARE-LAYOUT command for information about page layout.

The DECLARE-REPORT command for information about programs with multiple reports.

The DECLARE-TOC command for information about the table of contents.

The END-FOOTING command.

BEGIN-HEADING

Syntax

BEGIN-HEADING heading_lines_int_lit
[FOR-REPORTS=(ALL|
report_name1[, report_namei]...)]
[FOR-TOCS=(ALL|toc_name1[, toc_namei]...)]
[NAME={footing_name}]
END-HEADING

Description

Begins a HEADING section.

The HEADING section defines and controls information to be printed at the top of each page.

You must define the report_name in a DECLARE-REPORT paragraph. If you do not use DECLARE-REPORT, the heading is applied to all reports. You can also specify FOR-REPORTS=(ALL). Note that the parentheses are required.

You can specify more than one BEGIN-HEADING section; however, only one can exist for each report. A BEGIN-HEADING section with FOR-REPORTS=(ALL) can be specified followed by other BEGIN-HEADING sections for specific reports, which override the ALL setting.

You must define the toc_name in a DECLARE-TOC paragraph. You can also specify FOR-TOCS=(ALL). Note that the parentheses are required.

You can specify more than one BEGIN-HEADING section; however, only one section can exist for each table of contents. A BEGIN-HEADING section with FOR-TOCS=(ALL) can be specified, followed by other BEGIN-HEADING sections for specific tables of contents, which override the ALL setting.

The BEGIN-HEADING section can be shared between reports and a table of contents.

You can print outside the heading area of the report—that is, in the body area—from the heading, but you cannot print in the heading area from the body.

Parameters

Example

The following example illustrates the BEGIN-HEADING command

begin-heading 2                              ! Use 2 lines for
print $current-date (1,1) edit MM/DD/YY      ! heading,
  print 'Sales for the Month of ' (1,30)     ! 2nd is blank.
  print $month ()
end-heading
begin-heading 2 for-tocs=(all) 
  print 'Table of Contents' (1,1) bold center 
end-heading

See Also

The ALTER-REPORT command for information about dynamic headings/footings.

The DECLARE-LAYOUT command for information about page layout.

The DECLARE-REPORT command for information about programs with multiple reports.

The DECLARE-TOC command for information about Table of Contents.

The END-HEADING command.

BEGIN-PROCEDURE

Syntax

BEGIN-PROCEDURE procedure_name [LOCAL|(arg1
[, argi]...)]
END-PROCEDURE

Description

Begins a procedure. A procedure is one of the most powerful parts of the SQR language. It enables modularized functions and provides standard execution control.

The procedure name must be unique. The name is referenced in DO commands. Procedures contain other commands and paragraphs (for example, SELECT, SQL, DOCUMENT).

By default, procedures are global. That is, variables or columns defined within a procedure are known and can be referenced outside of the procedure.

A procedure is local when the word LOCAL appears after the procedure name or when the procedure is declared with arguments. That is, variables declared within the procedure are available only within the procedure, even when the same variable name is used elsewhere in the program. In addition, any query defined in a local procedure has local database column variable names assigned that do not conflict with similarly named columns defined in queries in other procedures.

SQR procedures can be called recursively. However, unlike C or Pascal, SQR maintains only one copy of the local variables and they are persistent.

Arguments passed by a DO command to a procedure must match in number:

• Database text or date columns, string variables, and literals can be passed to procedure string arguments. If you are passing a date string to a date argument, the date string must be in the format specified by the SQR_DB_DATE_FORMAT setting, or a database-dependent format, or the database-independent format SYYYYMMDD[HH24[MI[SS[NNNNNN]]]].

  See the Default Database Formats table in the PRINT command description.

• Database numeric columns, numeric variables, and numeric literals can be passed to procedure numeric arguments.

• Numeric variables (DECIMAL, INTEGER, FLOAT) can be passed to procedure numeric arguments without regard to the argument type of the procedure. SQR automatically converts the numeric values upon entering and leaving the procedure as required.

• Date variables or columns can be passed to procedure date or string arguments . When a date variable or column is passed to a string argument, the date is converted to a string according to the following rules:

  • For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting.

    If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

  • For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

    If this has not been set, SQR uses the format listed in the Default Database Formats table.

  • For TIME columns, the format specified by the SQR_DB_TIME_ONLY_FORMAT setting is used.

    If this has not been set, SQR uses the format listed in the TIME Column Formats table.

To reference or declare global variables from a local procedure, add a leading underscore to the variable name, after the initial $, #, or &. (Example: #_amount)

Note. All the SQR-reserved variables, such as #sql-status and $sql-error, are global variables. Within a local procedure, they must be referenced by the leading underscore: #_sql-status or $_sql-error.

Parameters

Example

The following example shows a procedure, main, that also runs the procedure print_list for each row returned from the Select statement. No parameters are passed to print_list:

begin-procedure main
begin-select
name
address
phone
  do print_list
from custlist order by name
end-select
end-procedure               ! main

In the following example, five arguments are passed to the Calc procedure:

do Calc (&tax, 'OH', &county_name, 12, #amount)

begin-procedure Calc(#rate, $state, $county, #months, :#answer)
.
.
.
let #answer = ...
end-procedure

In the preceding example, the value for :#answer is returned to #amount in the calling DO command.

The following example references global variables:

begin-procedure print-it ($a, $b)
print  $_deptname (+2,5,20)           ! $deptname is
print  $a         (,+1)               ! declared outside
print  $b         (,+1)               ! this procedure
end-procedure

See Also

DO, END-PROCEDURE

The Default Database Formats table in the PRINT command description.

BEGIN-PROGRAM

Syntax

BEGIN-PROGRAM
END-PROGRAM

Description

Begins the PROGRAM section of an SQR program.

After processing any commands in the SETUP section, SQR starts program execution at the BEGIN-PROGRAM section. The PROGRAM section typically contains a list of DO commands, though other commands can be used. This is the only required section in an SQR program.

Example

The following example illustrates the BEGIN-PROGRAM command:

begin-program
   do startup
   do main
   do finish
end-program

See Also

BEGIN-REPORT, BEGIN-SETUP, END-PROGRAM

BEGIN-SELECT

Syntax

BEGIN-SELECT[DISTINCT][-Cnn][-Bnn][-XP][-NR][-SORTnn]
[-LOCK{RR|CS|RO|RL|XX}][-DBdatabase]
[-DBconnectionstring]
[LOOPS=nn][ON-ERROR=procedure[(arg1[,argi]...)]]
{column} [&synonym]
{expression &synonym}
{[$columnname] &synonym = (char | number | date)}
[SQR commands]
FROM {table,...|[table:$tablename]}
                [additional SQL]
                [$variable]
END-SELECT

Description

Begins a SELECT paragraph . A SELECT paragraph is the principal means of retrieving data from the database and printing it in a report. A SELECT paragraph must be inside a PROCEDURE or BEGIN-PROGRAM section.

Note that SELECT * FROM is not a valid SQR SQL statement. BEGIN-SELECT can be placed inside a BEGIN-PROGRAM section.

Parameters

The table describes the parameters:

Note. The arguments can span multiple lines; however, the first character position cannot be used unless the continuation character terminated the previous line. If the first character position is used with arguments spanning multiple lines, the argument will be misconstrued as a Select column.

'DSN=ora8;UID=scott;PWD=tiger' 

Example

In this example, duplicate rows are not selected for the city, state, and zip columns because of the distinct keyword. The numbers within parentheses accompanying city, state, and zip define the column positions of these rows. Column names cannot have spaces in front of them.

See Using Column Variables.

begin-select distinct
city      (1,1,30)
state     (0,+2,2)
zip       (1,+3,6)
from custlist order by city
end-select

In this example, the first two columns may be present when the statement is compiled. The column cust_id is declared to be a number. A runtime error occurs if the database table, as identified by the variable $table_name, declares it to be something other than a number.

begin-select             loops=100
[$col_var_char]        &col1=char
[$col_var_num]           &col2=number
cust_id                &id=number
from [$table_name]
[$where clause]
[$order_by_clause]
end-select

In this example, the embedded SQR command Do Print_Row is carried out once for each row.

begin-select distinct
city      (1,1,30)
state     (0,+2,2)
zip       (1,+3,6)
    Do Print_Row
from custlist order by city
end-select

See Also

Selecting Data from the Database, Using Dynamic SQL and Error Checking.

END-SELECT, EXIT-SELECT

BEGIN-SETUP

Syntax

BEGIN-SETUP
END-SETUP

Description

Begins a SETUP section. This optional section is processed prior to the BEGIN-PROGRAM, BEGIN-HEADING, and BEGIN-FOOTING sections.

The SETUP section should be the first section in the program.

The SETUP section contains commands that determine the overall characteristics of the program. The commands used in the SETUP section cannot be used elsewhere unless specified. The SETUP section can include the following commands:

ASK
BEGIN-SQL

(The BEGIN-SQL command can also be used in BEGIN-PROCEDURE paragraphs.)

CREATE-ARRAY

(The CREATE-ARRAY command can also be used in the other sections of an SQR program.)

DECLARE-CHART
DECLARE-IMAGE
DECLARE-LAYOUT
DECLARE-PRINTER
DECLARE-PROCEDURE
DECLARE-REPORT
DECLARE-VARIABLE

(The DECLARE-VARIABLE command can also be used in LOCAL procedures.)

DECLARE-TOC
LOAD-LOOKUP

(The LOAD-LOOKUP command can also be used in the other sections of an SQR program.)

USE

(Sybase and Microsoft SQL Server only.)

Example

The following example illustrates the BEGIN-SETUP command:

begin-setup
   declare-layout customer_list
      paper-size=(8.5, 11)
      left-margin=1.0
      right-margin=1.0
   end-declare
end-setup

See Also

ASK, BEGIN-SQL, CREATE-ARRAY, LOAD-LOOKUP, USE

BEGIN-SQL

Syntax

BEGIN-SQL[-Cnn][-XP][-NR][-SORTnn]
[-LOCK{RR|CS|RO|RL|XX}]
[-DBdatabase][-DBconnectionstring]
   [ON-ERROR=procedure[(arg1[,argi]]...)]! In the SETUP section
   |[ON-ERROR={STOP|WARN|SKIP}](insetup)! Outside the SETUP section
END-SQL

Description

Begins an SQL paragraph . This paragraph can reside in a BEGIN-PROCEDURE, BEGIN-SETUP, or BEGIN-PROGRAM section.

BEGIN-SQL starts all SQL statements except SELECT, which has its own BEGIN-SELECT paragraph. If a single paragraph contains more than one SQL statement, each statement except the last must be terminated by a semicolon (;).

If a single paragraph contains more than one SQL statement, and the -C flag is used, all are assigned the same context area size or logical connection number.

Only non-SELECT statements can be used (except SELECT INTO for Sybase and Microsoft SQL Server). Columns and variables can be referenced in the SQL statements.

Stored Procedures

For Oracle, stored procedures are implemented by PL/SQL in the BEGIN-SQL paragraph. For Sybase and Microsoft SQL Server, SQR supports stored procedures with the EXECUTE command.

For some databases, such as Oracle, using DDL statements within a BEGIN-SQL paragraph causes a commit of outstanding inserts, updates, and deletes and releases cursors. For this reason, ensure that these are done in the proper order or the results will be unpredictable.

Oracle PL/SQL

For Oracle, PL/SQL is supported in a BEGIN-SQL paragraph. This requires an additional semicolon at the end of each PL/SQL statement.

For Oracle PL/SQL:

begin-sql
declare
   varpl varchar2 (25);;
   var2 number (8,2);;
begin
varpl :='abcdefg';;
$v1 :=varpl;;
$v2 :='1230894asd';; 
var2 :=1234.56;; 
#v :=var2;;
end;;
end-sql

For Oracle stored procedures:

begin-sql
begin
#dept_number :=get_dept_no($dept_name);;
end;;
end-sql

Parameters

DSN=data_source_name[;keyword=value[;keyword=value[;...]]]

'DSN=ora8;UID=scott;PWD=tiger'

Example

The following example illustrates the BEGIN-SQL command:

begin-sql
update orders set invoice_num = #next_invoice_num

where order_num = &order_num
end-sql

begin sql
delete orders

where order_num = &order_num;
insert into orders values ($customer_name, #order_num,...)
end-sql

See Also

Using Dynamic SQL and Error Checking, Using Additional SQL Statements with SQR.

END-SQL, BEGIN-PROCEDURE, EXECUTE

The -S command-line flag.

BREAK

Syntax

BREAK

Description

Causes an exit from within an EVALUATE or WHILE command. Execution then continues to the command immediately following the END-WHILE or END-EVALUATE.

This command is used inside a WHILE ... END-WHILE loop or within an EVALUATE command.

See Also

WHILE, EVALUATE

CALL, CALL SYSTEM

Syntax

CALL subroutine USING  {src_txt_lit|_var|_col}|{ src_num_lit|_var|_col} {dst_txt_var|_num_var}  [param]

To issue operating system commands from within an SQR program, use the following syntax:

CALL SYSTEM USING command status [ WAIT | NOWAIT ]

Description

Issues an operating system command or calls a subroutine that you have written in another language, such as C or COBOL, and passes the specified parameters.

You can write your own subroutines to perform tasks that are awkward in SQR. Subroutines can be written in any language.

Warning! Oracle recommends that the UCALL function not use any database calls because it may cause erroneous results.

Used in an SQR program, CALL has the following format:

   CALL ​your_sub USING ​source destination [param_literal]
   CALL SYSTEM USING ​command ​status ​[WAIT|NOWAIT]

The CALL SYSTEM is a special subroutine that is provided as part of SQR to enable the program to issue operating system commands. Its arguments, command, status, and WAIT|NOWAIT are described subsequently.

The values of the source and destination variables and the parameter's literal value are passed to your subroutine. Upon return from the subroutine, a value is placed in the destination variable.

You must write the subroutine and call it in one of the supplied UCALL routines. Optionally, you could rewrite UCALL in another language instead.

The source file UCALL.C contains sample subroutines written in C. The UCALL function takes the following arguments:

When you use the CALL command, your arguments are processed in the following way:

• Calling arguments are copied into the variables depending on the type of argument. Strings are placed into strsrc and numerics are placed into dblsrc.

• Return values are placed into strdes or dbldes depending on whether your destination argument for CALL is a string or numeric variable.

The destination arguments can also be used to pass values to your subroutine.

To access your subroutine, add a reference to it in UCALL and pass along the arguments that you need.

You must relink SQR to CALL after compiling a user-defined function that becomes another SQR function.

If you have created a new object file, you must add your subroutine to the link command file: in UNIX/Linux it is called SQRMAKE; in Microsoft Windows it is called SQREXT.MAK. (Alternatively, you could add your routine to the bottom of the UCALL source module that is already included in the link).

Your subroutine and calling SQR program are responsible for passing the correct string or numeric variables and optional parameter string to the subroutine. No checking is performed.

Parameters

Example

For example, if your program runs under UNIX and you want to make a copy of a file, you can use the following code in your program:

!Executing a UNIX command from an SQR program
Let $Command_String='cp /usr/tmp/file1.dat /usr/tmp/file2.dat'
Call System Using $Command_String #Status
If #Status<>0
  Show 'Error executing the command in Unix: '$command
End-If

See these sample subroutines included in the UCALL source file:

• TODASH shows how strings can be manipulated.

• SQROOT demonstrates how to access numerics.

• SYSTEM invokes a secondary command processor.

The following code calls these subroutines:

call todash using $addr $newaddr '/.',                   ! Convert these to 
                                                         ! dashes
call sqroot using #n #n2                                 ! Put square root of
                                                         ! #n into #n2
call sqroot using &hnvr #j                               ! Hnvr is numeric
                                                         ! database column
call system using 'dir' #s                               ! Get directory listing

The following example uses the SYSTEM argument to issue an operating system command. Some operating systems enable you to invoke a secondary command processor to enter one or more commands and then return to SQR.

! Unix (Type 'exit' to return to SQR)
!
let $shell = getenv('SHELL')
if isblank($shell)
  let $shell = '/bin/sh'
end-if
call system using $shell #unix_status

!Windows (Type 'exit' to return to SQR)
!
let $comspec = getenv('COMSPEC')
let $cmd = comspec || '/c' ||$comspec || ' /k' 
call system using $cmd #win_status wait

The following step-by-step example shows how to add a user-defined subroutine to SQR so that it can be invoked from SQR with the CALL command. For this example, the C function initcap, which makes the first letter of a string uppercase, is added. The function accepts two parameters. The first parameter is the string to which the initcap function is applied. The second is the resultant string.

To add the initcap function to SQR, you need to make the following modifications to the UCALL.C file that was provided with SQR:

1. Add the prototype for the initcap function:

   static void initcap CC_ARGS((char *, char *));

2. Modify the UCALL routine in the UCALL.C file.

   Specifically, add an else if statement at the end of the if statement to check for the initcap function:

      void ucall CC_ARGL((callname, strsrc, strdes, dblsrc, dbldes, params))
      ...   
   
          /* If other subroutines, add "else if..." statement for each */
          else if (strcmp(callname,"initcap") == 0)
              initcap(strsrc, strdes);
          else
              sq999("Unknown CALLed subroutine:  %s\n", callname);
          return;
          }
   

3. At the end of the UCALL.C file, add the initcap routine listed in the following example.

   The routine name must be lowercase; however, in your SQR program, it can be referenced by using either uppercase or lowercase.

   static void initcap CC_ARGL((strsrc, strdes))
   CC_ARG(char *, strsrc)               /* Pointer to source string      */
   CC_LARG(char *, strdes)              /* Pointer to destination string */
      {
      int nIndex;
      int nToUpCase;
      char cChar;
      
      nToUpCase = 1;
      for (nIndex = 0; cChar = strsrc[nIndex]; nIndex++)
         {
         if (isalnum(cChar))
            {
            if (nToUpCase)
               strdes[nIndex] = islower(cChar) ? toupper(cChar) : cChar;
            else
               strdes[nIndex] = isupper(cChar) ? tolower(cChar) : cChar;
            nToUpCase = 0;
            }
         else
            {
            nToUpCase = 1;
            strdes[nIndex] = cChar;
            }
         }
      strdes[nIndex] = '\0';
      }

Note. The CC_ARG macros are defined in the UCALL.C source module. The macros enable the programmer to define a fully prototyped function without concern for whether the C compiler supports the feature.

After these modifications, recompile UCALL.C and relink SQR. See the programming manual for your particular machine for details.

Finally, the following example shows a simple SQR program that uses the initcap function:

begin-program
   input $name 'Enter the first name '! Get the first name from the user 
   lowercase $name                    ! Set the first name to all lowercase 
   call initcap using $name $capname  ! Now set the first character to uppercase
   input $last 'Enter the last name ' ! Get the last name from the user 
   lowercase $last                    ! Set the last name to all lowercase 
   call initcap using $last $caplast  ! Now set the first character to uppercase
   .
   .
   .

See Also

The LET command for information about user-defined functions using UFUNC.C that can be used in the context of an expression and that can either or both pass and return any number of arguments.

CLEAR-ARRAY

Syntax

CLEAR-ARRAY NAME=array_name

Description

Resets each field of an array to its initial value.

The CLEAR-ARRAY command resets each field of the named array to the initial value specified for that field in the CREATE-ARRAY command. If no initial value was specified, numeric fields are reset to zero, text fields are reset to null, and date fields are reset to null. CLEAR-ARRAY also releases all memory that was used by the specified array and returns it to its pristine state.

Parameters

Example

The following example illustrates the CLEAR-ARRAY command:

clear-array  name=custs

See Also

CREATE-ARRAY

CLOSE

Syntax

CLOSE {filenum_lit|_var_col}

Description

Closes a file, specified by its file number.

Closes a flat file that was previously opened with the OPEN command.

Parameters

Example

The following example illustrates the CLOSE command:

close 5
close #j

See Also

OPEN, READ, WRITE

COLUMNS

Syntax

COLUMNS {int_lit|_var|_col}[int_lit|_var|_col]...

Description

Defines logical columns to be used for PRINT commands.

COLUMNS defines the leftmost position of one or more columns within the current page layout. It sets the first column as current.

You can use COLUMNS for printing data either down the page or across the page, depending on how you use the NEXT-COLUMN and USE-COLUMN commands.

The COLUMNS command applies only to the current report. If you want to print columns in more than one report, you must specify the COLUMNS command for each report.

The USE-COLUMN 0 deselects columns. See USE-COLUMN.

Parameters

See Also

NEXT-COLUMN, NEXT-LISTING, NEW-PAGE, USE-COLUMN, USE-REPORT.

COMMIT

Syntax

COMMIT

Description

Causes a database commit.

COMMIT is useful when you are doing many inserts, updates, or deletes in an SQL paragraph. A database commit releases the locks on the records that have been inserted, updated, or deleted. Used with some databases, it also has other effects. For this reason, it should not be used within the scope of an active SELECT paragraph or results will be unpredictable.

When the application finishes, a commit is performed automatically unless a ROLLBACK was done or, for callable SQR, the -XC flag was set.

Other commands or options, such as the CONNECT command and the use of DDL statements for some databases with a BEGIN-SQL paragraph, can also cause the database to do a commit.

COMMIT is an SQR command and should not be used within an SQL paragraph. If COMMIT is used in an SQL paragraph, results will be unpredictable.

Note. The COMMIT command can be used with SQR servers for Oracle, DB2, Informix, and ODBC. For Sybase and Microsoft SQL Server, use BEGIN TRANSACTION and COMMIT TRANSACTION within SQL paragraphs as in the following code segment.

Example

The following example illustrates the COMMIT command:

add 1 to #updates_done
if #updates_done > 50
    commit
    move 0 to #updates_done
end-if

For Sybase:

...      ! Begin Transaction occurred previously
begin-sql
   insert into custlog values (&cust_num, &update_date)
end-sql
add 1 to #inserts
if #inserts >= 50
   begin-sql
      commit transaction;             ! Commit every 50 rows
      begin transaction               ! Begin next transaction
   end-sql
   move 0 to #inserts
end-if

...      ! One more Commit Transaction is needed

Warning! Any data being changed by a current transaction is locked by the database and cannot be retrieved in a SELECT paragraph until the transaction is completed by a COMMIT or ROLLBACK statement (or COMMIT TRANSACTION or ROLLBACK TRANSACTION statement for Sybase or Microsoft SQL Server).

CONCAT

Syntax

CONCAT {src_any_lit|_var|_col} WITH 
dst_txt_var[[:$]edit_mask]

Description

Concatenates a variable, column, or literal with a string variable.

The contents of the source field are appended to the end of the destination field.

CONCAT can optionally edit the source field before appending it. You can change edit masks dynamically by placing them in a string variable and referencing the variable name preceded by a colon (:).

Also, the source can be a date variable or column. If an edit mask is not specified, the date is converted to a string according to the following rules:

• For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting.

  If this has not been set, the first database-dependent format as listed in the Default Database Formats table is used.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT.

  If a format has not been set, the format listed in the Default Database Formats table is used.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT.

  If a format has not been set, the format as listed in the Time Column Formats table is used.

Parameters

Example

The following example illustrates the CONCAT command:

concat &zip_plus_4 with $zip '-xxxx'        ! Edit zip plus 4.
concat &descrip with $rec :$desc_edit       ! Edit mask in variable.
concat $date1 with $string                  ! Concatenate a date.

See Also

The PRINT command for information about the Default Database Formats table, the Time Column Formats table, and edit masks.

The LET command for string functions.

STRING, UNSTRING

CONNECT

Syntax

CONNECT {txt_lit|_var|_col}[ON-ERROR=procedure[(arg1
[, argi]...)]]

Description

Logs off the database and logs on under a new username and password.

The new username and password can be stored in a string variable, column, or literal.

Warning! The username and password are not encrypted, so beware of security issues.

After each CONNECT, the reserved variable $username is set to the new username.

All database cursors or logons are closed before the CONNECT occurs. You should not issue a CONNECT within a SELECT or an SQL paragraph while a query is actively fetching or manipulating data from the database.

Parameters

Note. You can optionally specify arguments to be passed to the ON-ERROR procedure. Arguments can be any variable, column, or literal.

Example

The following example illustrates the CONNECT command:

connect  $new-user  on-error=bad-logon($new_user)
connect  'sqr/test'

CREATE-ARRAY

Syntax

CREATE-ARRAY NAME=array_name SIZE=nn 
{FIELD=name:type[:occurs]
[={init_value_txt_lit|_num_lit}]}...

Description

Creates an array of fields to store and process data.

You can define arrays to store intermediate results or data retrieved from the database. For example, a SELECT paragraph can retrieve data, store it in an array, and gather statistics at the same time. When the query finishes, a summary could be printed followed by the data previously stored in the array.

SQR creates arrays before a program starts to run. The CREATE-ARRAY command can be used in any section of a program.

Commands to process arrays include:

CREATE-ARRAY
CLEAR-ARRAY
GET
PUT
ARRAY-ADD
ARRAY-SUBTRACT
ARRAY-MULTIPLY
ARRAY-DIVIDE
LET

The maximum number of arrays in a program is 128; the maximum number of fields per array is 200.

The following code is a representation of an array emps with three fields in which the CREATE-ARRAY command defines the array:

create-array name=emps size=10
field=name:char='Unknown'
   field=rate:number:2=10.50
   field=phone:char='None'

The name is a simple field (one occurrence), rate has two occurrences, and phone is a simple field. Both array elements and field occurrences are referenced beginning with 0 (zero). The rate is referenced by rate(0) or rate(1). The emps array will contain 10 elements, 0 through 9. All name fields are initialized to Unknown, all phone fields are initialized to None, and all rate fields are initialized to 10.50.

Parameters

Example

The following example illustrates the CREATE-ARRAY command:

create-array  name=custs size=100
   field=name:char
   field=no:number
   field=state:char
   field=zip:char
   field=contacts:char:5
   field=last-contacted:date

See Also

The sample report CUSTOMR4.SQR included with SQR.

DECLARE-VARIABLE, ARRAY-ADD, ARRAY-DIVIDE, ARRAY-MULTIPLY, ARRAY-SUBTRACT, GET, PUT, LET, CLEAR-ARRAY.

The LOAD-LOOKUP command for an alternative way to store database tables in memory.

CREATE-COLOR-PALETTE

Syntax

CREATE-COLOR-PALETTE
 NAME = {palette_name_txt_lit}
 COLOR_1 = {rgb_value}
 COLOR_2 = {rgb_value}
 [COLOR_n] = {rgb_value}

Description

Create a color palette.

This command enables you to create a palette of colors. The number of palettes that can be defined in a program is not limited. No gaps are permitted in the palette.

Parameters

Example

The following example illustrates the CREATE-COLOR-PALETTE command:

begin-report
 create-color-palette 
   name = 'funky'
   color_1 = ('blue')
   color_2 = ('red')
   color_3 = ('orange')

  Print-Chart Groovy
   Color-Palette = 'Funky'
 end-report

See Also

DECLARE-CHART, PRINT-CHART

#DEBUG

Syntax

#DEBUG[x...]SQR_Command

Description

Causes the current command to be processed during a debugging session.

A -DEBUG[xx] flag in the SQR command line enables conditional compilation of SQR commands. When this flag is used, any command (including other compiler directives) preceded by the word #DEBUG is processed; other commands are ignored.

This is useful for placing DISPLAY, SHOW, PRINT or other commands in your program for testing and for deactivating them when the report goes into production.

The -DEBUG flag can contain a suffix of up to 10 letters or digits. These characters are used to match any letters or digits appended to the #DEBUG preprocess command inside the program. #DEBUG commands with one or more matching suffix characters are processed; other commands are ignored. Commands without any suffix always match.

In addition, for each -DEBUGxx letter, a substitution variable is defined. For example, if the flag -DEBUGab is used on the command line, three substitution variables are defined: debug, debuga, and debugb. These variables can be referenced in #IFDEF commands to enable or disable whole sections of code for debugging.

Parameters

Example

The following SQR command line contains the -DEBUG flag with no suffixes:

sqr  myprog  sammy/baker  -debug

The following SHOW command in the program is carried out if invoked with the previous command line because the -DEBUG flag was used:

#debug show 'The total is ' #grand-tot 999,999,999

In the following code example, the command line contains the -DEBUG flag with the suffixes a, b, and c:

sqr  myprog  sammy/baker  -debugabc

In the following code example, the first three #DEBUG commands are compiled, but the fourth, beginning #debuge, is not because its suffix does not match any of the suffixes on the -DEBUG flag:

#debuga  show 'Now selecting rows...'
#debug   show 'Finished query.'
#debugb  show 'Inserting new row.'
#debuge  show 'Deleting row.'

The following code example shows the use of an #IF with a #DEBUG:

#debuga  #if {platform}='unix'
#debuga     show 'Platform is UNIX'
#debuga  #endif

See Also

The #IF, #IFDEF, and #IFNDEF commands.

DECLARE-CHART

Syntax

DECLARE-CHART chart_name
[DATA-LABELS=data_labels_lit]
[COLOR-PALETTE=color_palette_lit]
[ITEM-COLOR=(chart_item_keyword_lit, color_value_lit |(r,g,b)]
[CHART-SIZE=(chart_width_int_lit,chart_depth_int_lit)]
[TITLE=title_txt_lit]
[SUB-TITLE=subtitle_txt_lit]
[FILL=fill_lit]
[3D-EFFECTS=3d_effects_lit]
[BORDER=border_lit]
[POINT-MARKERS=point_markers_lit]
[TYPE=chart_type_lit]
[LEGEND=legend_lit]
[LEGEND-TITLE=legend_title_txt_lit]
[LEGEND-PLACEMENT=legend_placement_lit]
[LEGEND-PRESENTATION=legend_presentation_lit]
[PIE-SEGMENT-QUANTITY-DISPLAY=
pie_segement_quantity_display_lit]
[PIE-SEGMENT-PERCENT-DISPLAY=
pie_segement_percent_display_lit]
[PIE-SEGMENT-EXPLODE=pie_segement_explode_lit]
[X-AXIS-LABEL=x_axis_label_txt_lit]
[X-AXIS-MIN-VALUE={x_axis_min_value_lit|_num_lit}]
[X-AXIS-MAX-VALUE={x_axis_max_value_lit|_num_lit}]
[X-AXIS-SCALE=x_axis_scale_lit]
[X-AXIS-MAJOR-TICK-MARKS=x_axis_major_tick_marks_lit]
[X-AXIS-MINOR-TICK-MARKS=x_axis_minor_tick_marks_lit]
[X-AXIS-MAJOR-INCREMENT=
{x_axis_major_increment_lit|_num_lit}]
[X-AXIS-MINOR-INCREMENT=
x_axis_minor_increment_num_lit]
[X-AXIS-TICK-MARK-PLACEMENT=
x_axis_tick_mark_placement_lit]
[X-AXIS-GRID=x_axis_grid_lit]
[Y-AXIS-LABEL=y_axis_label_lit]
[Y-AXIS-MIN-VALUE={y_axis_min_value_lit|_num_lit}]
[Y-AXIS-MAX-VALUE={y_axis_max_value_lit|_num_lit}]
[Y-AXIS-SCALE=y_axis_scale_lit]
[Y-AXIS-MAJOR-TICK-MARKS=y_axis_major_tick_marks_lit]
[Y-AXIS-MINOR-TICK-MARKS=y_axis_minor_tick_marks_lit]
[Y-AXIS-MAJOR-INCREMENT=
{y_axis_major_increment_lit|_num_lit}]
[Y-AXIS-MINOR-INCREMENT=
y_axis_minor_increment_num_lit]
[Y-AXIS-TICK-MARK-PLACEMENT=
y_axis_tick_mark_placement_lit]
[Y-AXIS-GRID=y_axis_grid_lit]
END-DECLARE

Note. If CHART-SIZE is not defined, it must be defined in PRINT-CHART.

Description

Defines the attributes of a chart that can later be displayed by PRINT-CHART.

The DECLARE-CHART command can define the attributes of a chart to be printed as part of a report.

This command can appear only in the SETUP section.

A chart defined with DECLARE-CHART is printed by referencing its name in the PRINT-CHART command. Some or all of the chart attributes can be overridden at runtime with the PRINT-CHART command. As such, DECLARE-CHART is useful when the basic properties of a chart are common to many PRINT-CHART commands.

Note. All DECLARE-CHART attributes can be overridden as part of the PRINT-CHART command. Columns are not supported within the DECLARE-CHART command or the PRINT-CHART command. Attributes that are specified more than once produce a warning, and the first instance is regarded as the actual value. Attributes can be used in any order, with the exception of chart-name, which must follow the DECLARE-CHART keyword.

Also, the FILL specification in the DECLARE-PRINTER command can influence the appearance of the chart. The following table lists the final appearance of the chart with a combination of values for PRINTER.COLOR and CHART.FILL options.

Specifying Chart Data Series Colors

Color palettes are used in the new graphics to set the colors of each data point in a data series. You specify the color palette to be used in a business chart by creating an SQR COLOR-PALETTE using the CREATE-COLOR-PALETTE command. The following code demonstrates how to create the color palette:

Create-Color-Palette
   Name = 'Test-Palette'
   Color_1 = (100,133,238)  
   Color_2 = (0, 0, 255)
   Color_3 = (0,255,0)
   Color_4 = (0,0,255)   
   Color_5 = (0,0,0)

Users can specify any number of palettes, with up to 64 colors defined in each palette. If more data points are in the data sets than are defined colors in the palette, the palette resets and continues to set the data point colors from Color_1 to Color_n.

After a color palette has been defined, it can be used within the DECLARE-CHART and PRINT-CHART commands to specify the color palette to be used. The following code example demonstrates the use of a color palette:

Print-Chart test_Chart
   COLOR-PALETTE = 'Test-Palette'

Specifying Chart Item Colors

Users can specify the foreground and background colors of the individual areas within a business chart using ITEM-COLOR = (rgb-value) within the DECLARE-CHART and PRINT-CHART commands. The following list shows chart item keywords that are valid for ITEM-COLOR:

• ChartBackground: Background color of entire chart area.

• ChartForeground: Text and Line color of chart area.

• HeaderBackground: Area within the text box specified for the Title and Subtitle.

• HeaderForeground: Text color of the Title and Subtitle.

• FooterBackground: Area within the text box specified for the X Axis label.

• FooterForeground: Text color of the X Axis label.

• LegendBackground: Area within the box defining the legend.

• LegendForeground: Text and outline color of the legend.

• ChartAreaBackground: Area that includes the body of the chart.

• ChartAreaForeground: Text and line colors of the chart area.

• PlotAreaBackground: Area within the X and Y Axis of a chart.

• PlotAreaForeground: Text and line colors of the plot area.

Parameters

The following DECLARE-CHART Command Arguments table describes other arguments for the DECLARE-CHART command.

Note. Oracle does not currently support setting NewGraphics to Yes. You should not use the DATA-LABELS, COLOR-PALETTE, and ITEM-COLOR attributes listed in the following table because they are valid only when NewGraphics=Yes.

Example

This code example declares a basic sales chart using DECLARE-CHART. For each region, the SUB-TITLE, DATA-ARRAY, and other elements are overridden to provide the chart with the specific features desired.

begin-setup


declare-chart base_sales_chart
chart-size             = (30, 20 )
title                  = 'Quarterly Sales'
sub-title              = none
fill                   = color
3d-effects             = yes
type                   = stacked-bar
legend-title           = 'Product'
x-axis-grid            = yes
end-declare


end-setup

begin-program


print-chart base_sales_chart
sub-title               = 'Region I' 
data-array              = reg1_sales
data-array-row-count    = #rows_reg1
data-array-column-count = 2
y-axis-max-value        = #max_of_all_regions
y-axis-min-value        = #min_of_all_regions
legend                  = no


print-chart base_sales_chart
sub-title               = 'Region II' 
data-array              = reg2_sales
data-array-row-count    = #rows_reg2
data-array-column-count = 2
y-axis-max-value        = #max_of_all_regions
y-axis-min-value        = #min_of_all_regions
legend                  = no


end-program

begin-procedure chart_region_sales ($sub, $ary,
                     #rows, #cols,
                     #max_of_all_regions,
                     #min_of_all_regions)



print-chart base_sales_chart (20, 15 )
sub-title                 = $sub
data-array                = all sales
data-array-row-count      = #rows
data-array-column-count   = #cols
data-array-column-labels  = ('Q1', 'Q2', 'Q3', 'Q4' )
y-axis-max-value          = #max_of_all_regions
y-axis-min-value          = #min_of_all_regions
chart-size                = (50, 30)


end-procedure

See Also

The PRINT-CHART command.

DECLARE-COLOR-MAP

Syntax

In the SETUP section:

DECLARE-COLOR-MAP
      color_name = ({rgb})
color_name = ({rgb})
.
.
.
END-DECLARE 

Description

Defines colors in an SQR report.

The DECLARE-COLOR-MAP command in the BEGIN-SETUP section defines or redefines colors in an SQR report. You can define an endless number of entries.

Parameters

Example

The following example illustrates the DECLARE-COLOR-MAP command:

begin-setup
   declare-color-map
    light_blue = (193, 222, 229)
   end-declare
end-setup

See Also

The ALTER-COLOR-MAP, SET-COLOR, and GET-COLOR commands in this section.

DECLARE-CONNECTION

Syntax

In the SETUP section:

DECLARE-CONNECTION connection_name_txt_lit
DSN={uq_txt_lit}
[USER={uq_txt_lit}]
[PASSWORD={uq_txt_lit}]
[PARAMETERS=keyword_str=attr_str;[,keyword_str=attr_str ;...]]
END-DECLARE 

In the body of the report:

DECLARE-CONNECTION connection_name
DSN={uq_txt_lit|_var}
[USER={uq_txt_lit|_var}]
[PASSWORD={uq_txt_lit|_var}]
[PARAMETERS=keyword_str=attr_str;[, keyword_str=attr_str;...]]
END-DECLARE 

Description

Defines the datasource logon parameters prior to logon. Can be used to override the default connection logon parameters.

Parameters

Example

The following example illustrates the DECLARE-CONNECTION command:

declare-connection SAPR3-1
   dsn=SAPR3
   username=guest
   password=guest
end-declare

DECLARE-IMAGE

Syntax

DECLARE-IMAGE image_name
[TYPE=image_type_lit]
[IMAGE-SIZE=(width_num_lit,height_num_lit)]
[SOURCE=file_name_lit]
END-DECLARE

Note. If TYPE, IMAGE-SIZE, and SOURCE are not defined in DECLARE-IMAGE, they must be defined in PRINT-IMAGE.

Description

Declares the type, size, and source of an image to be printed.

The DECLARE-IMAGE command defines and names an image. This image can then be placed in a report at the position specified in the PRINT-IMAGE command.

Note. If the image file is unrecognizable, or has incomplete header information, a box (either shaded, for HP printers, or having a diagonal line through it in the case of postscript) appears where the image is expected.

Parameters

Example

The following example illustrates the DECLARE-IMAGE command:

declare-image officer-signature
   type           = eps-file
   source         = 'off_sherman.eps'
   image-size     = (40, 5)
end-declare

See Also

PRINT-IMAGE.

DECLARE-LAYOUT

Syntax

DECLARE-LAYOUT layout_name
[PAPER-SIZE=({paper_width_num_lit[uom],
paper_depth_num_lit[uom]}|{paper_name})]
[FORMFEED=form_feed_lit]
[ORIENTATION=orientation_lit]
[LEFT-MARGIN=left_margin_num_lit[uom]]
[TOP-MARGIN=top_margin_num_lit[uom]]
[RIGHT-MARGIN=right_margin_num_lit[uom]
|LINE-WIDTH=line_width_num_lit[uom]
|MAX-COLUMNS=columns_int_lit]
[BOTTOM-MARGIN=bottom_margin_num_lit[uom]
|PAGE-DEPTH=page_depth_num_lit[uom]
|MAX-LINES=lines_int_lit]
[CHAR-WIDTH=char_width_num_lit[uom]]
[LINE-HEIGHT=line_height_num_lit[uom]]
END-DECLARE

Description

Defines the attributes for the layout of an output file .

The DECLARE-LAYOUT command describes the characteristics of a layout to be used for an output file. A layout can be shared by more than one report. If no DECLARE-LAYOUT is defined or if a DECLARE-REPORT does not reference a defined layout, a layout named DEFAULT is created with the default attribute values shown in the DECLARE-LAYOUT Command Arguments table. For an example of how DECLARE-LAYOUT relates to DECLARE-REPORT, see the DECLARE-REPORT examples in this document.

You can define as many layouts as are necessary for the requirements of the application. You can override the DEFAULT layout attributes by defining a layout called DEFAULT in your program. Each layout name must be unique.

SQR maps its line and column positions on the page by using a grid determined by the LINE-HEIGHT and CHAR-WIDTH arguments. That is, SQR calculates the number of columns per row by dividing the LINE-WIDTH by the CHAR-WIDTH and calculates the number of lines by dividing the PAGE-DEPTH by the LINE-HEIGHT. Each printed segment of text is placed on the page using this grid. Because the characters in proportional fonts vary in width, a word or string may be wider than the horizontal space you have allotted, especially in words containing uppercase letters or boldfaced characters. To account for this behavior, you can either move the column position in the PRINT or POSITION statements or indicate a larger CHAR-WIDTH in the DECLARE-LAYOUT command.

The DECLARE-LAYOUT command selects the proper fonts. In addition, the parameter interacts with PAPER-SIZE in the following way:

• When you do not specify ORIENTATION=LANDSCAPE or the PAPER-SIZE dimensions, SQR creates a page with the dimensions set to 11 inch by 8.5 inch. This results in a page of 100 columns by 45 lines with 0.5 inch margins.

• When you specify PAPER-SIZE=(paper_name), the page orientation is set according to the paper_name specified. If you also specify ORIENTATION and the value differs from the PAPER-SIZE value, the ORIENTATION value overrides the PAPER-SIZE value.

• When you specify PAPER-SIZE=(page_width, page_depth), SQR does not swap the page width and page depth if ORIENTATION=LANDSCAPE.

Parameters

This table lists valid unit of measure suffixes:

This table lists valid paper names for the paper_name parameter.

This table describes the arguments of the DECLARE-LAYOUT command:

Example

This example illustrates the ability to specify these parameters using a different measurement system, such as metric:

!
declare-layout my-layout      ! Results in: 

paper-size=(a4)               !   paper-size=(210mm, 297mm) 

left-margin=12.7 mm           !   top-margin=12.7mm 

right-margin=25.4 mm          !  left-margin=12.7mm
end-declare                   !  right-margin=25.4mm

                              !   bottom-margin=12.7mm

                              !   orientation=portrait

                              !   columns=67

                              !   lines=64

This example changes the page dimensions and also changes the left and right margins to be 1 inch:

!
declare-layout large-paper    ! Results in:

paper-size=(14, 11)           !   paper-size=(14in, 11in)

left-margin=1                 !   top-margin=0.5in 

right-margin=1                !   left-margin=1.0in
end-declare                   !   right-margin=1.0in

                              !   bottom-margin=0.5in

                              !   orientation=portrait

                              !   columns=120

                              !   lines=60

This example retains the default page dimensions and changes the left and right margins to be 1 inch:

declare-layout default        ! Results in:

left-margin=1                 !   paper-size=(8.5in, 11in)

right-margin=1                !   top-margin=0.5in
end-declare                   !   left-margin=1.0in 

                              !   right-margin=1.0in

                              !   bottom-margin=0.5in

                              !   orientation=portrait

                              !   columns=65

                              !   lines=60

This example changes the orientation to landscape; the default page dimensions (8.5 in and 11 in) are swapped, the columns and rows are recalculated, and all other values remain the same:

declare-layout default         ! Results in:

orientation=landscape          !   paper-size=(11in, 8.5in)
end-declare                    !   top-margin=0.5in

                               !   left-margin=0.5in

                               !   right-margin=0.5in

                               !   bottom-margin=0.5in

                               !   orientation=landscape

                               !   columns=100

                               !   lines=45

This example changes the orientation to landscape; the default page dimensions (8.5 in and 11 in) are swapped, and the top margin is set to 1 inch:

declare-layout my_landscape     ! Results in:

orientation=landscape           !   paper-size=(11in, 8.5in)

top-margin=1                    !   top-margin=1.0in
end-declare                     !   left-margin=0.5in

                                !   right-margin=0.5in

                                !   bottom-margin=0.5in

                                !   orientation=landscape

                                !   columns=100

                                !   lines=43

This example illustrates how to specify the page dimensions using one of the predefined names (note that the orientation has also changed because this example is an envelope):

declare-layout envelope          ! Results in: 

paper-size=(com-10)              !   paper-size=(4.125in, 9.5in) 
end-declare                      !   top-margin=0.5in 

                                 !   left-margin=0.5in

                                 !   right-margin=0.5in

                                 !   bottom-margin=0.5in

                                 !   orientation=landscape

                                 !   columns=85

                                 !   lines=18

See Also

DECLARE-REPORT

DECLARE-PRINTER

Syntax

DECLARE-PRINTER printer_name
[FOR-REPORTS=(report_name1[,report_namei]...)]
[TYPE=printer_type_lit]
[INIT-STRING=initialization_string_txt_lit]
[RESET-STRING=reset_string_txt_lit]
[COLOR=color_lit]
[POINT-SIZE=point_size_num_lit]
[FONT-TYPE=font_type_int_lit]
[SYMBOL-SET=symbol_set_id_lit]
[STARTUP-FILE=file_name_txt_lit]
[PITCH=pitch_num_lit]
[FONT=font_int_lit]
[BEFORE-BOLD=before_bold_string_txt_lit]
[AFTER-BOLD=after_bold_string_txt_lit]
END-DECLARE

Description

Overrides the printer defaults for the specified printer type.

Each printer has a set of defaults as listed in the DECLARE-PRINTER Command Arguments table. The DECLARE-PRINTER command overrides these defaults.

Use the DECLARE-PRINTER command in the SETUP section to define the characteristics of the printer or printers to be used. If you need to change some of the arguments depending on the runtime environment, you can use the ALTER-PRINTER command in any part of the program except the PROGRAM and SETUP sections.

A program can contain no more than one DECLARE-PRINTER command for each printer type for each report. If you do not provide a printer declaration, the default specifications are used. You can override the default printer attributes by providing a DECLARE-PRINTER specification for each printer. The names are:

• DEFAULT-LP for line printer.

• DEFAULT-HP for HP LaserJet.

• DEFAULT-HT for HTML.

• DEFAULT-PS for PostScript.

This table lists the arguments, provides the possible choices or measure, lists the default values, and describes the arguments.

This table lists the fonts that are available in SQR for use with the FONT argument for HPLASERJET printer types.

The font that you choose—in orientation, typeface, and point size—must be an internal font, available in a font cartridge, or downloaded to the printer.

For fonts not listed in the Fonts Available for HP LaserJet Printers in SQR table, you must indicate the font style using the FONT-TYPE argument to ensure that the correct typeface is selected by the printer.

This table lists the fonts that are available in SQR for use with the FONT argument for PostScript printer types:

Other type faces can be added to the POSTSCRI.STR file.

Different fonts are available in SQR for Microsoft Windows when you are printing with Microsoft Windows printer drivers (using the -PRINTER:WP command-line flag). When you use the -PRINTER:WP flag, your report is sent directly to the default Microsoft Windows printer. To specify a nondefault Microsoft Windows printer, enter -PRINTER:WP:{printer name}. The {printer name} is the name assigned to your printer. For example, to send output to a Microsoft Windows printer named NewPrinter, you would use -PRINTER:WP:NewPrinter. If your printer name has spaces, enclose the entire argument in quotes.

Fonts are specified by number in the FONT qualifier of the ALTER-PRINTER command.

This table lists the fonts that are available when you are printing with Microsoft Windows printer drivers:

Note. Fonts 6, 8, and 800 are not supplied with Microsoft Windows. You can get these fonts by purchasing the ADOBE Type Manager (ATM). The advantage of using ATM fonts is the compatibility for PostScript printer fonts. The Symbol font uses the SYMBOL_CHARSET instead of the usual ANSI_CHARSET character set. You can add more fonts by editing the appropriate Fonts section in the sqr.ini file.

See Using the PSSQR.INI File and the PSSQR Command Line.

Parameters

Note. The DECLARE-PRINTER Command Arguments table describes the other arguments of the DECLARE-PRINTER command. The table lists the options, default values, and description of each of the arguments.

Example

The following example illustrates the DECLARE-PRINTER command:

declare-printer HP-definition   ! Default HP definition

type=HP                         ! for all reports

font=4                          ! Helvetica

symbol-set=12U                  ! PC-850 Multilingual
end-declare
declare-printer PS-Sales        ! PS definition

for-reports=(sales)             ! for the Sales report

type=PS

font=5                          ! Times-Roman
end-declare

See Also

ALTER-PRINTER, DECLARE-REPORT

DECLARE-PROCEDURE

Syntax

DECLARE-PROCEDURE
[FOR-REPORTS=(report_name1[,report_namei]...)]
[BEFORE-REPORT=procedure_name[(arg1[,argi]...)]]
[AFTER-REPORT=procedure_name[(arg1[,argi]...)]]
[BEFORE-PAGE=procedure_name[(arg1[,argi]...)]]
[AFTER-PAGE=procedure_name[(arg1[,argi]...)]]
END-DECLARE

Description

Declares procedures that are triggered when a specified event occurs.

The DECLARE-PROCEDURE command can be used to define SQR procedures that are to be invoked before or after a report is printed or before the beginning or end of each page.

Issue the DECLARE-PROCEDURE in the SETUP section. For multiple reports, you can use the command as often as required to declare procedures required by all the reports. If you issue multiple DECLARE-PROCEDURE commands, the last one takes precedence. In this way, you can use one command to declare common procedures for ALL reports and others to declare unique procedures for individual reports. The referenced procedures can accept arguments.

If no FOR-REPORTS are specified, ALL is assumed. Initially, the default for each of the four procedure types is NONE. If a procedure is defined in one DECLARE-PROCEDURE for a report, that procedure is used unless NONE is specified.

Use the USE-PROCEDURE command to change the procedures to be used at runtime. To disable a procedure, specify NONE in the USE-PROCEDURE statement.

Parameters

Example

The following example illustrates the DECLARE-PROCEDURE command:

declare-procedure              ! These procedures will

before-report=report_heading   ! be used by all reports

after-report=report_footing
end-declare
declare-procedure             ! These procedures will

for-reports=(customer)        ! be used by the customer

before-page=page_setup        ! report

after-page=page_totals
end-declare

See Also

USE-PROCEDURE

DECLARE-REPORT

Syntax

DECLARE-REPORT report_name
[TOC=toc_name]
[LAYOUT=layout_name]
[PRINTER-TYPE=printer_type]
END-DECLARE

Description

Defines reports and their attributes.

Issue the DECLARE-REPORT in the SETUP section.

You can use the DECLARE-REPORT command to declare one or more reports to be produced in the application.

You must use this command when developing applications to produce more than one report.

Multiple reports can share the same layout and the same printer declarations or each report can use its own layout or printer definitions if the report has unique characteristics.

When you are printing multiple reports, unless report names are specified by the -F command-line flag, the first report declared is generated with the name of program.lis, where program is the application name.

Additional reports are generated with names conforming to the rules dictated by the OUTPUT-FILE-MODE setting in the sqr.ini file.

When the -KEEP or -NOLIS flag is used, the first intermediate print file (.spf file) is generated with a name of program.spf and additional reports are generated with names conforming to the rules dictated by the OUTPUT-FILE-MODE setting in the sqr.ini file.

Parameters

Example

The following example illustrates the DECLARE-REPORT command:

declare-layout customer_layout
   left-margin
   right-margin
end-declare

declare-layout summary_layout
   orientation=landscape
end-declare

declare-report customer_detail

toc=detailed

layout=customer_layout

printer-type=postscript
end-declare
declare-report customer_summary

layout=summary_layout

printer-type=postscript
end-declare
.
.
.
use-report customer_detail

...print customer detail...
use-report customer_summary

...print customer summary...

See Also

USE-REPORT, DECLARE-LAYOUT, DECLARE-PRINTER, DECLARE-TOC

DECLARE-TOC

Syntax

DECLARE-TOC toc_name
[FOR-REPORTS=(report_name1[,report_namei]...)]
[DOT-LEADER=YES|NO]
[INDENTATION=position_count_num_lit]
[BEFORE-TOC=procedure_name[(arg1[,argi]...)]]
[AFTER-TOC=procedure_name[(arg1[,argi]...)]]
[BEFORE-PAGE=procedure_name[(arg1[,argi]...)]]
[AFTER-PAGE=procedure_name[(arg1[,argi]...)]]
[ENTRY=procedure-name [(argi [,argi] ...)]]
END-DECLARE

Description

Defines the table of contents and its attributes.

Use DECLARE-TOC in the SETUP section.

You can use the DECLARE-TOC command to declare one or more tables of contents for the application.

A table of contents can be shared between reports.

Parameters

Example

The following example illustrates the DECLARE-TOC command:

begin-setup
  declare-toc common
    for-reports=(all)
    dot-leader=yes
    indentation=2
end-declare
end-setup
.
.
.
toc-entry level=1 text=$Chapter
toc-entry level=2 text=$Heading
.
.

See Also

BEGIN-FOOTING, BEGIN-HEADING, DECLARE-REPORT, TOC-ENTRY

DECLARE-VARIABLE

Syntax

DECLARE-VARIABLE
[DEFAULT-NUMERIC={DECIMAL[(prec_lit)]|FLOAT|INTEGER}]
[DECIMAL[(prec_lit)]num_var[(prec_lit)][num_var
[(prec_lit)]]...]
[FLOAT num_var[num_var]...]
[DATE date_var[date_var]...]
[INTEGER num_var[num_var]...]
[TEXT string_var[string_var]...]
END-DECLARE

Description

Enables you to explicitly declare a variable type.

You can set the default numeric type externally, using the -DNT command-line flag or the DEFAULT-NUMERIC setting in the Default-Settings section of the sqr.ini file. However, the setting in the DECLARE-VARIABLE command takes precedence over all other settings. If the command has not been used, then the -DNT command-line flag takes precedence over the setting in the sqr.ini file.

In addition to FLOAT, INTEGER, and DECIMAL, you can set DEFAULT-NUMERIC in the sqr.ini file and the -DNT command-line flag to V30. With V30, the program acts in the same manner as in versions prior to release 4.0; that is, all variables are FLOAT. V30 is not a valid setting for the DEFAULT-NUMERIC setting in the DECLARE-VARIABLE command.

The DECLARE-VARIABLE command enables you to determine the type of variables to use. This command can appear only in the SETUP section or as the first statement of a local procedure. The placement of the command affects its scope. When used in the SETUP section, it affects all variables in the entire program. Alternatively, when it is placed in a local procedure, its effect is limited to the scope of the procedure. If the command is in both places, the local declaration takes precedence over the SETUP declaration.

In addition to declaring variables, this command enables you to specify the default numeric type using the DEFAULT-NUMERIC setting as FLOAT, INTEGER, or DECIMAL. When dealing with money or when more precision is required, use the DECIMAL qualifier.

The DECLARE-VARIABLE command, the -DNT command-line flag, and the DEFAULT-NUMERIC setting in the sqr.ini file affect the way numeric literals are typed. If V30 is specified, then all numeric literals are FLOAT (just as in versions prior to release 4.0); otherwise, the use or lack of a decimal point determines the type of the literal as either FLOAT or INTEGER, respectively. Finally, not specifying the DECLARE-VARIABLE command, the -DNT command-line flag, and the DEFAULT-NUMERIC setting in the sqr.ini file is the same as specifying V30.

Parameters

Example

The following example illustrates the DECLARE-VARIABLE command:

begin-setup
  declare-variable 
    default-numeric=float
    decimal  #decimal(10)
    integer  #counter
    date     $date
  end-declare
end-setup
.
.
let $date = strtodate('Jan 01 2004','Mon DD YYYY')
print $date (1,1)
position (+2,1)

let #counter = 0
while #counter < 10
 let #decimal = sqrt(#counter)
 add 1 to counter
 print #decimal  (+1,1) 9.999999999
end-while

do sub1($date, 'day', 10)

do sub2

.
.
begin-procedure sub1(:$dvar, $units, #uval)
declare-variable
 date $dvar
 integer #uval
end-declare
let $dvar = dateadd($dvar, $units, #uval)
print $dvar (+1,1)
position (+2,1)
end-procedure
.
.
begin-procedure sub2 LOCAL
declare-variable
 date $mydate
end-declare
let $mydate = dateadd($_date, 'year', 5)
print $mydate (+1,1)
position (+2,1)
end-procedure
.
.

#DEFINE

Syntax

#DEFINE substitution_variable value

Description

Declares a value for a substitution variable within the body of the report (rather than using the ASK command).

#DEFINE is useful for specifying constants such as column locations, printer fonts, or any number or string that is used in several locations in the program. When the value of the number or string must be changed, you need only change the #DEFINE command. All references to that variable change automatically, which makes modifying programs much simpler.

If the ASK command is used to obtain the value of a substitution variable that has already been defined, ASK uses the previous value and the user is not prompted. This enables you to predefine some variables and not others. When the report runs, ASK requests values for only those variables that have not had a value assigned.

You can use #DEFINE commands inside an include file. This is a method of gathering commonly used declarations into one place, and reusing them for more than one report.

The value in the #DEFINE command can have embedded spaces and does not need to be enclosed within quotes. The entire string is used as is.

The #DEFINE command cannot be broken across program lines.

Parameters

Example

This code example defines several constants:

#define  page_width  8.5
#define  page_depth  11
#define  light LS^10027
#define  bold  LS^03112
#define  col1  1
#define  col2  27
#define  col3  54
#define  order_by  state, county, city, co_name

This code example from a report uses the definitions from the preceding example:

   begin-setup

declare-printer contacts

   type=hp

   paper-size=({page_width}, {page_depth})

end-declare
end-setup
begin-heading 5
  print 'Company Contacts'     (1,1)  center
  print 'Sort:  {order_by}'    (2,1)  center
  print 'Company'              (4,{col1})
  print 'Contact'              (4,{col2})
  print 'Phone'                (4,{col3})
end-heading
begin-procedure main
begin-select
company      (1,{col1})
  print '{bold}'      (0,{col2})         ! Print contact in boldface.
contact         ()
  print '{light}'         ()             ! Back to lightface.
phone         (0,{col3})                 ! Note: There must be enough
  next-listing                           ! space between col2
from customers                           ! and col3 for both
order by  {order_by}                     ! font changes and the
end-select                               ! contact field.
end-procedure

See Also

ASK

DISPLAY

Syntax

DISPLAY {any_lit|_var|_col}
[[:$]edit_mask|NUMBER|MONEY|DATE][NOLINE]

Description

Displays the specified column, variable, or literal.

The DISPLAY command can display data to a terminal. The data is displayed to the current location on the screen. If you want to display more than one field on the same line, use NOLINE on each display except the last.

Dates can be contained in a date variable or column, or a string literal, column, or variable. When a date variable or column is displayed without an edit mask, the date appears in the following manner:

• For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting.

  If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format listed in the DATE Column Formats table.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format listed in the TIME Column Formats table.

When the program displays a date in a string literal, column, or variable using EDIT or DATE, the string uses the format specified by the SQR_DB_DATE_FORMAT setting, one of the database-dependent formats as listed in the Default Database Formats table, or the database-independent format SYYYYMMDD[HH24[MI[SS[NNNNNN]]]].

If you require more control over the display, use the SHOW command.

Parameters

Example

The following segments illustrate the various features of the DISPLAY command:

 !
 ! Display a string using an edit mask
 !
 display '123456789' xxx-xx-xxxx

Produces the following output:

123-45-6789


!
 ! Display a number using an edit mask
 !
 display 1234567.89 999,999,999.99

Produces the following output:

1,234,567.89

!
 ! Display a number using the default edit mask (specified in SQR.INI)
 !
 display 123.78

Produces the following output:

123.780000

!
 ! Display a number using the locale default numeric edit mask
 !
 alter-locale  number-edit-mask = '99,999,999.99'
 display 123456.78 number

Produces the following output:

123,456.78

!
 ! Display a number using the locale default money edit mask
 !
 alter-locale  money-edit-mask = '$$,$$$,$$9.99'
 display 123456.78 money

Produces the following output:

$123,456.78

!
 ! Display a date column using the locale default date edit mask
 !
 begin-select
 dcol
  from tables
 end-select
 alter-locale  date-edit-mask = 'DD-Mon-YYYY'
 display &dcol date

Produces the following output:

01-Jan-2004

!
 ! Display two values on the same line
 !
 display 'Hello' noline
 display ' World'

Produces the following output:

Hello World

!
 ! Display two values on the same line with editing of the values
 !
 alter-locale  money-edit-mask = '$$,$$$,$$9.99'
 let #taxes = 123456.78
 display 'You owe ' noline
 display #taxes money noline
 display ' in back taxes.'

Produces the following output:

You owe $123,456.78 in back taxes.

See Also

The SHOW command for information about screen control.

The LET command for information about copying, editing, or converting fields.

The EDIT parameter of the PRINT command for a description of the edit masks.

The ALTER-LOCALE command for a description of the arguments NUMBER-EDIT-MASK, MONEY-EDIT-MASK, and DATE-EDIT-MASK.

DIVIDE

Syntax

DIVIDE {src_num_lit|_var|_col} INTO dst_num_var
[ON-ERROR={HIGH|ZERO}][ROUND=nn]

Description

Divides one number into another.

The source field is divided into the destination field and the result is placed in the destination. The source is always first, the destination always second.

When dealing with money-related values (dollars and cents), use decimal variables rather than float variables. Float variables are stored as double-precision floating-point numbers, and small inaccuracies can appear when many numbers are divided in succession. These inaccuracies can appear due to the way different hardware and software implementations represent floating point numbers.

Parameters

Example

The following example illustrates the DIVIDE command:

divide  37.5  into  #price   ! #price / 37.5
divide  &rate  into  #tot  on-error=high
divide  #j  into  #subtot  on-error=zero

Note. In the preceding example, High is the maximum value and zero is the lowest value.

See Also

ADD

The LET command for a discussion of complex arithmetic expressions.

DO

Syntax

DO procedure_name[(arg1[, argi]...)]

Description

Invokes the specified procedure.

When the procedure ends, processing continues with the command following the DO command. You can use arguments to send values to or receive values from a procedure.

Arguments passed by a DO command to a procedure must match in number:

• Database text columns, string variables, and literals can be passed to procedure string or date arguments.

• Database numeric columns, numeric variables, and numeric literals can be passed to procedure numeric arguments.

• Numeric variables (DECIMAL, INTEGER, FLOAT) can be passed to procedure numeric arguments without regard to the argument type of the procedure.

  SQR automatically converts the numeric values upon entering and leaving the procedure as required.

• Date variables can be passed to procedure date or string arguments.

When a field in a DO command receives a value back from a procedure (a colon indicates that it is a back value, that is, a value that is being returned), it must be a string, numeric, or date variable, depending on the procedure argument; however, a date can be returned to a string variable and vice versa.

When a date is passed to a string, the date is converted to a string according to the following rules:

• For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting.

  If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format listed in the DATE Column Formats table.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format as listed in the TIME Column Formats table.

Parameters

Example

The following example illustrates the DO command:

do get_names
do add_to_list ($name)
do print_list ('A', #total, &co_name, $name)

See Also

The BEGIN-PROCEDURE command for information about passing arguments.

The PRINT command for information about date and time formats.

#ELSE

Syntax

#ELSE

Description

Compiles the code following the #ELSE command when a preceding #IF, #IFDEF, or #IFNDEF command is FALSE. (#ELSE is a compiler directive that works with the #IF, #IFDEF, and #IFNDEF compiler directives.)

See Also

The #IF, #IFDEF, and #IFNDEF commands for a description of each compiler directive.

ELSE

Syntax

ELSE

Description

ELSE is an optional command in an IF command.

See Also

The IF command for a description and example.

ENCODE

Syntax

ENCODE src_code_string_lit INTO dst_txt_var

Description

Assigns a nondisplay or display character to a string variable.

The ENCODE command can define nondisplay characters or escape sequences sent to an output device. These characters or sequences can perform complex output device manipulations. The ENCODE command also displays characters not on the keyboard. If your keyboard does not have the euro symbol, use the ENCODE feature to create a string variable for it.

The encode characters can be included in a report at the appropriate location using a PRINT or PRINT-DIRECT command.

Unicode (UCS-2) code that points from <1> to <65535> can be defined in the ENCODE command.

Parameters

Example

The following example illustrates the ENCODE command:

encode '<27>L11233' into $bold            ! Code sequence to turn bold on.
print $bold () code-printer=lp

See Also

The chr function described in the Miscellaneous Functions table under the LET command.

PRINT, PRINT-DIRECT

END-DECLARE, END-DOCUMENT, END-EVALUATE, END-FOOTING, END-HEADING

Syntax

END-DECLARE
END-DOCUMENT
END-EVALUATE
END-FOOTING
END-HEADING

Description

Completes a section or paragraph.

The END-DECLARE command completes a paragraph started with:

DECLARE-CHART
DECLARE-IMAGE
DECLARE-LAYOUT
DECLARE-PRINTER
DECLARE-PROCEDURE
DECLARE-REPORT
DECLARE-VARIABLE

Other END- commands complete the corresponding BEGIN- command:

BEGIN-DOCUMENT
EVALUATE
BEGIN-FOOTING
BEGIN-HEADING

Each command must begin on its own line.

Example

The following example illustrates the BEGIN-FOOTING and END-FOOTING commands:

begin-footing  2
   print  'Company Confidential'  (1)  center
end-footing

See Also

DECLARE-paragraph, BEGIN-section

#END-IF, #ENDIF

Syntax

#END-IF

Description

Ends an #IF, #IFDEF, or #IFNDEF command. (#END-IF is a compiler directive.)

#ENDIF (without the hyphen) is a synonym for #END-IF.

Example

The following example illustrates the #END-IF compiler directive:

#ifdef debuga
  show  'DebugA: #j = '  #j  edit  9999.99
  show  'Cust_num   = '  &cust_num
#end-if

See Also

The #IF, #IFDEF, and #IFNDEF commands for a description of each compiler directive.

END-IF

Syntax

END-IF

Ends an IF command.

See Also

The IF command for a description and example.

END-PROCEDURE, END-PROGRAM, END-SELECT, END-SETUP, END-SQL, END-WHILE

Syntax

END-PROCEDURE
END-PROGRAM
END-SELECT
END-SETUP
END-SQL
END-WHILE

Description

Completes the corresponding section or paragraph.

Each END- command completes the corresponding BEGIN- command:

BEGIN-PROCEDURE
BEGIN-PROGRAM
BEGIN-SELECT
BEGIN-SETUP
BEGIN-SQL
WHILE

Each command must begin on its own line.

Example

The following example illustrates the END-PROGRAM command:

begin-program
   do main
end-program

See Also

BEGIN-section, WHILE

EVALUATE

Syntax

EVALUATE {any_lit|_var|_col}

This command is equivalent to case/switch in C or Java. The general format of an EVALUATE command is:

EVALUATE {any_lit|_var|_col}
WHEN comparison_operator {any_lit|_var|_col}
SQR_Command...
[BREAK]
[WHEN comparison_operator {any_lit|_var|_col}
SQR_Command...
[BREAK]]
[WHEN-OTHER
SQR_Command...
[BREAK]]
END-EVALUATE

Description

Determines the value of a column, literal, or variable and takes action based on that value.

The EVALUATE command is useful for branching to different commands depending on the value of a specified variable or column.

EVALUATE commands can be nested.

Evaluating a date variable or column with a string results in a date comparison (chronological, not a byte-by-byte comparison as is done for strings). The string must be in the proper format as shown in the following list:

• For DATETIME columns and SQR DATE variables in the format specified by the SQR_DB_DATE_FORMAT setting, SQR uses one of the database-dependent formats (see the Default Database Formats table), or the database-independent format 'SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]'.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting, or the format listed in the DATE Column Formats table.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting, or the format as listed in the TIME Column Formats table.

Parameters

Example

The following example illustrates the EVALUATE command:

evaluate &code
  when = 'A'
     move 1 to #j
     break
  when = 'B'
  when = 'C'
     move 2 to #j     ! Will happen if &code is B or C.
     break
  when > 'D'
     move 3 to #j     ! Move 3 to #j and continue checking.
  when > 'H'
     add 1 to #j      ! Add 1 to #j and continue checking.
  when > 'W'
     add 2 to #j
     break
  when-other
     if isnull (&code)
         do null_code
      else
         move 0 to #j  ! Unknown code.
     end-if
     break
end-evaluate 

See Also

The commands IF and LET for comparison operators.

EXECUTE (Sybase and Microsoft SQL Server)

Syntax

EXECUTE [-XC][ON-ERROR=procedure[(arg1[,argi]...)]]
[DO=procedure[(arg1[,argi]...)]]
[@#status_var=]stored_procedure_name
[[@param=]{any_col|_var|_lit}[,...]]
[INTO any_coldata_type[(length_int_lit)]
[,...]][WITH RECOMPILE]

The syntax of this command generally follows that of the Sybase Transact-SQL EXECUTE command, with the exception of optional arguments and the INTO argument.

Description

Runs a stored procedure in Sybase or Microsoft SQL Server database.

If the stored procedure specified in stored_procedure_name contains a SELECT query, the EXECUTE command must specify an INTO argument to process the values from the query. If no INTO argument is specified, then the values from the query are ignored.

EXECUTE retrieves just the first row when the following conditions are met:

• The DO procedure is not specified.

• The stored procedure, stored_procedure_name, selects one or more rows.

• An INTO argument is specified.

This is useful for queries returning a single row.

Parameters

Example

The following code example invokes the stored procedure get_total with two parameters: a string literal and a string variable. The result from the stored procedure is stored in the variable #total.

execute get_total 'S. Q. Reporter' $State #Total Output

The following code example invokes the stored procedure get_products with two parameters. The stored procedure selects data into five column variables. The SQR procedure print_products is called for each row retrieved. The return status from the stored procedure is placed in the variable #proc_return_status.

execute do=print_products
   @#proc_return_status=
   get_products
   @prodcode=&code,            @max=#maximum
   INTO &prod_code         int,
      &description         char (45),
      &discount         float,
      &restock         char,
      &expire_date         datetime

begin-procedure print_products
print &prod_code            (+1,1)
print &description            (+5,45)
print &discount            (+5) edit 99.99
print &restock            (+5) match Y 0 5 Yes N 0 5 No
print &expire_date            (+5,) edit 'Month dd, yyyy'
end-procedure

EXIT-SELECT

Syntax

EXIT-SELECT

Description

Exits a SELECT paragraph immediately.

EXIT-SELECT causes SQR to jump to the command immediately following the END-SELECT command.

Use EXIT-SELECT when you need to end a query before all rows have been retrieved.

Example

The following example illustrates the EXIT-SELECT command:

begin-select
cust_num, co_name, contact, city, state, zip, employees
  add &employees to #tot_emps
  if #tot_emps >= 5000
    exit-select         ! Have reached required total emps.
  end-if
  do print_company
from customers order by employees desc
end-select

See Also

BEGIN-SELECT

EXTRACT

Syntax

EXTRACT {dst_txt_var|date_var} FROM
{{src_txt_lit|_var|_col}|{src_date_var|_col}}
{start_num_lit|_var}{length_num_lit|_var}

Description

Copies a portion of a string into a string variable.

You must specify the starting location of the string as an offset from the beginning of the string and its length. An offset of 0 (zero) begins at the leftmost character; an offset of 1 begins one character beyond that, and so on.

If the source is a date variable or column, it is converted to a string before the extraction according to the following rules:

• For DATETIME columns and SQR DATE variables, SQR specifies the SQR_DB_DATE_FORMAT setting.

  If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format listed in the DATE Column Formats table.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format as listed in the TIME Column Formats table.

If the destination is a date variable, the string extracted from the source must be in one of the following formats:

• The format specified by the SQR_DB_DATE_FORMAT setting.

• One of the database-dependent formats (see the Default Database Formats table).

• The database-independent format 'SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]'.

Parameters

Example

The following example illustrates the EXTRACT command:

extract  $state  from  $record  45  2
extract  $foo from "SQR Rocks" 0 4 ! $foo='SQR'

code  from  &phone  0  3
extract  $zip_four  from  &zip  5  4
extract  $rec  from  $tape_block  #loc  #rec_len

Note. Oracle recommends that you not use the EXTRACT command when processing strings.

See Also

The substr function described in the Miscellaneous Functions table under the LET command.

FIND

The PRINT command for information about default date and time formats

FIND

Syntax

FIND {{obj_txt_lit|_var|_col}|{date_var|_col}} IN
{{src_txt_var|_col}|{date_var|_col}}
{start_int_lit|_var} dst_location_int_var

Description

Determines the location of a character sequence within a string.

FIND searches the specified string for a character sequence and, if the string is found, returns its location as an offset from the beginning of the specified string. If the sequence is not found, FIND returns –1 in dst_location_int_var.

You must specify an offset from which to begin the search and supply a numeric variable for the return of the location.

If the source or search object is a date variable or column, it is converted to a string before the search according to the following rules:

• For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting.

  If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format listed in the DATE Column Formats table.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format as listed in the TIME Column Formats table.

Parameters

Example

The following example illustrates the FIND command:

find  'aw.2'  in  &code5  0  #loc
find  ','  in  &name  0  #comma_loc
if #comma_loc = -1
   ...comma not found...

See Also

The instr function described in the Miscellaneous Functions table under the LET command.

EXTRACT

The PRINT command for information about default date and time formats.

GET

Syntax

GET dst_any_var...FROM src_array_name(element)
[field[(occurs)]]...

Description

Retrieves data from an array and places it into a date, string, or numeric variable.

Parameters

Example

The following code example copies $name, $start_date, and #salary from the first three fields in the #j^th element of the emps array:

get $name $start_date #salary from emps(#j)

The following code example copies #city_tot and #county_tot from the fields cities and counties in the #j^th element of the states array:

get #city_tot #county_tot from states(#j) cities counties

The following code example copies $code from the #j^th occurrence of the code field in the #n^th element of the codes array:

get $code from codes(#n) code(#j)

See Also

The PUT command for information about moving data into an array.

GET-COLOR

Syntax

GET-COLOR
[PRINT-TEXT-FOREGROUND=({color_name_var |{rgb})]
[PRINT-TEXT-BACKGROUND=({color_name_var |{rgb})]

Description

Retrieves the current colors.

The GET-COLOR command is allowed wherever the PRINT command is allowed. If the requested color settings do not map to a defined name, the name is returned as RGBredgreenblue, where each component is a three-digit number—for example, RGB127133033. You can use this format wherever you use a color name. The color name 'none' is returned if no color is associated with the specified area.

Parameters

Example

The following example illustrates the GET-COLOR command:

begin-setup
   declare-color-map
   light_blue = (193, 222, 229)
   end-declare
end-setup

begin-program
   alter-color-map name = 'light_blue' value = (193, 233, 230)

   print 'Yellow Submarine' ()
      foreground = ('yellow')
      background = ('light_blue')

   get-color print-text-foreground = ($print-foreground)  
   set-color print-text-foreground = ('purple')
   print 'Barney' (+1,1)
   set-color print-text-foreground = ($print-foreground)
end-program

See Also

DECLARE-COLOR-MAP, ALTER-COLOR-MAP, SET-COLOR

GOTO

Syntax

GOTO  label

Description

Skips to the specified label.

Labels must end with a colon (:) and can appear anywhere within the same section or paragraph as the GOTO command.

Parameters

Example

The following example illustrates the GOTO command:

begin-select
price
 if &price < #old_price
    goto next
 end-if
 print &price (2,13,0) edit 999,999.99
   ...
next:
 add 1 to #count
from products
end-select

GRAPHIC BOX, GRAPHIC HORZ-LINE, GRAPHIC VERT-LINE

Syntax

The GRAPHIC commands have the following syntax:

GRAPHIC ({line_int_lit|_var},{column_int_lit|_var},
{width_int_lit|_var})

BOX {depth_int_lit|_var}
[rule_width_int_lit|_var[shading_int_lit|_var]]

GRAPHIC ({line_int_lit|_var},{column_int_lit|_var},
{length_int_lit|_var})

HORZ-LINE [rule_width_int_lit|_var]

GRAPHIC ({line_int_lit|_var},{column_int_lit|_var},
{length_int_lit|_var})

VERT-LINE [rule_width_int_lit|_var]

Description

Draws a box or line.

After GRAPHIC commands are carried out, SQR changes the current print location to the starting location of the graphic. This is different from the way the PRINT command works.

The GRAPHIC command has the following variations:

• BOX

• HORZ-LINE

• VERT-LINE

The following sections describe the individual GRAPHIC commands:

Parameters

Example

The following code example shows the GRAPHIC BOX command:

graphic (1,1,66) box 58 20     ! Draw box around page
graphic (30,25,10) box 10      ! Draw a 10-characters-wide-by-10- characters-long⇒
 box
graphic (1,1,66) box 5 0 8     ! Draw 5 line shaded box (without border)
graphic (50,8,30) box 1        ! Draw box around 1 line

The following code example shows the GRAPHIC HORZ-LINE command:

graphic (4,1,66) horz-line 10  ! Put line under page heading
graphic (+1,62,12) horz-line   ! Put line under final total

The following code example shows the GRAPHIC VERT-LINE command:

graphic (1,27,54) vert-line    ! Draw lines between columns
graphic (1,52,54) vert-line
graphic (3,+2,4) vert-line 6   ! Red line the paragraph

See Also

The ALTER-PRINTER and DECLARE-PRINTER commands for information about setting and changing the FONT, FONT-TYPE, POINT-SIZE, and PITCH.

#IF

Syntax

#IF {txt_lit|num_lit}comparison_operator
{txt_lit|num_lit}

Description

Indicates that the commands following #IF are to be compiled when the expression is TRUE. (#IF is a compiler directive.)

SQR has five compiler directives that enable different pieces of SQR code to be compiled, depending on the existence or value of substitution variables (not program variables, such as string, numeric, or date).

Substitution variables defined automatically for each -DEBUGxxx letter can also be used with the #IF, #IFDEF, and #IFNDEF directives. They can enable or disable entire sections of an SQR program from the command line, depending on the -DEBUGxxx flag.

You can nest #IF, #IFDEF, and #IFNDEF directives to a maximum of 10 levels.

The #IF, #IFDEF, and #IFNDEF directives cannot be broken across program lines.

The following table lists the compiler directives.

Parameters

Example

The following example illustrates the #IF compiler directive:

begin-setup 
  ask type 'Use Male, Female or Both (M,F,B)'
end-setup
begin-procedure Main
#if {type} = 'M' 
  ...code for M here
#else
#if {type} = 'F'
  ...code for F here
#else
#if {type} = 'B'  
  ...code for B here
#else
  show 'M, F or B not selected.   Report not created.' 
  stop
#endif   ! for B
#endif   ! for F
#endif   ! for M

#ifdef  debug 
  show 'DEBUG:  Cust_num = '  &cust_num  edit  099999
#endif

#ifndef debugB            ! DebugB turned on with -DEBUGB on 
  do test_procedure       ! SQR command line.
#endif

See Also

The #DEBUG command for information about the -DEBUG command-line flag.

IF

Syntax

IF logical_expression

IF commands have the following structure:

IF logical_expression
SQR Command...
[ELSE
SQR Command...]
END-IF

Description

Carries out commands depending on the value of a condition.

The expression is evaluated as a logical TRUE or FALSE. A value or expression that evaluates to nonzero is TRUE.

Each IF command must have a matching END-IF command.

IF commands can be nested.

Comparing a date variable or column with a string results in a date comparison (chronological, not a byte-by-byte comparison as is done for strings). The string must be in the proper format as described in the following list:

• For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting, one of the database-dependent formats (see the Default Database Formats table), or the database-independent format 'SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]'.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting, or the format listed in the Date Column Formats table.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting, or the format as listed in the Time Column Formats table.

Parameters

Example

The following example illustrates the IF command:

if &price > &old_price and instr(&code, 'M', 1) > 0
   add 1 to #price_count
   if #price_count > 50
      show 'More than 50 prices found.' noline
      input $x 'Continue? (Y/N)'
      if upper($x) = 'N'
         stop
      end-if
   end-if
else
   add 1 to #old_price_count
end-if
if #rows   ! Will be TRUE if #rows is non-zero.
   do print-it
end-if

if $date1 > 'Apr 21 2004 23:59'
   do past_due
end-if

See Also

The LET command for a description of logical expressions.

EVALUATE

#IFDEF

Syntax

#IFDEF substitution_variable

Description

Indicates that the following commands are to be compiled when the substitution variable has been declared by an ASK or #DEFINE command, or by the -DEBUG flag on the SQR command line. (#IFDEF is a compiler directive.)

Parameters

See Also

The #IF command for a description of each compiler directive.

#IFNDEF

Syntax

#IFNDEF substitution_variable

Description

Indicates that the following commands are to be compiled when the substitution variable has not been declared by an ASK or #DEFINE command, or by the -DEBUG flag on the SQR command line. (#IFNDEF is a compiler directive.)

Parameters

See Also

The #IF command for a description of each compiler directive.

#INCLUDE

Syntax

#INCLUDE filename_lit

Description

Includes an external source file in the SQR report specification.

You may want to keep commonly used routines in a single file and reference or include that file in programs that use the routine. For example, you might have a set of #DEFINE commands for different printers to control initialization, font changes, and page size declarations. You can reference the appropriate include file depending on which printer you want to use.

Include files can be nested up to four levels. Variable substitution scanning takes place before the #INCLUDE command is processed. This enables you to substitute all or part of the include file name at runtime, adding flexibility for controlling which file is included for the run.

Parameters

Example

The following example illustrates the #INCLUDE command:

#include 'gethours.dat'        ! Common procedure.
#include 'XYZheader.dat'       ! Common report heading for XYZ Company.
#include 'printer{num}.dat'    ! Include printer definitions for 
                               ! printer {num}, which is passed
                               ! on the command line:
                               ! SQR REP1A SAM/JOE 18
                               ! where 18 is the arbitrary number 
                               ! assigned your printer
                               ! definition file, 'printer18.dat'.
                               ! The report would contain the
                               ! command:  ASK num
                               ! in the SETUP section, preceding
                               ! this #include statement.

INPUT

Syntax

INPUT input_var[MAXLEN=nn][prompt]
[TYPE={CHAR|TEXT|NUMBER|INTEGER|DATE}]
[STATUS=num_var][NOPROMPT][BATCH-MODE]
[FORMAT={txt_lit|_var|_col}]

Description

Accepts data entered by the user at a terminal.

Use MAXLEN to prevent the user from entering data that is too long. If an INSERT or UPDATE command references a variable for which the length is greater than the length defined in the database, the SQL is rejected and SQR halts. If the maximum length is exceeded, the terminal beeps (on some systems, this may cause the screen to flash instead).

If prompt is omitted, SQR uses the default prompt. Enter [$|#]var: . In either case, a colon (:) and two spaces are added to the prompt.

Specifying TYPE causes data type checking to occur. If the string entered is not the type specified, the terminal beeps and an error message is displayed. The INPUT command is then rerun. If TYPE=DATE is specified, then input_var can be a date or text variable; however, TYPE=DATE is optional if input_var is a date variable. If a numeric variable is used, it is validated as a numeric variable. The types CHAR, TEXT, and DATE are invalid types. The data types supported are described in the following table:

Specifying STATUS causes the INPUT command to finish regardless of what the user enters. No error message is displayed. A nonzero error code is stored in the indicated numeric variable if the length or datatype entered is incorrect.

The following table lists the values of the STATUS argument of the INPUT command:

By using NOPROMPT and STATUS with the SHOW command, you can write a sophisticated data entry routine.

FORMAT can be used only with dates. It can be a date edit mask or the keyword DATE. Use the keyword DATE if the date must be in the format as specified with the INPUT-DATE-EDIT-MASK setting for the current locale. If FORMAT has not been set, use a database-independent format for the data as listed in the datatypes table.

Parameters

Example

The following example shows several INPUT commands:

input  $state  maxlen=2  'Please enter state abbreviation'
input  #age  'Enter lower age boundary'  type=integer
input  $start_date  'Enter starting date for report'  type=date
input  $date_in  format='Mon dd yyyy'
input  $date  format=date

The following example shows another INPUT command:

show clear-screen (5,32) reverse 'CUSTOMER SUMMARY' normal
Try_again:
show  (12,20)  'Enter Start Date: '  clear-line
input  $start-date  noprompt  status=#istat  type=date
if  #istat  !=  0
     show (24,1) 'Please enter date in format DD-MON-YY' beep
     goto  try_again
end-if
show  (24,1)  clear-line            ! Clear error message line.

The following example illustrates the use of the BATCH-MODE option:

begin-program
 while (1)
  input $A status=#stat batch-mode
  if #stat = 3
   break
  else
   do procedure ($a)
  end-if
 end-while
end-program

See Also

ALTER-LOCALE

The INPUT-DATE-EDIT-MASK setting in the chapter “Using the PSSQR.INI File and the PSSQR Command Line.”

LAST-PAGE

Syntax

LAST-PAGE position [pre_txt_lit[post_txt_lit]]

Description

Places the last page number on each page, as in page n of m.

The text strings specified in pre_txt_lit and post_txt_lit are printed immediately before and after the number.

Using LAST-PAGE causes SQR and SQRT to delay printing until the last page has been processed so that the number of the last page is known.

Parameters

Example

The following example illustrates the LAST-PAGE command:

begin-footing 1
   page-number         (1,37)  'Page '    ! Will appear as
   last-page            ()  ' of ' '.'    ! "Page 12 of 25."
end-footing

See Also

PAGE-NUMBER, BEGIN-HEADING, BEGIN-FOOTING

LET

Syntax

LET dst_var=expression

Description

Assigns the value of an expression to a string, numeric, or date variable.

Valid expressions are formed as a combination of operands, operators, and functions. String, numeric, date, and array field operands can be used in an expression and embedded functions. SQR supports a standardized set of mathematical operators and logical comparison operators working within a carefully defined set of precedence rules. SQR also provides a rich set of mathematical, string, date, and file manipulation functions along with a number of special purpose utility functions. All combined, the SQR expression provides a powerful tool that can be tailored to suit any information processing need. The following detail outlines the specific behavior of each expression component: (1) the operand, (2) the operator, and (3) the function.

Parameters

Operands

Operands form the backbone of an SQR expression. Operands do not have to be the same type. You can combine string, numeric, and array field operands to form a valid expression. SQR performs a sequence of automatic operand conversions when it evaluates expressions that contain dissimilar operand types. As the expression is evaluated, operands of lower precision are converted to match the operand of higher precision. Consider the following code example:

let  #answer = #float * #decimal  / #integer

Because the multiply and divide operators are equal in precedence, the expression is evaluated as (#float * #decimal) / #integer. Working from the inside out, the #float variable is converted to a decimal type in which a multiply is performed yielding the simplified expression (#decimal)/#integer. SQR now converts the #integer operand to a decimal type before performing the final divide. When finished with the expression evaluation, SQR converts the result to match the type of the #answer variable.

Converting operands of lower precision to operands of higher precision preserves the number of significant digits. The number of significant digits is not lost when an integer is converted to float or decimal. In a similar manner, the number of significant digits is preserved when floating point operands are converted to the decimal type. The number of significant digits is sacrificed only when the final result is converted to match the type of the #answer variable and this variable is less precise than the highest of the operands being evaluated. In the example, precision is not lost if the #answer is declared as a decimal type. SQR considers integer variables as the lowest in the precision hierarchy, followed by float and then decimal.

Here are a few simple expression examples:

let #discount  =  round (&price * #rate / 100, 2)
let $name  =  $first_name || ' ' || $last_name
let customer.total (#customer_id) =

   customer.total (#customer_id) + #invoice_total
if not range(upper($code), 'A', 'G')
   ...processing when out of range...
let store.total (#store_id, #qtr) =

   store.total (#store_id, #qtr) + #invoice_total
let $date1 = strtodate ('Apr 10 2004', 'MON DD YYYY')

The following sections list operators and functions supported in expressions.

Operators

Operators of the same precedence are processed in the sequence in which they appear in the expression, from left to right. Use parentheses to override the normal precedence rules. All numeric types (decimal, float, integer) are supported for all operators.

This table lists operators in descending order of precedence (operators listed in the same row within the table have the same precedence):

Functions

This section lists numeric, file-related, and miscellaneous functions. The functions are listed in alphabetical order.

Function arguments are enclosed in parentheses and can be nested. Arguments referenced as x, y, or z indicate the first, second, or third argument of a function. Otherwise, functions take a single argument or no arguments. All arguments are evaluated before a function is evaluated.

Not all functions support all numeric types (decimal, float, integer). Certain functions do not support the decimal type directly, but convert input decimal operands to the float type before the function is evaluated. The following table annotates the functions that directly support the decimal type and the ones that do not.

Use parentheses to override the normal precedence rules.

This table describes numeric functions:

abs

dst_var ​= abs(num_value) 

let #dabsvar = abs(#dvar) 

acos

dst_var = acos(num_value) 

let #facosvar = acos(#fvar) 

asin

dst_var = asin(num_value) 

let #fasinvar = asin(#fvar) 

atan

dst_var = atan(num_value) 

let #fatanvar = atan(#fvar) 

ceil

dst_var = ceil(num_value) 

let #fceilvar = ceil(#fvar)

cos

dst_var = cos(num_value) 

let #fcosvar = cos(#fvar) 

cosh

dst_var = cosh(num_value) 

let #fcoshvar = cosh(#fvar) 

deg

dst_var = deg(num_value) 

let #fdegvar = deg(#fvar) 

e10

dst_var = e10(num_value) 

let #fe10var = e10(#fvar) 

exp

dst_var = exp(num_value) 

let #fexpvar = exp(#fvar) 

floor

dst_var = floor(num_value) 

let #ffloorvar = floor(#fvar) 

log

dst_var = log(num_value) 

let #flogvar = log(#fvar) 

log10

dst_var = log10(num_value) 

let #flog10var = log10(#fvar) 

mod

dst_var = mod(x_value, ​y_value) 

let #fmodvar = mod(#fxvar, #fyvar)

power

dst_var = power(x_value, ​y_value) 

let #fpowervar = power(#fxvar, #fyvar)

rad

dst_var = rad(num_value) 

let #fradvar = rad(#fvar) 

round

dst_var = round(num_value, ​place_value⇒
)

let #frndvar = round(#fvar, #fplace) ⇒
(#x, #y)

sign

dst_var = sign(num_value) 

let #fsignvar = sign(#fvar)

sin

dst_var = sin(num_value) 

let #fsinvar = sin(#fvar) 

sinh

dst_var = sinh(num_value) 

let #fsinhvar = sinh(#fvar) 

sqrt

dst_var = sqrt(num_value) 

let #fsqrtvar = sqrt(#fvar) 

tan

dst_var = tan(num_value) 

let #ftanvar = tan(#fvar) 

tanh

dst_var = tanh(num_value)

let #ftanhvar = tanh(#fvar) 

trunc

dst_var = trunc(num_value, ​place_value⇒
)

let #ftruncvar = trunc(#fvar,⇒
 #fplace) 

The transcendental functions sin, cos, tan, sinh, cosh, and tanh take their arguments in radians. The functions asin, acos, and atan return radian values. To convert from radians to degrees or degrees to radians, use the rad or deg functions as shown here:

let #x = sin(rad(45))            ! Sine of 45 degrees.
let #y = deg(asin(#x))           ! Convert back to degrees.

If arguments or intermediate results passed to a numeric function are invalid for that function, SQR halts with an error message.

For example, passing a negative number to the sqrt function causes an error. Use the cond function described in the Miscellaneous Functions table to prevent division by zero or other invalid function or operator argument values.

The following table lists file-related functions. These functions return 0 (zero) when successful; otherwise, they return the system error code.

stat_var ​= delete(filename) 

let #fstatus = delete($filename)

stat_var ​= exists(filename)

let #fstatus = exists($filename)

stat_var = rename(old_filename, ​new_filename) 

let #fstatus = rename($old_filename, $new_⇒
filename)

The following table lists miscellaneous functions. These functions return a string value unless otherwise indicated.

In these functions where a string argument is expected and a date variable, column, or expression is entered, SQR converts the date to a string according to the following rules:

• For DATETIME columns and SQR DATE variables, SQR uses the format specified by the SQR_DB_DATE_FORMAT setting.

  If this has not been set, SQR uses the first database-dependent format as listed in the Default Database Formats table.

• For DATE columns, SQR uses the format specified by the SQR_DB_DATE_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format listed in the DATE Column Formats table.

• For TIME columns, SQR uses the format specified by the SQR_DB_TIME_ONLY_FORMAT setting.

  If this has not been set, SQR uses the format as listed in the TIME Column Formats table.

Except where noted in an individual function, if a string variable, column, or expression is entered where a date argument is expected, the string must be in the format specified by the SQR_DB_DATE_FORMAT setting, one of the database-dependent formats listed in the Default Database Formats table, or the database-independent format 'SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]'.

array_var = array(array_name, ​field_name)

let #fstatus = printarray(array('products',⇒
 'name'), 10, 2, 'c')

ascii_var  = ascii(str_value)

let #fascii = ascii($filename)

ascii_var  = asciic(str_value)

let #fascii = asciic($filename)

dst_var = chr(num_value)

let $svar = chr(#num)

dst_var = cond(x_value, ​y_value, ​z_value)

let #avg = #total / cond(&rate != 0, &rate, 1)

dst_var = dateadd(date_value, ​units_value, ​⇒
quantity_value)

let $date = dateadd($startdate, 'day', 7.5)

dst_var = datediff(date1_value, ​date2_value, ​⇒
units_value)

let #diff = datediff($date1, $date2, 'hour')

dst_var = datenow()

let $date = datenow()

dst_var = datetostr(date_value [, ​format_mask])

dst_var = edit(source_value, ​edit_mask)

let $phone  =  edit(&phone, '(xxx) xxx-xxxxx')⇒
 let $price = edit(#price, '999.99')⇒
 let $today = edit($date, 'DD/MM/YYYY'

dst_var = getenv(env_value)

let $myuser = getenv('USER')

dst_var = instr(source_value, ​sub_value, ​offset_⇒
value)

let #offset = instr(&description, 'auto', 10)

dst_var = instrb(source_value, ​sub_value, ​offset_⇒
value)

let #offset = instrb(&description, 'auto', 10)

dst_var = isblank(source_value)

let #blank = isblank(&description)

dst_var =  isnull(source_value)

let #null = isnull($date)

dst_var = length(source_value)

let #length = length(&description)

dst_var = lengthb(source_value)

let #length = lengthb(&description)

dst_var = lengthp(source_value)

let #printlen = lengthp(&string)

dst_var = lengtht(source_value, ​encoding_value)

let #sjislen = lengtht($string, 'Shift-JIS')

dst_var = lower(source_value)

let $lower = lower(&description)

dst_var = lpad(source_value, ​length_value, ​pad_⇒
value)

let $lpad = lpad($notice, 25, '.')

dst_var = ltrim(source_value, ​set_value)

let $ltrim = ltrim(&description, '.')

dst_var = nvl(x_value, ​y_value)

let $city = nvl(&city, '-- not city --')

dst_var = range(x_value, ​y_value, ​z_value)

let #inrange = range(&grade, 'A', 'D')⇒
 let #inrange = range($date, $startdate,⇒
 $enddate)⇒
 let #inrange = range($date, $startdate, '15-Apr-⇒
04')⇒
 let #inrange = range(#price, #low, #high)

dst_var = replace(source_value, ​from_string, ​to_⇒
string)

Example:
let $replaced = replace($paragraph, 'good',⇒
 'excellent')

dst_var = roman(source_value)

let $roman = roman(#page-count)

dst_var = rpad(source_value, ​length_value, ​pad_⇒
value)

let $rpad = rpad($notice, 25, '.')

dst_var = rtrim(source_value, ​set_value)

let $rtrim = rtrim(&description, '.')

dst_var = strtodate(source_value [, ​format_mask])

dst_var = substr(source_value, ​offset_value, ​⇒
length_value)

let $piece = substr(&record, 10, #len)

dst_var = substrb(source_value, ​offset_value, ​⇒
length_value)

let $piece = substrb(&record, 10, #len)

dst_var = substrp(source_value, ​offset_value, ​⇒
length_value)

let $sub = substrp(&string, #printpos, #printlen)

dst_var = substrt(source_value, ​offset_value, ​⇒
length_value, ​encoding_value)

let $sjisPrep = substrt(&string, 1, 10, 'Shift-⇒
JIS')

dst_var = to_char(source_value)

let $string = to_char(#number)

dst_var = to_multi_byte ​(source_value)

let $multi = to_multi_byte (&text)

dst_var = to_number(source_value)

let #value = to_number($number)

dst_var = to_single_byte(source_value)

let $single = to_single_byte (&text)

dst_var =  translate(source_value, ​from_set, ​to_⇒
set)

let $translated = translate(edit(&price,⇒
 '999,999.99'),',',',')

dst_var = transform(source_value, transform_value)

let $hiragana = transform($string, 'ToHiragana')

dst_var = unicode(source_value)

let $uniStr = unicode('u+5e73 u+2294')

dst_var =  upper(source_value)

let $upper = upper(&description)

dst_var = wrapdepth(source_value, ​wrap_width, ​⇒
line_height, ​on, ​strip)

let #depth = wrapdepth(&description,40,1,'<13>','')

Writing Custom Functions

In addition to using the preceding built-in functions, you can write your own functions in C, using the supplied source file UFUNC.C .

You can pass any number of arguments to your function and values can be returned by the function or passed back in variables.

After editing and recompiling UFUNC.C, you must relink SQR.

The following step-by-step example shows how to add a user-defined function to SQR so that it can be invoked using the LET, IF, or WHILE command.

The example adds the C function random, which returns a random number. The function accepts a parameter that is used as the seed to start a new sequence of numbers. If the seed is zero, then the same sequence is used.

When adding functions to UFUNC, remember the following considerations:

• String functions require the following arguments:

  • (int) Number of arguments.

  • (char *) or (double *) Array of argument pointers, to either char[ ] or double.

  • (char *) Address for result string. If unchanged, function returns a NULL string.

  • (int) Maximum length of result string, in bytes.

• Numeric functions require the following arguments:

  • (int) Number of arguments.

  • (char *) or (double *) Array of argument pointers, to either char[ ] or double.

  • (double *) Address for result numeric value. If unchanged, function returns zero.

To add the random function to SQR, add the following modifications to the UFUNC.C file that was provided with SQR:

• Add the prototype for the random function: static void random CC_ARGS((char *, char *));

• Add the function name to the declaration list. The name of the function called from SQR is random. The return type is n for numeric. The number of arguments passed is 1, and the argument type is n for numeric. The function name in UFUNC.C is random. The characters PVR must be entered before the function name.