Docstoc

SQR

Document Sample
SQR Powered By Docstoc
					                                             SQR Notes


A. General Reference

                            SQR – (as of Version 6)

                            SQR is a weakly typed language. Variables can be created when
                            first referenced. Many bugs can be attributed to mistyping
                            variable names unless Declare-Variable is used
Performance                      1. SQR has a cursor pool that handles up to 30 cursors. A
                                     large program with more than 30 sql statements can cause
                                     a rotation of sql to cursor handles. Normally static sql may
                                     run like dynamic being re-prepared with each sql execution
                                     causing a performance hit.
                                 2. Consider the load-lookup option (see below) for small result
                                     sets.


Variable definition         &… database column or expression name. Can be any type (char,
                            number, date)
                            Declared automatically for columns defined in query (except for
                            dynamic columns and database or aggregate functions)

                            To reference a database variable, preface it with the table alias if
                            given, eg.
                            Begin-select
                             col1
                             $x = &t.col1
                             From mytable t
                            End-select
                            $y = &t.col1


                            $... text or date variable
                            #... numeric variable – floating pint, decimal or integer
                            %... list variable
                            @... variable name for marker location (used to identify positions to
                            begin for printing inside a BEGIN-DOCUMENT paragraph

                            Use Declare-Variable command to explicitly set declare variables of
                            the type you want (strong typing of variable). Declare-Variable can
                            only be used in a) Setup section or b) first statement of a local
                            procedure
                            Begin-Setup
                              Declare-Variable
                                  Decimal(nn) #var1 #var2 (specify nn for number of decimals)
                                  Float #var3 #var4
                                  Integer #var5 #var 6
                                  Text $var7
                                  Date $var8
                              End-Declare
                            End-Setup

                            Note a Date variable defined in Declare-Variable is a ‘real’ date as
                            opposed to defining a variable as $var_date (which is really a string)




D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                      Page 1 of 17
                                             SQR Notes

Variable scope              Variables are either global or local (local to procedure)
Literals                    Text or numeric constants
                            Text literal surrounded by single quotes
                            0-9 begin any numeric literals, eg -543.21 or 1.2E5
Variable naming             Can be almost any name of almost any length (except for reserved
                            words)

                            Do not use ‘_ ‘ or ‘:’ as first char

                            Not case sensitive

                            SQL initializes vars to null (text and date) or zero (numeric)

                            Numeric variables are 1 of 3 types – FLOAT, INTEGER ro DECIMAL
                            (See DECLARE-VARIABLE)

                            Variables and columns are known globally throughout report, except
                            if used in local procedure (one with args declared with LOCAL
                            argument – See BEGIN-PROCEDURE)
