|
| |
NOTE - Use your Browsers BACK Button to return to prior page or
CLICK here.
| SQR Select/Insert Procedure
Generator (and a Coral Reef...) |
| The following SQR Utility can be used when building PeopleSoft applications. A shell
SELECT and/or INSERT SQR procedure is generated using the RECNAME entered through an input
prompt. Additional prompts are provided to control the table alias, column synonyms and
variable prefixes... We'll describe some of these as we proceed below... |
| Download TD75_03.SQR (Original
Version) |
 |
Click on the Tropical Fish to download the TD75_03 program. As
we proceed through the page we're also going to build a Coral Reef... Let's get started... |
| REVISION - Download TD75_04.SQR
(Improved Version) |
 |
Here's a new version with improved subrecord support... this
version supports an 'almost' unlimited number of nested subrecords... this is something
I've been meaning to change for a while.. click on the Lobster for the TD75_04.SQR
version... |
 |
The TD75_04.SQR requires the include file called TDSUB.SQC...
this is the engine that produces the resolved field list for each primary record... click
on the Octopus to download... scroll down the page to view the new TD75_04.SQR source... |
| Let's begin building the
"Coral Reef"... |
 |
A Long-Nosed ButterFly Fish can be seen hovering around the reef... this
particular reef was created using crayola crayons... now... let's start talking about the
SQR Utility... |
 |
Let's take a look at the input prompts used by the TD75_03.SQR program.
After describing the prompts we can then examine the output SQR procedures generated by
the program. |
| TD75_03.SQR Input Prompts |
 | Enter RECNAME: |
The PeopleSoft Record Definition.
| Generate SELECT or INSERT procedure? (S/I): |
Press <enter> to generate both
(Select/Insert)
| Enter Synonym Character(s): |
Table alias used for synonyms and variables.
| Attach FieldType as Variable Suffix? (Y/N): |
FieldType (Char, Nbr, Date, etc.) appended
to variable for easy identification.
Example: $A_emplid_CHAR, #A_comprate_NBR
| Generate more procedures? (Y/N): |
Create a SELECT/INSERT procedure for another
record.
| Sample Output - SELECT procedure |
 |
The following sample output is for the record FED_TAX_DATA. I used a
synonym of "F" and I did not elect to use the fieldtype as a variable suffix. |
!**********************************************************************
!* SQR Select Procedure generated by TD75_04 - Tony DeLia *
!**********************************************************************
begin-procedure Select-fed_tax_data
begin-select
f.emplid
f.company
f.effdt
f.special_fwt_status
f.fwt_mar_status
f.fwt_allowances
f.fwt_addl_amt
f.fwt_addl_pct
f.fica_status_ee
f.fut_exempt
f.eic_status
f.statutory_ee
f.deceased
f.pension_plan
f.legal_rep
f.deferred_comp
f.use_total_wage
f.last_action
f.w4_prcss
f.w5_prcss
f.lock_in_recvd
f.lock_in_limit
f.last_update_date
let $F_emplid = &f.emplid
let $F_company = &f.company
let $F_effdt = &f.effdt
let $F_special_fwt_status = &f.special_fwt_status
let $F_fwt_mar_status = &f.fwt_mar_status
let #F_fwt_allowances = &f.fwt_allowances
let #F_fwt_addl_amt = &f.fwt_addl_amt
let #F_fwt_addl_pct = &f.fwt_addl_pct
let $F_fica_status_ee = &f.fica_status_ee
let $F_fut_exempt = &f.fut_exempt
let $F_eic_status = &f.eic_status
let $F_statutory_ee = &f.statutory_ee
let $F_deceased = &f.deceased
let $F_pension_plan = &f.pension_plan
let $F_legal_rep = &f.legal_rep
let $F_deferred_comp = &f.deferred_comp
let $F_use_total_wage = &f.use_total_wage
let $F_last_action = &f.last_action
let $F_w4_prcss = &f.w4_prcss
let $F_w5_prcss = &f.w5_prcss
let $F_lock_in_recvd = &f.lock_in_recvd
let #F_lock_in_limit = &f.lock_in_limit
let $F_last_update_date = &f.last_update_date
from ps_fed_tax_data f
where f.emplid = < CHAR >
and f.company = < CHAR >
and f.effdt = < DATE >
end-select
end-procedure
!**********************************************************************
|
 |
