SQL*Engine - Part 1 - Initial Overview/Demonstration
SQL*Engine - Part 2 - Launching PeopleSoft Process Requests
SQL*Engine - Part 3 - Command Guide
One of the most powerful features of my SQL*Engine process is the ability to create, modify & utilize variables within the SQL*Engine Script. Variables can be incorporated in SQL, Step Names & Conditional Execution as well as other clever uses. It deserves a special blog post all it's own. Here I'll demonstrate the @SET= functionality by comparing a Sample Script to the actual report output it produces. You can see the before/after values of the resolved variable substitutions & function results.
Immediately below is a sample SQL*Engine script that highlights the use of the @SET= directive along with it's variety of functions & meta-operators. The script itself does not perform any executable steps (SQL, @COUNT=, @SELECT= or @RUN=) - it is "Stepless". Variables are created, manipulated & displayed on the report.
*****************************************************************
* *
* MODULE: XXSQI_SAMPLE.SQX *
* AUTHOR: TONY DELIA. *
* DATE: 03/21/2019. *
* DESC: SQL*ENGINE SCRIPT - SAMPLE. *
* *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
* PROGRAM VARIABLES *
*****************************************************************
@HDR_REF=TD_SAMPLE_201903
@HDR_DESCR=TD SAMPLE SQL*Engine Script
*****************************************************************
* SAMPLE *
*****************************************************************
@DELIMITER=,
@SET=&&TEST=ABCDEFGHIJKLMNOPQRSTUVWXYZ
@MSG=Test Variable Original Value... [&&TEST]
@SET=&&TEST=@REVERSE
@MSG=Test Variable Reversed......... [&&TEST]
@SET=&&TEST=@LOWER
@MSG=Test Variable Lower Case....... [&&TEST]
@SET=&&TEST=@UPPER
@MSG=Test Variable Upper Case....... [&&TEST]
@SET=&&TEST=@REVERSE
@MSG=Test Variable Reversed Again... [&&TEST]
@SET=&&TEST=@XLATE(AEIOU,*****)
@MSG=Test Variable Translation...... [&&TEST]
@DELIMITER=*
@PARSE=&&TEST
@DELIMITER=,
@SET=&&P1=@COL:1
@SET=&&P2=@COL:2
@SET=&&P3=@COL:3
@SET=&&P4=@COL:4
@SET=&&P5=@COL:5
@SET=&&P6=@COL:6
@MSG=After Parsing by *:
@MSG=P1 [&&P1]
@MSG=P2 [&&P2]
@MSG=P3 [&&P3]
@MSG=P4 [&&P4]
@MSG=P5 [&&P5]
@MSG=P6 [&&P6]
@SET=&&XYZ=@SUBSTR(&&TEST,24,3)
@MSG=Extract 'XYZ' from String...... [&&XYZ]
@SET=&&XYZ=@REPLACE(XYZ,123)
@MSG=Replace 'XYZ' with '123'....... [&&XYZ]
@SET=&&TEST=@PAD(R,30,$)
@MSG=Right Pad with 30 $'s.......... [&&TEST]
@SET=&&TEST=@STRIP(*)
@MSG=Strip Out the Asterisks........ [&&TEST]
@SET=&&LEN=@LENGTH(&&TEST)
@MSG=Length of Test String.......... [&&LEN]
@SET=&&LIT=@NUMLIT(&&LEN)
@MSG=Length of String in English.... [&&LIT]
*
@SET=&&WORK=&&LEN
@SET=&&WORK=@CALC(%ADD,123000)
@MSG=Length PLUS 123000 (Random).... [&&WORK]
@SET=&&LIT=@NUMLIT(&&WORK)
@MSG=Literal Representation......... [&&LIT]
*
@SET=&&WORK=@CALC(%SUB,1000)
@MSG=Now Subtract 1000.............. [&&WORK]
@SET=&&LIT=@NUMLIT(&&WORK)
@MSG=Literal Representation......... [&&LIT]
*
@SET=&&WORK=@CALC(%DIV,2)
@MSG=Next Divide by 2............... [&&WORK]
@SET=&&LIT=@NUMLIT(&&WORK)
@MSG=Literal Representation......... [&&LIT]
*
@SET=&&WORK=@CALC(%MUL,3)
@MSG=Then Multiply by 3............. [&&WORK]
@SET=&&LIT=@NUMLIT(&&WORK)
@MSG=Literal Representation......... [&&LIT]
*
@SET=&&WORK=@CALC(%RND,1000,U)
@MSG=Round Up to 1000's............. [&&WORK]
@SET=&&LIT=@NUMLIT(&&WORK)
@MSG=Literal Representation......... [&&LIT]
*
@SET=&&MOD=&&WORK
@SET=&&MOD=@CALC(%MOD,50000)
@MSG=Modulus when / 50,000.......... [&&MOD]
@SET=&&LIT=@NUMLIT(&&MOD)
@MSG=Literal Representation......... [&&LIT]
*
@SET=&&BIN=1111
@SET=&&DEC=@CONVERT(&&BIN,%BIN,%DEC)
@SET=&&DEC=@CALC(%ADD,1)
@SET=&&BIN=@CONVERT(&&DEC,%DEC,%BIN)
@MSG=Updated Binary Value (&&BIN)
@SET=&&HEX=@CONVERT(&&BIN,%BIN,%HEX)
@MSG=Converted Bin-to-Hex Value (&&HEX)
@SET=&&DEC=@CONVERT(&&HEX,%HEX,%DEC)
@MSG=Converted Hex-to-Dec Value (&&DEC)
@SET=&&DEC=@CALC(%ADD,67)
@MSG=Decimal PLUS 67 (&&DEC)
@SET=&&HEX=@CONVERT(&&DEC,%DEC,%HEX)
@SET=&&BIN=@CONVERT(&&HEX,%HEX,%BIN)
@SET=&&OCT=@CONVERT(&&HEX,%HEX,%OCT)
@SET=&&DEC=@CONVERT(&&BIN,%BIN,%DEC)
@MSG=HEX(&&HEX) DEC(&&DEC) OCT(&&OCT) BIN(&&BIN)
*
@SET=&&FLD=1
@SET=&&FLD=@CALC(%OR,14)
@SET=&&FLD=@CALC(%AND,7)
@SET=&&FLD=@CALC(%XOR,32)
*
@SET=&&X1=123
@SET=&&X2=456
@SET=&&B1=@CONVERT(&&X1,%DEC,%BIN)
@SET=&&B1=@PAD(L,10,0)
@SET=&&B2=@CONVERT(&&X2,%DEC,%BIN)
@SET=&&B2=@PAD(L,10,0)
@MSG= Before: X1[&&X1] X2[&&X2]
@SET=&&X1=@CALC(%XOR,&&X2)
@SET=&&B1=@CONVERT(&&X1,%DEC,%BIN)
@SET=&&B1=@PAD(L,10,0)
@SET=&&X2=@CALC(%XOR,&&X1)
@SET=&&B2=@CONVERT(&&X2,%DEC,%BIN)
@SET=&&B2=@PAD(L,10,0)
@SET=&&X1=@CALC(%XOR,&&X2)
@SET=&&B1=@CONVERT(&&X1,%DEC,%BIN)
@SET=&&B1=@PAD(L,10,0)
@MSG= After: X1[&&X1] X2[&&X2]
*
@SET=&&DBNAME=@DBNAME
@MSG=Database Name.................. [&&DBNAME]
@MSG=List all Variable Contents
@DISPLAY
@MSG=End of Sample
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
When the SQL*Engine script is processed it resolves the dynamic variables & uses them as specified. The variable substitution can be viewed in the LOG file (not shown here) and the formatted report output. Below you will find segments of the input SQL*Engine script (GREEN) followed by the resulting output on the report (ORANGE). Functions include handling of: Strings, Arithmetic, Binary Operations & Base Number System Conversion (Binary/Decimal/Octal/Hexadecimal). Throughout the script a variable may be passed from one directive to another & modified accordingly.
@DELIMITER=,
@SET=&&TEST=ABCDEFGHIJKLMNOPQRSTUVWXYZ
@MSG=Test Variable Original Value... [&&TEST]
DIR> @SET=&&TEST=ABCDEFGHIJKLMNOPQRSTUVWXYZ
SET> ABCDEFGHIJKLMNOPQRSTUVWXYZ
MSG> Test Variable Original Value... [ABCDEFGHIJKLMNOPQRSTUVWXYZ]
@SET=&&TEST=@REVERSE
@MSG=Test Variable Reversed......... [&&TEST]
DIR> @SET=&&TEST=@REVERSE
SET> ZYXWVUTSRQPONMLKJIHGFEDCBA
MSG> Test Variable Reversed......... [ZYXWVUTSRQPONMLKJIHGFEDCBA]
@SET=&&TEST=@LOWER
@MSG=Test Variable Lower Case....... [&&TEST]
DIR> @SET=&&TEST=@LOWER
SET> zyxwvutsrqponmlkjihgfedcba
MSG> Test Variable Lower Case....... [zyxwvutsrqponmlkjihgfedcba]
@SET=&&TEST=@UPPER
@MSG=Test Variable Upper Case....... [&&TEST]
DIR> @SET=&&TEST=@UPPER
SET> ZYXWVUTSRQPONMLKJIHGFEDCBA
MSG> Test Variable Upper Case....... [ZYXWVUTSRQPONMLKJIHGFEDCBA]
@SET=&&TEST=@REVERSE
@MSG=Test Variable Reversed Again... [&&TEST]
DIR> @SET=&&TEST=@REVERSE
SET> ABCDEFGHIJKLMNOPQRSTUVWXYZ
MSG> Test Variable Reversed Again... [ABCDEFGHIJKLMNOPQRSTUVWXYZ]
@SET=&&TEST=@XLATE(AEIOU,*****)
@MSG=Test Variable Translation...... [&&TEST]
DIR> @SET=&&TEST=@XLATE(AEIOU,*****)
SET> *BCD*FGH*JKLMN*PQRST*VWXYZ
MSG> Test Variable Translation...... [*BCD*FGH*JKLMN*PQRST*VWXYZ]
@DELIMITER=*
@PARSE=&&TEST
DIR> @PARSE=*BCD*FGH*JKLMN*PQRST*VWXYZ
@DELIMITER=,
@SET=&&P1=@COL:1
@SET=&&P2=@COL:2
@SET=&&P3=@COL:3
@SET=&&P4=@COL:4
@SET=&&P5=@COL:5
@SET=&&P6=@COL:6
@MSG=After Parsing by *:
@MSG=P1 [&&P1]
@MSG=P2 [&&P2]
@MSG=P3 [&&P3]
@MSG=P4 [&&P4]
@MSG=P5 [&&P5]
@MSG=P6 [&&P6]
MSG> P1 []
MSG> P2 [BCD]
MSG> P3 [FGH]
MSG> P4 [JKLMN]
MSG> P5 [PQRST]
MSG> P6 [VWXYZ]
@SET=&&XYZ=@SUBSTR(&&TEST,24,3)
@MSG=Extract 'XYZ' from String...... [&&XYZ]
DIR> @SET=&&XYZ=@SUBSTR(*BCD*FGH*JKLMN*PQRST*VWXYZ,24,3)
SET> XYZ
MSG> Extract 'XYZ' from String...... [XYZ]
@SET=&&XYZ=@REPLACE(XYZ,123)
@MSG=Replace 'XYZ' with '123'....... [&&XYZ]
DIR> @SET=&&XYZ=@REPLACE(XYZ,123)
SET> 123
MSG> Replace 'XYZ' with '123'....... [123]
@SET=&&TEST=@PAD(R,30,$)
@MSG=Right Pad with 30 $'s.......... [&&TEST]
DIR> @SET=&&TEST=@PAD(R,30,$)
SET> *BCD*FGH*JKLMN*PQRST*VWXYZ$$$$
MSG> Right Pad with 30 $'s.......... [*BCD*FGH*JKLMN*PQRST*VWXYZ$$$$]
@SET=&&TEST=@STRIP(*)
@MSG=Strip Out the Asterisks........ [&&TEST]
DIR> @SET=&&TEST=@STRIP(*)
SET> BCDFGHJKLMNPQRSTVWXYZ$$$$
MSG> Strip Out the Asterisks........ [BCDFGHJKLMNPQRSTVWXYZ$$$$]
@SET=&&LEN=@LENGTH(&&TEST)
@MSG=Length of Test String.......... [&&LEN]
DIR> @SET=&&LEN=@LENGTH(BCDFGHJKLMNPQRSTVWXYZ$$$$)
SET> 25
MSG> Length of Test String.......... [25]
@SET=&&LIT=@NUMLIT(&&LEN)
@MSG=Length of String in English.... [&&LIT]
DIR> @SET=&&LIT=@NUMLIT(25)
SET> Twenty-Five
MSG> Length of String in English.... [Twenty-Five]
@SET=&&WORK=&&LEN
@SET=&&WORK=@CALC(%ADD,123000)
@MSG=Length PLUS 123000 (Random).... [&&WORK]
DIR> @SET=&&WORK=@CALC(%ADD,123000)
SET> 123025
MSG> Length PLUS 123000 (Random).... [123025]
@SET=&&WORK=@CALC(%SUB,1000)
@MSG=Now Subtract 1000.............. [&&WORK]
DIR> @SET=&&WORK=@CALC(%SUB,1000)
SET> 122025
MSG> Now Subtract 1000.............. [122025]
@SET=&&WORK=@CALC(%DIV,2)
@MSG=Next Divide by 2............... [&&WORK]
DIR> @SET=&&WORK=@CALC(%DIV,2)
SET> 61012.5
MSG> Next Divide by 2............... [61012.5]
@SET=&&WORK=@CALC(%MUL,3)
@MSG=Then Multiply by 3............. [&&WORK]
@SET=&&LIT=@NUMLIT(&&WORK)
@MSG=Literal Representation......... [&&LIT]
DIR> @SET=&&WORK=@CALC(%MUL,3)
SET> 183037.5
MSG> Then Multiply by 3............. [183037.5]
DIR> @SET=&&LIT=@NUMLIT(183037.5)
SET> One Hundred Eighty-Three Thousand Thirty-Seven and 50/100
MSG> Literal Representation......... [One Hundred Eighty-Three Thousand Thirty-Seven and 50/100]
@SET=&&WORK=@CALC(%RND,1000,U)
@MSG=Round Up to 1000's............. [&&WORK]
DIR> @SET=&&WORK=@CALC(%RND,1000,U)
SET> 184000
MSG> Round Up to 1000's............. [184000]
@SET=&&MOD=&&WORK
@SET=&&MOD=@CALC(%MOD,50000)
@MSG=Modulus when / 50,000.......... [&&MOD]
DIR> @SET=&&MOD=184000
SET> 184000
DIR> @SET=&&MOD=@CALC(%MOD,50000)
SET> 34000
MSG> Modulus when / 50,000.......... [34000]
@SET=&&BIN=1111
@SET=&&DEC=@CONVERT(&&BIN,%BIN,%DEC)
@SET=&&DEC=@CALC(%ADD,1)
@SET=&&BIN=@CONVERT(&&DEC,%DEC,%BIN)
@MSG=Updated Binary Value (&&BIN)
DIR> @SET=&&BIN=1111
SET> 1111
DIR> @SET=&&DEC=@CONVERT(1111,%BIN,%DEC)
SET> 15
DIR> @SET=&&DEC=@CALC(%ADD,1)
SET> 16
DIR> @SET=&&BIN=@CONVERT(16,%DEC,%BIN)
SET> 10000
MSG> Updated Binary Value (10000)
@SET=&&HEX=@CONVERT(&&BIN,%BIN,%HEX)
@MSG=Converted Bin-to-Hex Value (&&HEX)
@SET=&&DEC=@CONVERT(&&HEX,%HEX,%DEC)
@MSG=Converted Hex-to-Dec Value (&&DEC)
@SET=&&DEC=@CALC(%ADD,67)
@MSG=Decimal PLUS 67 (&&DEC)
@SET=&&HEX=@CONVERT(&&DEC,%DEC,%HEX)
@SET=&&BIN=@CONVERT(&&HEX,%HEX,%BIN)
@SET=&&OCT=@CONVERT(&&HEX,%HEX,%OCT)
@SET=&&DEC=@CONVERT(&&BIN,%BIN,%DEC)
@MSG=HEX(&&HEX) DEC(&&DEC) OCT(&&OCT) BIN(&&BIN)
DIR> @SET=&&HEX=@CONVERT(10000,%BIN,%HEX)
SET> 10
MSG> Converted Bin-to-Hex Value (10)
DIR> @SET=&&DEC=@CONVERT(10,%HEX,%DEC)
SET> 16
MSG> Converted Hex-to-Dec Value (16)
DIR> @SET=&&DEC=@CALC(%ADD,67)
SET> 83
MSG> Decimal PLUS 67 (83)
DIR> @SET=&&HEX=@CONVERT(83,%DEC,%HEX)
SET> 53
DIR> @SET=&&BIN=@CONVERT(53,%HEX,%BIN)
SET> 1010011
DIR> @SET=&&OCT=@CONVERT(53,%HEX,%OCT)
SET> 123
DIR> @SET=&&DEC=@CONVERT(1010011,%BIN,%DEC)
SET> 83
MSG> HEX(53) DEC(83) OCT(123) BIN(1010011)
@SET=&&FLD=1
@SET=&&FLD=@CALC(%OR,14)
@SET=&&FLD=@CALC(%AND,7)
@SET=&&FLD=@CALC(%XOR,32)
DIR> @SET=&&FLD=1
SET> 1
DIR> @SET=&&FLD=@CALC(%OR,14)
SET> 15
DIR> @SET=&&FLD=@CALC(%AND,7)
SET> 7
DIR> @SET=&&FLD=@CALC(%XOR,32)
SET> 39
@SET=&&X1=123
@SET=&&X2=456
@SET=&&B1=@CONVERT(&&X1,%DEC,%BIN)
@SET=&&B1=@PAD(L,10,0)
@SET=&&B2=@CONVERT(&&X2,%DEC,%BIN)
@SET=&&B2=@PAD(L,10,0)
DIR> @SET=&&X1=123
SET> 123
DIR> @SET=&&X2=456
SET> 456
DIR> @SET=&&B1=@CONVERT(123,%DEC,%BIN)
SET> 1111011
DIR> @SET=&&B1=@PAD(L,10,0)
SET> 0001111011
DIR> @SET=&&B2=@CONVERT(456,%DEC,%BIN)
SET> 111001000
DIR> @SET=&&B2=@PAD(L,10,0)
SET> 0111001000
@MSG= Before: X1[&&X1] X2[&&X2]
@SET=&&X1=@CALC(%XOR,&&X2)
@SET=&&B1=@CONVERT(&&X1,%DEC,%BIN)
@SET=&&B1=@PAD(L,10,0)
@SET=&&X2=@CALC(%XOR,&&X1)
@SET=&&B2=@CONVERT(&&X2,%DEC,%BIN)
@SET=&&B2=@PAD(L,10,0)
@SET=&&X1=@CALC(%XOR,&&X2)
@SET=&&B1=@CONVERT(&&X1,%DEC,%BIN)
@SET=&&B1=@PAD(L,10,0)
@MSG= After: X1[&&X1] X2[&&X2]
MSG> Before: X1[123] X2[456]
DIR> @SET=&&X1=@CALC(%XOR,456)
SET> 435
DIR> @SET=&&B1=@CONVERT(435,%DEC,%BIN)
SET> 110110011
DIR> @SET=&&B1=@PAD(L,10,0)
SET> 0110110011
DIR> @SET=&&X2=@CALC(%XOR,435)
SET> 123
DIR> @SET=&&B2=@CONVERT(123,%DEC,%BIN)
SET> 1111011
DIR> @SET=&&B2=@PAD(L,10,0)
SET> 0001111011
DIR> @SET=&&X1=@CALC(%XOR,123)
SET> 456
DIR> @SET=&&B1=@CONVERT(456,%DEC,%BIN)
SET> 111001000
DIR> @SET=&&B1=@PAD(L,10,0)
SET> 0111001000
MSG> After: X1[456] X2[123]
@SET=&&DBNAME=@DBNAME
@MSG=Database Name.................. [&&DBNAME]
DIR> @SET=&&DBNAME=@DBNAME
SET> NSFSDEV
MSG> Database Name.................. [NSFSDEV]
@MSG=List all Variable Contents
@DISPLAY
MSG> List all Variable Contents
DIR> @DISPLAY
VAR> &&TEST = BCDFGHJKLMNPQRSTVWXYZ$$$$
VAR> &&P1 =
VAR> &&P2 = BCD
VAR> &&P3 = FGH
VAR> &&P4 = JKLMN
VAR> &&P5 = PQRST
VAR> &&P6 = VWXYZ
VAR> &&XYZ = 123
VAR> &&LEN = 25
VAR> &&LIT = Thirty-Four Thousand
VAR> &&WORK = 184000
VAR> &&MOD = 34000
VAR> &&BIN = 1010011
VAR> &&DEC = 83
VAR> &&HEX = 53
VAR> &&OCT = 123
VAR> &&FLD = 39
VAR> &&X1 = 456
VAR> &&X2 = 123
VAR> &&B1 = 0111001000
VAR> &&B2 = 0001111011
VAR> &&DBNAME = NSFSDEV
At the end of the script the @DISPLAY directive shows all dynamic variables created with @SET= & shows the latest value assigned to them. This directive can be placed in multiple areas of your script while testing to make sure the process is passing the expected values.
As with any programming language great care must be taken to assure the proper results are achieved.