Arrays                      Arrays are 0 based indexed
                            create-array name=myarray size=500         !(500number rows)
                               field=colname1:char=’init value’
                               field=colname2:number:2=1.0 !(number:2 indicates 2 occurances
                                                     in a row)

                            Assigning value
                            Put #x into myarray(1) colname2(1)
                            Or
                            Put $y #x1 #x2 into myarray(3) !puts values into array in order of
                                                    fields in array

                            Get a value
                            Get $y from myarray(2) colname

                            Create-Array
                            Clear-Array -reinitializes array)
                            Get, Put - can reference multiple variables in on statement
                            Get #city_tot #count_tot from states(#j) cities counties
                            Put $company $name into employees(#j)

                            Let - reference 1 variable/ 1 array position at a time

                            Array-Add, Array-Subtract, Array-Multiply, Array-Divide

                            SQR did not give an error, but also did not give correct results with :
                            put &np_racf_logon into racf_array(#racf_id_ix -1 ) racf_id

                            It worked ok with:
                            let #racf_id_ix = #number_racf_ids - 1
                            put &np_racf_logon into racf_array(#racf_id_ix ) racf_id

Comment                     !
                            To us in as text, type it twice - !!
                            (Same for ‘ –single quote – ‘’ to print 1 quote)
Continuation                  - (hyphen) at end of line - doesn’t seem to work. What’s the
                                catch? Just for continuation of literals?


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                       Page 2 of 17
                                             SQR Notes


                            No longer needed?


Concatentation              Let $where = ‘zyc’ || ‘abc’
List variables              Contain ordered list of SQR variables (can not nest lists within lists)

                            Create list
                            LET %LIST1 = LIST(num1|string1, num2|string2,…)


Displaying values for       If you get an error make sure any variables in show/display are
trace                       correctly specified (eg. With &, $, table synonym qualifier, etc.)


                            Display var1:edit mask ! display 1 var with edit
                            Show ‘var1:’ $var1 ‘var2:’ $var2 ! display multiple text strings
                                                            ! and/or variables

                            Show $var1 #var2 edit 9,999.99

Program Structure           5 sections
                               - Program
                               - Setup
                               - Heading
                               - Footing
                               - Procedure



                            #include …
                            #.... …
                            begin-setup         Optional section but executes first if present,
                            ..                  Must be placed at beginning of program
                            end-setup

                            Begin-program        Only required section - executes after begin-setup
                             do …
                             do …
                            end-program

                            begin-procedure
                              …
                              begin-select
                                …
                              end-select
                            end-procedure

                            Begin-heading        Processed just be report page written to output,
                             …                   after page body completed or before New-Page
                            End-heading           issued

                            Begin-footing
                              …
                            End-footing
                            #include …


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                       Page 3 of 17
                                              SQR Notes


                            Begin-report        Use may be discontinued and should be replaced
                             …                  with begin-program
                            End-report

Default output files        Output file has same name as program with .lis extension
                            (Print …. output goes to the .lis file)
                            Output file put to same directory as your program
                            Log file has same name as program with .log extension (Show …
                            command output shows up in the .log file)
Command line/               Sqrw mysqr.sqr uid/pswrd runtimeflags arguments
Runtime flags
                            Sqrw ..        Compile/execute (use ‘\’ in directories)
                            Sqr …          Compile/execute on UNIX (use ‘/’ in directories)

                            Runtime flags

                            -DEBUGxxxxx
                             x can be any letter or digit (up to 10 allowed) . Causes
                            compile/execution of debug code #debug and #debugx (where x is
                            one of letters in list)

                            -f     directory where sql will palce the report output file
                                 eg. –fc:\windows\temp

                            -o define name of sql log file
                              eg. –oc:\temp\sqr.log

                            -S show program sql and nunber of times compiled(if dynamic) and
                            executed, rows returned
                                  COMPILES = 1
                                  EXECUTES = 1
                                  ROWS = 36254
                                  CURSOR #3:
                                   SQL = SELECT …

                            -Tnn generate only nn pages or program output report (all order by
                            clauses in select statements ignored and only first report – if more
                            than 1, is produced)
                            -E[file] to direct all error messages to a file

                            -RS            compile program only (compiled file given .sqt extension)
                            -RT            execute compiled .sqt version

                            sqrw mysqr.sqr uid/pswd –RS … (compile)
                            sqrw mysqr.sqt uid/pswd -RT … (execute .sqt file)
                            sqrt mysqr.sqt uid/pswd …     (execute using sqrt program)

                            Arguments
                            Arg1 arg2          list arguments in order of Ask then Input
                            @argfile.txt       arguments, 1 per line, in text file, first 2 lines should
                                               userid/password and db connectivity string


                            Z/OS example command line
                            DSN SYSTEM(DB2A)



D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                            Page 4 of 17
                                             SQR Notes

                            RUN PROG(SQR) PLAN(SQR815T)
                            LIB('SYS3.SQR.V821.SQR.UNICODE.LOAD') PARMS('/ SP
                            DSN/PS80DEV -FSQROUT -GPRINT=YES -P -PRINTER:LP
                            -XLFF -ISI( -TBZ')

                            Running via UltraEdit
                               1. Have drive mapped to sqrw.exe (may also be
                                   sqrwt,sqrwp,sqrwv)
                               2. ? is program name, %f tells UltraEdit to insert program
                                   name
                               3. database/userid
                               4. –i parm list of directories that SQR searchs for include files,
                                   ending each with a directory character. Separate directories
                                   with ‘,’ or ‘;’. For Unix using semicolon, precede each
                                   semicolon with ‘\’ escape.
                               5. –o parm log message file
                               6. –zif parm full path and name of SQR initialization file sqr.ini
                               7. –f parm overrides directory/file (if specified) for output file
                                   name program.lis
                               8. –printer parm printer type xx to be used when creating
                                   output printer files.
                               9. –s display status of all cursors at end of the report run
                                   (number times sql compiled, execute, total number rows
                                   selected)

                                 UltraEdit command string follows

                            m:\ps80prd\bin\sqrps\sqr\db2\BINW\sqrw.exe ?%f
                            PS80PRD/$FOERTS -iC:\QUALSQR\sqr\ -oC:\TEMP\sqr.log -
                            zifw:\PS80dev\sqr\pssqr.ini -fc:\temp\ -printer:hp -s

Issue Operation             Call System Using $command #status ! $command holds the
system commands                                        !system command or program to
                                                       ! execute

                            Can hard code command string if you want

                            Call System using $command #status Wait|Nowait

                            For synchronous or async mode.

Arithmetic commands         Add #var1 to #var2
                            Subtract #var1 from #var2
                            Multiply #var1 times #var2 round n (round to n decimals)
                            Divide #var1 into #var2 round n On-Error=zero (optional – can be
                            high value, zero or halt processing – the default)

Move                        Move can do data conversions from one data type to another, and
                            data editing if edit mask provided.

                            Move &phone to &display_phone (xxxx)bxxx-xxxx
                            Move #average to $display_average $9,999.99
                            Move ‘Month DD, YYYY’ to date_mask
                            Move &effdt to $Effective_dt :$date_mask (when mask in variable
                            the variable must first start with ‘:’)
                            Move &counter to #number_of_emps Number


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                     Page 5 of 17
                                             SQR Notes

                            Move &Annual_Rate to #Annual_rate Money
                            Move $Hire_Date to $Start_date Date
String commands             Find ‘John’ in $full_name 0 #position (-1 into #position if not found)

                            Extract $area_code from $phone_number 1 3

                            Encode ‘<27>KL11233’ to $Bold

                            Concat $Full_zip with $address (zip appended to address)

                            Let &average = ‘average = ‘
                            Concat #average to &average

                            String $emplid $emplname $birth_dt by ‘,’ into $empl_record

                            Unstring $empl_name by ‘-‘ $first_name $mid_initial $last_name

Let command                 Let target_variable = expression (expression can be operands,
                            operators and functions)
Functions                   See documentation
Reporting                   The heading and footing of a report is generated after the
                            body(detail) of the report is created. All output lines accumulated
                            first in memory and when page complete then page written out.
Procedures                  Can be global or local

                            2 ways to make procedure local
                            Begin-Procedure List_employees Local       (local keyword makes
                            local)
                            …
                            End-procedure

                            Begin-Procedure List_Employess($Company) (arguments make
                            local)
                            …
                            End-procedure


                            Local procedures
                               1. Any query results are local to procedure
                               2. To reference a global variable in the procedure add and
                                    underscore after its special character, eg #_count, $_street
                               3. All SQ reserved words are global and underscores are
                                    needed ($_sql-error)
                               4. If a local procedure is called recursively only 1 copy of its
                                    local variables are maintained.

SQL select                  Begin-select loops=n !if loops specified, read that many rows only
                            Emplid      !column names must start on col 1 with no comma
                            Name, city ! or multiple columns can be listed separated by comma
                              Do xyz    ! any procedures must be indented
                            From ….
                            Where …
                            End-select

                            All request columns are assigned first, then any procedures are
                            called.


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                      Page 6 of 17
                                             SQR Notes


                            Assigning a value to a variable
                            Begin-select
                              avg(*) &ee_avg
                              Print ‘employee avg = ‘ (1,1)
                              Print &ee_avg (, +1)
                            From …
                            End-select


                            Implicit printing can be done by placing position parm immediately
                            after the column name
                            Begin-select
                             Emplname (+1,1)
                            From …
                            End-select

                            Select * is not allowed in SQR

Native SQL                  SQL other than selects can be coded in begin-sql
                            Begin-procedure
                            Begin-SQL ON-Error=error-proc       !if error execute error-proc
                            Update employees
                             Set ….
                            Where ….;        !separate multiple sqls with ‘;’
                            Delete from …
                            End-sql
                            End-procedure

                            Begin-SQL can also appear in Begin-Setup section

                            Begin-Setup
                             Begin-SQL On-Error=Warn ! In setup can use Warn, Stop,Skip
                             ….
                             End-SQL
                            End-Setup

                            Dynamic sql
                            If all or part of SQL based on a text string, reference the SQL :
                            $where_phrase = ‘…’
                            Begin-select
                            Col1,
                            Col2,
                            From
                               Some_table
                            #IFDEF MVS
                               \$where_phrase\
                            #ESLE
                               [$where_phrase]
                            #ENDIF

                            Warn causes SQR to display warning message
                            Stop causes SQR to stop program
                            Skeip causes SQR to skip errors and continue running the program
SQL Commit                  Sybase/SQL Server use
                              Begin Transaction


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                  Page 7 of 17
                                             SQR Notes

                              Commit Transaction

                            Informix
                            BEGIN WORK
                            END WORK

                            Oracle (/DB2?)
                             Commit - (may close all open cursors)

                            A commit is done by SQL when program finishes w/o errors
Load-Lookup                 Creates lookup array with 2 fields, - Key and Return_Value
                            If placed in Setup section table loaded only once, if placed in
                            procedure executed whenever procedure called.

                            Begin-Setup
                            Load-Lookup Name=Employee_names
                                Rows=1000 !default is 100 if omitted
                                Table=Employees !table name
                                Key=Emplid
                                Return_value=Empl_name
                             Where=division=’XYZ’ !selection criteria, can contain subselect)
                            End-Setup          ! see manual for other load-lookup options

                            Begin-Program
                             …
                             Lookup Employee_names &emplid $empl_name
                            ….
                            End-program

                            If load-lookup is used in a procedure, the where phrase can be
                            dynamically created.

If                          If ….
                            Else
                            End-If

Evaluate                    Evaluate &var
                             When = ‘T’
                               …
                               Break !exit evaluate – don’t do subsequent ‘when’ statements
                             When ‘L’
                               ….
                               Break
                              When-Other
                               ….
                               Break
                            End-Evaluate

Loops                       While ( condition true)
                              …               ! coding a break will cause an exit from the loop
                            End-while
Print command               Print …. (x,y,x) ! x – line position on page, y – column number
                                             ! z – number of positions to print


                            A ‘+’ or ‘-‘ in front of number makes it relative to number used in prior


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                         Page 8 of 17
                                             SQR Notes

                            print command.
                            If no ‘+’ or ‘-‘ used the position is absolute on the page

                            Substitition variables can also be used
                            Begin-Procedure Init
                            #Define emplid-pix 1
                            #define emplname-pix 10
                            End-procedure

                            Begin-Select
                            Emplid (+1,{emplid-pix})
                            Emplname (, {emplname-pix})
                            From…
                            End-select

                            Formatting command follow the placement
                            Print $emplname (x,y,z) Underline Center …

                            Fill the line
                            Print ‘-‘ (1,1,100) Fill line with 100 hyphens

                            Edit masks - see manual for all edit masks
                            Print $zip (1,1) edit xxxxx-xxxx
                            Print #price (1,+4) edit 9,999.99

                            Positioning
                            Postion (5,3) ! position to line 5, col 3
                            Print #price () ! print price at current position

                            Skipping lines between detail
                            Begin-procedure xyz
                            Begin-sql
                            Emplid (1,1,10)
                            Name (2,1,20)
                            Next-Listing Skiplines=1 Need = 2 !skip 1 line, need 2 lines for
                                                               ! next group (or do new-page)
                            From …
                            End-sql
                            End-procedure

                            Horizontal Spacing
                            Columns 5 10 20 ! define 3 columns
                            Print $emplid (1,1,10) !print emplid in first logical column
                            Next-Column
                                                                               nd
                            Print $emplname(+1,2,20) !print emplname in 2 logical column
                            Use-Column 3
                                                                           rd
                            Print $address (+1,2,30) !print address in 3 column
                            Use-Column 0 ! stop printing by logical column, can turn back on
                                              ! by using Use-column n.

                            Control breaks
                            Begin-select
                            &company (…) On-Break Level = 1 Before=Company_name
                                           After=Company_totals Skiplines = 1
                                           Save=$prev_company
                            &emplid ( …) On-Break Level = 2 before=Empl_name



D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                Page 9 of 17
                                             SQR Notes

                                          After=Empl_summary save=$prev_emplid
                            &emplname (…)
                            &eff_date (….)
                            &salary (…)
                            End-select

                            More options exist. Check manual

Variables in SQR            Bind variables – can change value between invocations of select
reports                     Begin-select
                            EmplName
                            From Personal_data
                            Where emplid = &emplid    !bind variable
                            End-select

                            Substitution Variables – compile time variables (set at compile
                            time)
                            #Define col_emplid 12             ! define substitution var
                            #Define col_empl_name 30
                            …
                            Print ‘Emplid’ (0,1,{col_emplid}) ! using substitution var

                            Oftentimes substitution variables are defined in external source
                            (include file)
                            #Include ‘stdcolumns’ ! include files can be nested

                            Dynamic query variables – used to build dynamic SQL code
                            When used in sql, the variables must be enclosed in
                            [&where] square brackets - Unix?
                            \&where\   backslashes (MVS/AS/400)

User input                  Begin-setup
                             Ask emplid ‘Enter employee id’ !Ask can only be used in setup
                                                            ! section
                            Input #deduction_year maxlen=4 'Enter year for
                            which deductions are tobe applied' type=integer
                            status=#input_status format=yyyy
                            if #input_status <> 0
                              Show ' #deduction_year must be numeric and
                            between 2006 and 2020
                              #return-status = 16
                              stop
                            end-if


                            End-setup
                            ….
                            Begin-select
                            Emplname
                            From personal_data
                            Where emplid = {emplid}
                            End-select

                            Ask values are sequentially looked for on 1) command line, 2)
                            argument file, 3) user prompt
                            Ask values are assigned at compile time and can not be changed