First of all, notice the generated SELECT procedure has been named
Select-fed_tax_data. This is the default format... the user can edit this once the routine
is placed in their program. The synonym "F" has been used to prefix columns and
variables. All columns have been assigned to variables ($ or # reflects datatype). |
 |
The "F" has also been used as the table alias. Dummy criteria
has been generated based on the primary keys of the record. You can edit this manually
within your program. You may also want to edit the opening comments at the start of the
generated SELECT procedure... |
| Sample Output - INSERT procedure |
 |
We're still using the record FED_TAX_DATA. The SQR INSERT procedure is
generated with appropriate columns, variables and comments. You can replace the variables
with hard-coded defaults... the comments are especially useful in this case. |
!**********************************************************************
!* SQR Insert Procedure generated by TD75_04 - Tony DeLia *
!**********************************************************************
begin-procedure Insert-fed_tax_data
begin-sql
insert into ps_fed_tax_data
(emplid,
company,
effdt,
special_fwt_status,
fwt_mar_status,
fwt_allowances,
fwt_addl_amt,
fwt_addl_pct,
fica_status_ee,
fut_exempt,
eic_status,
statutory_ee,
deceased,
pension_plan,
legal_rep,
deferred_comp,
use_total_wage,
last_action,
w4_prcss,
w5_prcss,
lock_in_recvd,
lock_in_limit,
last_update_date)
values ($F_emplid, ! EMPLID
$F_company, ! COMPANY
$F_effdt, ! EFFDT
$F_special_fwt_status, ! SPECIAL_FWT_STATUS
$F_fwt_mar_status, ! FWT_MAR_STATUS
#F_fwt_allowances, ! FWT_ALLOWANCES
#F_fwt_addl_amt, ! FWT_ADDL_AMT
#F_fwt_addl_pct, ! FWT_ADDL_PCT
$F_fica_status_ee, ! FICA_STATUS_EE
$F_fut_exempt, ! FUT_EXEMPT
$F_eic_status, ! EIC_STATUS
$F_statutory_ee, ! STATUTORY_EE
$F_deceased, ! DECEASED
$F_pension_plan, ! PENSION_PLAN
$F_legal_rep, ! LEGAL_REP
$F_deferred_comp, ! DEFERRED_COMP
$F_use_total_wage, ! USE_TOTAL_WAGE
$F_last_action, ! LAST_ACTION
$F_w4_prcss, ! W4_PRCSS
$F_w5_prcss, ! W5_PRCSS
$F_lock_in_recvd, ! LOCK_IN_RECVD
#F_lock_in_limit, ! LOCK_IN_LIMIT
$F_last_update_date); ! LAST_UPDATE_DATE
end-sql
end-procedure
!**********************************************************************
|
 |
The procedure is given a default name of Insert-fed_tax_data. Each column
is placed in the list. The values list contains the corresponding variable and comment
(fieldname). Variables are prefixed with the $ or # based on the datatype. |
| Sample Output - SELECT procedure
using datatype suffix |
 |
Here's sample SELECT output using the datatype suffix option. We'll use
the record STATE_TAX_DATA along with the synonym "ST". The datatype option is a
quick way of determining the exact datatype (date, sign, nbr, etc) instead of $ or # only.
They can be removed manually later... |
!**********************************************************************
!* SQR Select Procedure generated by TD75_04 - Tony DeLia *
!**********************************************************************
begin-procedure Select-state_tax_data
begin-select
st.emplid
st.company
st.effdt
st.state
st.resident
st.non_res_declared
st.special_swt_status
st.swt_mar_status
st.swt_allowances
st.swt_addl_allownces
st.wage_plan_cd
st.annl_exemption_amt
st.percent_of_fwt
st.swt_addl_amt
st.swt_addl_pct
st.sdi_status
st.sut_exempt
st.ui_jurisdiction
st.addl_amt_adj
st.lock_in_recvd
st.lock_in_limit
let $ST_emplid_CHAR = &st.emplid
let $ST_company_CHAR = &st.company
let $ST_effdt_DATE = &st.effdt
let $ST_state_CHAR = &st.state
let $ST_resident_CHAR = &st.resident
let $ST_non_res_declared_CHAR = &st.non_res_declared
let $ST_special_swt_status_CHAR = &st.special_swt_status
let $ST_swt_mar_status_CHAR = &st.swt_mar_status
let #ST_swt_allowances_NBR = &st.swt_allowances
let #ST_swt_addl_allownces_NBR = &st.swt_addl_allownces
let $ST_wage_plan_cd_CHAR = &st.wage_plan_cd
let #ST_annl_exemption_amt_NBR = &st.annl_exemption_amt
let #ST_percent_of_fwt_NBR = &st.percent_of_fwt
let #ST_swt_addl_amt_SIGN = &st.swt_addl_amt
let #ST_swt_addl_pct_NBR = &st.swt_addl_pct
let $ST_sdi_status_CHAR = &st.sdi_status
let $ST_sut_exempt_CHAR = &st.sut_exempt
let $ST_ui_jurisdiction_CHAR = &st.ui_jurisdiction
let $ST_addl_amt_adj_CHAR = &st.addl_amt_adj
let $ST_lock_in_recvd_CHAR = &st.lock_in_recvd
let #ST_lock_in_limit_NBR = &st.lock_in_limit
from ps_state_tax_data st
where st.emplid = < CHAR >
and st.company = < CHAR >
and st.effdt = < DATE >
and st.state = < CHAR >
end-select
end-procedure
!**********************************************************************
|
 |
The procedure is given a default name of Select-state_tax_data. The
variables now have a suffix that denotes the datatype. There are times when this may come
in handy - especially when working with large records that have fields with many different
datatypes and similar names. |
 |
Here's the complete source code for the TD75_03.SQR program. Our
"Coral Reef" is almost complete... |
!**********************************************************************
!* *
!* MODULE: TD75_04.SQR *
!* AUTHOR: TONY DELIA. *
!* DATE: 10/06/1999. *
!* SYSTEM: TD SQR UTILITY SERIES. *
!* DESC: GENERATE SQR SELECT/INSERT PROCEDURES. *
!* *
!**********************************************************************
!* *
!* TABLES: psrecfield - Select *
!* psdbfield - Select *
!* pskeydefn - Select *
!* *
!**********************************************************************
!* *
!* INPUT: A) Enter RECNAME: *
!* *
!* The PeopleSoft Record Definition. *
!* *
!* B) Generate SELECT or INSERT procedure? (S/I): *
!* *
!* Press <enter> to generate both (Select/Insert). *
!* *
!* C) Enter Synonym Character(s): *
!* *
!* Table alias used for synonyms and variables. *
!* *
!* D) Attach FieldType as Variable Suffix? (Y/N): *
!* *
!* FieldType (Char, Nbr, Date, etc.) appended to *
!* variable for easy identification. *
!* *
!* Example: $A_emplid_CHAR, #A_comprate_NBR *
!* *
!* E) Generate more procedures? (Y/N): *
!* *
!* Repeats process starting with Step A. *
!* *
!**********************************************************************
!* *
!* LEGAL: CONFIDENTIALITY INFORMATION. *
!* *
!* This module is the original work of Tony DeLia. It *
!* can be considered ShareWare under the following *
!* conditions. *
!* *
!* A - The author's name (Tony DeLia) remains on any *
!* and all versions of this module. *
!* B - Any modifications must be clearly identified. *
!* C - A "vanilla" copy of this module must be kept *
!* alongside any revised versions. *
!* *
!**********************************************************************
!* *
!* WEBSITE: http://www.sqrtools.com *
!* *
!* Questions/Comments: tdelia@erols.com *
!* *
!**********************************************************************
#include 'setenv.sqc' ! Set environment
!**********************************************************************
!* Setup Procedure *
!**********************************************************************
begin-setup
#Include 'setup02a.sqc' ! Printer and page-size init landscape
end-setup
!**********************************************************************
!* Headings *
!**********************************************************************
begin-heading 08
#include 'stdhdg01.sqc'
print '=' ( +1, 1,175 ) fill
print ' ' ( +1, 1, 0 )
print '=' ( +1, 1,175 ) fill
end-heading
!**********************************************************************
!* Mainline Processing *
!**********************************************************************
begin-report
do Init-DateTime
do Get-Current-DateTime
move $AsOfToday to $AsOfDate
do Set-Defaults
open $FileName as 1 for-writing record=100 status=#o-status
if #o-status = 0
let $sw = 'Y'
while upper($sw) = 'Y'
let $sw = 'N'
do Process-Main
input $sw maxlen=1 'Generate more procedures? (Y/N)'
end-while
close 1
else
display 'Output File Error ' oline
display $FileName
end-if
end-report
!**********************************************************************
!* Set Defaults *
!**********************************************************************
begin-procedure Set-Defaults
let $ReportTitle = 'Generate SQR Select/Insert Procedures'
let $ReportId = 'TD75_04'
display $ReportId noline
display ' ' noline
display $ReportTitle
display ' '
let $TypeString = 'CharLongNbr SignDateTimeDtTm****'
let $VarString = '$$##$$$*'
create-array name=GENmtx size=500 field=GENname:char -
field=GENvar:char -
field=GENtype:char -
field=GENkey:char
let $FileName = '{FILEPREFIX}' || $ReportId || '.OUT'
let $comment = chr(33) ! Exclamation Point
! Process Scheduler Workaround
input $dummy 'Press <enter> 1 of 3'
input $dummy 'Press <enter> 2 of 3'
input $dummy 'Press <enter> 3 of 3'
end-procedure
!**********************************************************************
!* Process Main *
!**********************************************************************
begin-procedure Process-Main
input $RECname maxlen=15 'Enter RECNAME'
input $PRCtype maxlen=1 'Generate SELECT or INSERT procedure? (S/I)'
uppercase $PRCtype
if $PRCtype <> 'S' ! SELECT SQR Procedure
and $PRCtype <> 'I' ! INSERT SQR Procedure
let $PRCtype = 'B' ! Default - Both SELECT/INSERT procedures
end-if
input $SYNchar 'Enter Synonym Character(s)'
input $VARsuf 'Attach FieldType as Variable Suffix? (Y/N)'
uppercase $VARsuf
let #varsuf = 5
if $VARsuf <> 'Y'
let $VARsuf = 'N'
let #varsuf = 0
end-if
do Process-Record
if #P_ctr > 0
! Generate SELECT Procedure
if $PRCtype = 'S'
or $PRCtype = 'B'
do Write-Select-Columns
do Write-Select-Assignments
end-if
! Generate INSERT Procedure
if $PRCtype = 'I'
or $PRCtype = 'B'
do Write-Insert-Columns
do Write-Insert-Values
end-if
end-if
end-procedure
!**********************************************************************
!* Write Select Columns *
!**********************************************************************
begin-procedure Write-Select-Columns
let $rec = rpad($comment,71,'*')
write 1 from $rec
let $rec = $comment || '*'
let $rec = rpad($rec,9,' ')
let $rec = $rec || 'SQR Select Procedure generated by '
let $rec = $rec || $ReportID || ' - Tony DeLia'
let $rec = rpad($rec,70,' ')
let $rec = $rec || '*'
write 1 from $rec
let $rec = rpad($comment,71,'*')
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $rec = 'begin-procedure Select-' || lower($RECname)
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $rec = 'begin-select'
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $blank = ' '
let $synonym = lower($SYNchar) || '.'
let #idx = 0
while #idx < #P_ctr
let $FieldName = GENmtx.GENname (#idx)
lowercase $FieldName
let $rec = $synonym || $FieldName
write 1 from $rec
let #idx = #idx + 1
end-while
end-procedure
!**********************************************************************
!* Write Select Assignments *
!**********************************************************************
begin-procedure Write-Select-Assignments
let $rec = ' '
write 1 from $rec
let #idx = 0
let $blank = ' '
let $prefix = upper($SYNchar) || '_'
let $synonym = '&' || lower($SYNchar) || '.'
let #len = length($prefix) + 25 + #varsuf
while #idx < #P_ctr
let $FieldName = GENmtx.GENname (#idx)
lowercase $FieldName
let $VarType = GENmtx.GENtype (#idx)
let $Variable = GENmtx.GENvar (#idx)
let $Variable = $Variable || $prefix || $FieldName
let $Variable = rtrim($Variable,'#')
if $VARsuf = 'Y'
let $Variable = $Variable || '_' || upper($VarType)
end-if
let $rec = ' let ' || $Variable
let $rec = rpad($rec, #len, ' ')
let $rec = $rec || ' = ' || $synonym || $FieldName
write 1 from $rec
let #idx = #idx + 1
end-while
let $rec = ' '
write 1 from $rec
let $rec = ' from ps_' || lower($RECname) || ' ' || lower($SYNchar)
write 1 from $rec
do Build-Where
let $rec = ' '
write 1 from $rec
let $rec = 'end-select'
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $rec = 'end-procedure'
write 1 from $rec
let $rec = ' '
write 1 from $rec
end-procedure
!**********************************************************************
!* Build Where Clause *
!**********************************************************************
begin-procedure Build-Where
let #idx = 0
let $oper = 'where '
let $synonym = lower($SYNchar) || '.'
while #idx < #P_ctr
let $FieldKey = GENmtx.GENkey (#idx)
if $FieldKey = 'Y'
let $FieldName = GENmtx.GENname (#idx)
let $FieldType = GENmtx.GENtype (#idx)
lowercase $FieldName
uppercase $FieldType
let $FieldName = rpad($FieldName, 25, ' ')
let $FieldType = ' < ' || $FieldType || ' >'
let $rec = $oper || $synonym || $FieldName ||
'=' || $FieldType
write 1 from $rec
let $oper = ' and '
end-if
let #idx = #idx + 1
end-while
end-procedure
!**********************************************************************
!* Write Insert Columns *
!**********************************************************************
begin-procedure Write-Insert-Columns
let $rec = rpad($comment,71,'*')
write 1 from $rec
let $rec = $comment || '*'
let $rec = rpad($rec,9,' ')
let $rec = $rec || 'SQR Insert Procedure generated by '
let $rec = $rec || $ReportID || ' - Tony DeLia'
let $rec = rpad($rec,70,' ')
let $rec = $rec || '*'
write 1 from $rec
let $rec = rpad($comment,71,'*')
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $rec = 'begin-procedure Insert-' || lower($RECname)
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $rec = 'begin-sql'
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $rec = 'insert into ps_' || lower($RECname)
write 1 from $rec
let #idx = 0
let $blank = ' '
let $pad = rpad($blank,7,' ')
let $prefix = '('
let $suffix = ','
while #idx < #P_ctr
let $FieldName = GENmtx.GENname (#idx)
let #idx = #idx + 1
lowercase $FieldName
if #idx = #P_ctr
let $suffix = ')'
end-if
let $rec = $pad || $prefix || $FieldName || $suffix
write 1 from $rec
let $prefix = ' '
end-while
end-procedure
!**********************************************************************
!* Write Insert Values *
!**********************************************************************
begin-procedure Write-Insert-Values
let #idx = 0
let $sep = rpad($comment,4,' ')
let $blank = ' '
let $pad = rpad($blank,7,' ')
let $prefix = '('
let $suffix = ','
let $values = 'values '
uppercase $SYNchar
while #idx < #P_ctr
let $FieldName = GENmtx.GENname (#idx)
let $Variable = GENmtx.GENvar (#idx)
let $VarType = GENmtx.GENtype (#idx)
let $Variable = $Variable || $SYNchar || '_' || lower($FieldName)
let $Variable = rtrim($Variable,'#')
if $VARsuf = 'Y'
let $Variable = $Variable || '_' || upper($VarType)
end-if
let #idx = #idx + 1
if #idx = #P_ctr
let $suffix = ');'
end-if
let $rec = $values || $prefix || $Variable || $suffix
let $rec = rpad($rec, 45, ' ')
let $rec = $rec || $sep || $FieldName
write 1 from $rec
let $values = $pad
let $prefix = ' '
end-while
let $rec = ' '
write 1 from $rec
let $rec = 'end-sql'
write 1 from $rec
let $rec = ' '
write 1 from $rec
let $rec = 'end-procedure'
write 1 from $rec
let $rec = ' '
write 1 from $rec
end-procedure
!**********************************************************************
!* Process Record *
!**********************************************************************
begin-procedure Process-Record
let #P_ctr = 0
clear-array name=GENmtx
do REC-Func($RECname, 'Y', #RECctr)
let #ctr = 0
while #ctr < #RECctr
do REC-Func-Get(#ctr, $rec, $fld, $sub, #lev)
if $sub <> 'Y'
let $FLDrec = $rec
let $FLDname = $fld
do Get-Field-Attributes
do Write-Array
end-if
let #ctr = #ctr + 1
end-while
end-procedure
!**********************************************************************
!* Get Field Attributes *
!**********************************************************************
begin-procedure Get-Field-Attributes
begin-select
f1.longname
f1.shortname
f1.length
f1.decimalpos
f1.fieldtype
let $P_longname = &f1.longname
let $P_shortname = &f1.shortname
let #P_length = &f1.length
let #P_decimalpos = &f1.decimalpos
let #P_fieldtype = &f1.fieldtype
let #pos = (#P_fieldtype * 4) + 1
let #var = #P_fieldtype + 1
let $P_fieldtype = rtrim(substr($TypeString, #pos, 4),' ')
let $P_variable = substr($VarString, #var, 1)
let $P_fieldname = $FLDname
do Get-Key-Indicator
let $P_key = $FieldKey
from psdbfield f1
where f1.fieldname = $FLDname
end-select
end-procedure
!**********************************************************************
!* Get Key Indicator *
!**********************************************************************
begin-procedure Get-Key-Indicator
let $FieldKey = ' '
begin-select
k1.fieldname
let $FieldKey = 'Y'
from pskeydefn k1
where k1.recname = $FLDrec
and k1.fieldname = $FLDname
and k1.indexid = '_'
end-select
end-procedure
!**********************************************************************
!* Write Array *
!**********************************************************************
begin-procedure Write-Array
let GENmtx.GENname (#P_ctr) = $P_fieldname
let GENmtx.GENvar (#P_ctr) = $P_variable
let GENmtx.GENtype (#P_ctr) = $P_fieldtype
let GENmtx.GENkey (#P_ctr) = $P_key
let #P_ctr = #P_ctr + 1
end-procedure
!**********************************************************************
!* Include Members: *
!**********************************************************************
#Include 'tdsub.sqc' !TD SubRecord Analyzer
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'datetime.sqc' !Routines for date and time formatting
!Init-DateTime procedure
!**********************************************************************
!* End of Program *
!**********************************************************************
|
| This concludes another SQRTOOLS
page... |
 |
This completes another section of SQRTOOLS.COM... and the "Coral
Reef" scene is complete... |
| And here's the complete
"Coral Reef"... |
|
I would appreciate any feedback you may have on this site.
Send mail to tdelia@erols.com or click on the
Octopus. |
Tony DeLia - Updated December 15, 2000
|
