New Page 1

More Routines...

I enjoyed building the Rain Forest... So... let's build another one!

Here's another simple, but useful, SQR routine. This routine replaces a table alias in a Dynamic SQL string with a new alias. This allows the string to be used in another SQL statement in your program. The SQL criteria is now duplicated!

Dynamic SQL Strings... A practical example...

Consider PeopleSoft's Standard Payroll Input procedure (PAYINIT.SQC)... Based on user entered parameters a dynamic SQL string is generated... this string variable is called $SlctCalendar... this dynamic SQL string is then utilized in a subsequent select statement in the SQR...

$SlctCalendar contains the SQL criteria including the appropriate "A." table alias. PeopleSoft requires the "A." alias to be used when using the PAYINIT.SQC #Include file. Most commonly you'll find the $SlctCalendar appended with a select against PS_PAY_CALENDAR.

Examining the $SlctCalendar Variable...

The $SlctCalendar variable could contain something like:

     A.RUNID = 'ABC'

          or it could contain...

    A.COMPANY    = '100'
AND A.PAYGROUP   = 'BWK'
AND A.PAY_END_DT = '22-JUL-1999'

It is then used in a SQL Select Statement as follows:

begin-select

a.company
a.paygroup
a.pay_end_dt
..etc..

from ps_pay_calendar     a
where a.pay_confirm_run = 'Y'
and [$SlctCalendar]

end-select

You may be wondering why you may need to change a table alias... if so here's a brief explanation...

Why use another table alias?

Consider the following 2 SQL Selects

Select #1

begin-select
a.company
...
from ps_pay_calendar a
where [$SlctCalendar]

Select #2 (later in the same program)

begin-select
a.company
...
from ps_pay_line a
where [$SlctCalendar]

This produces an SQR Error Message!

SQR does not allow the duplicate synonym of &a.company to exist.

Using an alternate alias (such as B.) would be required... &b.company would work nicely. That's where this simple SQR routine comes in... it will copy the SQL criteria into a new variable using a different table alias...

Some Rain Forest commentary... then the TDALIAS.SQC routine...

This portion of the rain forest shows a waterfall to the left... a monkey in a tree... a pink bird searching for food... and a quail-like creature pecking away at the forest floor... Now let's move forward and examine the TDALIAS.SQC routine to perform our required function... notes are included in the source code explaining usage and input/output parameters...

TDALIAS.SQC - Change table alias in dynamic SQL variable

!**********************************************************************
!*                                                                    *
!*       MODULE:  TDALIAS.SQC                                         *
!*       AUTHOR:  TONY DELIA.                                         *
!*         DATE:  08/08/96.                                           *
!*       SYSTEM:  TD SQR UTILITY SERIES.                              *
!*         DESC:  SQL STRING ALIAS REPLACEMENT.                       *
!*         NOTE:  THE EXAMPLE SHOWN BELOW REUSES THE PEOPLESOFT       *
!*                STANDARD PAY CALENDAR SELECTION SQL STRING FOR      *
!*                SUBSEQUENT SQL SELECTION IN THE PROGRAM. THIS IS    *
!*                USEFUL WHEN PROCESSING MULTIPLE SQL STATEMENTS      *
!*                (SELECT, UPDATE, DELETE, ... ) THAT REQUIRE THE     *
!*                IDENTICLE SELECTION CRITERIA. SIMPLY REPLACE THE    *
!*                ALIAS TO THAT OF YOUR TABLE.                        *
!*                                                                    *
!**********************************************************************
!*                                                                    *
!*        USAGE:  do Alias-SQL('A.', 'pc.', $SlctCalendar, $newSQL)   *
!*                .                                                   *
!*                .                                                   *
!*                #Include 'tdalias.sqc'                              *
!*                                                                    *
!**********************************************************************
!*                                                                    *
!*       SAMPLE:  begin-SQL                                           *
!*                update ps_pay_calendar pc                           *
!*                   set pc.gl_interface_run = 'Y'                    *
!*                 where [$newSQL]                                    *
!*                end-SQL                                             *
!*                                                                    *
!**********************************************************************
!*                                                                    *
!*        PARMS:  $oldALIAS     => Old Alias in SQL String            *
!*                $newALIAS     => New Alias in SQL String            *
!*                $oldSQL       => SQL String to convert              *
!*                                                                    *
!*      RETURNS:  $newSQL       => New SQL String (converted)         *
!*                                                                    *
!**********************************************************************
!*                                                                    *
!*        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                *
!*                                                                    *
!**********************************************************************

begin-procedure Alias-SQL($oldALIAS, $newALIAS, $oldSQL, :$newSQL)

let $newSQL  = ''
let #pos-a   = 1
let #pos-b   = 0

while #pos-b < length($oldSQL)

   let #pos-b = instr($oldSQL, $oldALIAS, #pos-a)

   if #pos-b  = 0
      let #pos-b = length($oldSQL) + 1
   end-if

   if #pos-a < #pos-b
      let $newSQL = $newSQL ||
          substr($oldSQL, #pos-a, #pos-b - #pos-a)
   end-if

   if #pos-b < length($oldSQL)
      let $newSQL = $newSQL || $newALIAS
      let #pos-a  = #pos-b + length($oldALIAS)
   end-if

end-while

display ' '
display 'Alias SQL Change - TDALIAS.SQC'
display ' '
display 'Old SQL: '                  noline
display $oldSQL
display 'New SQL: '                  noline
display $newSQL
display ' '

end-procedure

!**********************************************************************
!*       End of Program                                               *
!**********************************************************************

This concludes our session...

This completes another section of SQRTOOLS.COM... As you may have guessed our second "Rain Forest" is complete! This took a couple evenings to complete... I was pretty happy with the way it turned out... but I don't think I'll make a third!

Another complete "Rain Forest"...

p2rnfst0.jpg (91165 bytes)

Feedback

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 July 22, 1999