D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                Page 10 of 17
                                             SQR Notes

                            during runtime

                            Begin-Program
                             Input $eff_date ‘Enter effective date’ type=date
                             …
                            End-Program

                            Argument file is identified on command line with an ‘@’ symbol
                            Sqrw mysqr.sqr @effdate.txt
                            Input values are 1 per line




Peoplesoft Process Scheduler notes

Run Control                 PSPRCSRUNCNTL –

                            Contains
                            OPRID - signon id – eg FOERTS OPRID and RUNCTLID are
                            RUNCNTLID – run control id – eric key fields

                            Other columns such as printer destination, output file destination,
                            etc.




                            Report specific parms come from report specific table

                            begin-select
                            r1.oprid
                            r1.run_cntl_id
                            r1.recruiter_id
                            r1.job_req_nbr
                            from ps_np_run_phw410 r1
                            where r1.oprid=$prcs_oprid   !passed in as arg
                            and r1.run_cntl_id=$prcs_run_cntl_id !passed in
                                                                  !as arg
                            end-select
                            end-procedure Get-run-ctl

Reading/Writing files       Files can be read/written in sequential access only

                            Opening a file
                            Open ‘testfile’ as 1 For-Reading Record=100:Vary ! chars beyond
                                              ! 100 will be ignored. Vary indicates records must
                                              ! be terminated with a line terminator (system
                                              ! dependent)

                            Open $testfile as 1 For-Reading status=#filestat1
                            IF #Filestat1 != 0
                              Display ' '
                              Display ‘'*** Error in File Open 1 ***'
                              Display ' '


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                    Page 11 of 17
                                             SQR Notes

                             Stop
                            End-if


                            Open $output as #file-ix For-Writing Record=100:Fixed ! line
                                           ! terminator not included in 100

                            Open $output as #file-ix For-Writing Record=100:Fixed_Nolf !no line
                                                                           ! feed

                            Open $output as #file-ix For-Append


                            Closing

                            Close 1
                            Close #file-ix

                            Reading
                            Read #file-ix into $input-record:100
                            Read #file-ix into $emplid:10 $emplname:10 $address:30

                            Read #file into $input-record
                            Unstring $input-record By ‘,’ into $emplid $emplname $address
                            Extract $emplname from $input-record 10 10
                            Let $emplname =substr($input_record,10,10)

                            End of file
                            Read #file into $input-record
                             If #end-file = #file
                                ….
                             End-if

                            Read status
                            Read #file
                            If #read_stat <> 0
                               …
                            End-if

                            Trailing blanks are omitted when the record is read.

                            If reading binary data, open the file as fixed or fixed_nolf
                            Read 1 into #Total:4 #Avg:4

                            Binary fields can be 1 to 4 bytes in length

                            Date fields if written in SQR date variable format can be read into
                            either a date or a string variable. The date variable must be one of
                            the following formats.
                                1) format specified by SQR_DB_DATE_FORAMT
                                2) your database specific format
                                3) the database independent format of
                                     ‘SYYYYMMDD[HH24[MI[SS[NNNNNN]]]]’


                            Writing a file



D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                    Page 12 of 17
                                             SQR Notes

                            Write #file From $output:80 Status=$output-stat
                            Write #file from $emplid:10 ‘,’ $last_name:20 ‘,’ $cdate:18

                            The print command can also be used to write output but this is not
                            as flexible. Consult reference for more info.

                            Sorts can be done by creating the input, calling the system sort (if
                            Unix via Call System using ‘sort ….’), then opening and reading the
                            sorted file.


                            Common practice
                            While 1
                              Read 1 into $record:80
                             If #end-file = 1
                                 Break
                             End-if
                             Do process-Input-record
                            End-while


                            While Not #end-file
                              Read 1 into $record:80
                             If #end-file
                                 Break
                             End-if
                             Do process-Input-record
                            End-while

                            Close 1
                            End-procedure



Compiler directives         #Include - insert external source file

                            #Debug – execute sqr code in the debugging mode
                             #Debugx sqr_command
                              Activated by using –DEBUG as command line argument
                             Eg. –DEBUGxyz on command line will activate
                             #Debug Show …’
                             #Debugx Show ‘ …’
                             #Debugy Show ‘ …’
                             #Debugz Show ‘ …’
                             Also can do
                             #debuga #ifDEF MVS
                             #debuga     Show ‘Running on MVS’
                             #debuga #endif



                            #If, #Else, #End-if/#Endif (with or without hyphen OK)
                            To change way SQR compiles specified pieces of source code
                              #IFDEF MVS
                                  ….
                              #Else


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                    Page 13 of 17
                                             SQR Notes

                                 ….
                              #Endif

                            #IfDef, #Endif
                                     Useful in combination with DEBUG to turn on sections of
                                     code
                                     #Ifdef Debug
                                              Show …
                                              Do …..
                                      #Endif


                            Begin-setup
                             Ask yes_no ‘Enter Y or N’
                            End-setup
                            #if {yes_no} = ‘Y’
                              ….
                            #else
                              ….
                            #endif




                            #IfNDef, #End-if


Charts                      See reference for printing charts
File output                 Mainframe vs PC output

                            #ifdef mvs
                              let $OutPutFile = '{FILEPREFIX}' || $FileName || '{FILESUFFIX}'
                              Open $OutPutFile as 1 for-Writing
                                 record = 346:Fixed_nolf
                                 status=#FileStat
                            #else
                              let $OutPutFile = 'c:\temp\pib391W.csv'
                              Open $OutPutFile as 1 for-Writing
                                 record = 346:Fixed
                                 status=#FileStat
                            #end-if
System specific SQC’s       Peoplesoft SQC’s that are specific to the environment and care must
                            be taken to properly keep NT vs mainframe versions

                            OPSYS.sqc – defines environment SQR running in
                             Eg. #DEFINE NT Or
                                 #define MVS mutually exclusive

                            Setupdb.sqc - various database options
Load-lookup                 Load an in-core table and then use for lookups. The table is of the
                            form key/value and the lookup is via a binary search.

                            begin-setup
                            load-lookup
                                    name=prods
                                    table=products


D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                                   Page 14 of 17
                                              SQR Notes

                                    key=product_code
                                    return_value=description
                            end-setup
                            ...
                            begin-select
                            order_num (+1,1)
                            product_code
                                    lookup prods &product_code $desc
                                    print $desc (,15)
                            from orderlines
                            end-select



B. Code templates
!**********************************************************************
!Program ID:                                                          *
!**********************************************************************
! Description of the Report:                                          *
!                                                                     *
!**********************************************************************
! Author:                                                             *
! Date:                                                               *
!**********************************************************************
! Maintenance History                                                 *
!**********************************************************************
! SCR#    Programmer      Date    Description                         *
!                                                                     *
!**********************************************************************

#include 'xxxxxx.sqc' !Identify the environment
#include 'yyyyyy.sqc' !Printer and page-size initialization

!**********************************************************************
begin-setup

#define col_company                       1
#define col_name                          6


  declare-variable
    Integer #var1 #var2
  end-declare
  #include setupdb.sqc

#ifndef EBCDIC                          ! If not running on IBM MVS or AS/400
  declare-printer LP-definition
   type=LINEPRINTER
  init-
string=<27>E<27>(0N<27>&l1O<27>&l8D<27>&l5E<27>&l66F<27>(s16.66H<27>&k2G
!            |     |      |        |        |        |         |            |
!            |     |      |        |        |        |         |              --> CR
!            |     |      |        |        |        |           --> Line Prntr font
!            |     |      |        |        |          --> 66 text lines
!            |     |      |        |          --> top margin = 5 lines
!            |     |      |          --> 8 lines per inch
!            |     |        --> Landscape mode
!            |      --> ISO 8859-1 symbol set
!              --> Reset



D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc                        Page 15 of 17
                                             SQR Notes

    before-bold=<27>[r
    after-bold=<27>[u
  end-declare
  #else                                             ! EBCDIC Ports
  declare-printer LP-definition
    type=LINEPRINTER
  end-declare
  #end-if

 declare-layout listing
    ORIENTATION=landscape
    LEFT-MARGIN=0.0 in
    TOP-MARGIN=0.0 in
    MAX-LINES = 58
    MAX-COLUMNS = 177
  end-declare

   declare-report A
    layout=listing
   end-declare

   declare-report B
    layout=listing
   end-declare




end-setup

!**********************************************************************
begin-program

end-program

!**********************************************************************
begin-program

end-program
!**********************************************************************
begin-heading 1
   for-reports=(A)
   print 'Comp'                     (+1,{col_company})
   print 'Name'                       (+0,{col_name})
end-heading

!***************************************************************
begin-procedure Process-Main
begin-SELECT Loops =
A.EMPLID
A.NAME
 …
  Do stuff
FROM
  …
Where
  …


!**********************************************************************




D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc           Page 16 of 17
                                             SQR Notes

Begin-Procedure Open-Files
  let #OutFileNumber = 1
  let $OutFileName = '{FILEPREFIX}' || 'PRGID01W| '{FILESUFFIX}'

  OPEN $OutFileName
    as #OutFileNumber
   for-Writing
       record=1296:FIXED_NOLF
       status=#StatusOutFile

  if #statusoutfile <> 0
     show 'Error in Opening PRGID01W File'
     let #return-code = 1
     stop
  end-if

let $FileName2 = '{FILEPREFIX}' || $FileName2 || '{FILESUFFIX}'
open $Filename2 as 2 for-reading record=200:{fixed} status=#filestat2

IF #filestat2 != 0
   DISPLAY ' '
   DISPLAY '*** Error in File Open 2 ***'
   DISPLAY ' '
   STOP
END-IF

End-Procedure Open-Files

!**********************************************************************
Begin-Procedure Write-Out-File
!**********************************************************************
  Write #OutFileNumber
  from $field1:11
        $field2:11
End-Procedure Write-Out-File

!**********************************************************************
Begin-Procedure Close-Out-File
!**********************************************************************
    close #OutFileNumber
End-Procedure Close-Out-File

#Include 'datetime.sqc'
 …




D:\Docstoc\Working\pdf\5c8ae34d-b70c-4f53-822b-c33e43b89062.doc           Page 17 of 17

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:12/1/2011
language:English
pages:17