[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

Re: Dynamic variables



Jeff,

What you are seeing is dynamic table name substitution. Here's what the book 
says:

"You can use dynamic query variables to specify table names in the FROM clause 
of a SELECT paragraph. Use the following format to provide an actual (static) 
table name for SQR's scan phase.
  [static_table : $txt_var}
When the program begins executing, the value of the $txt_var is used as the 
table name. The columns defined for the dynamic table must match the columns 
defined for the static table exactly.
Following is an example of specifying a table name dynamically in a FROM clause:
  begin-select
  name, city, state, zip
     ... do processing ...
  from [customers : $tablename]
  where ....
  end-select
SQR uses the "customers" table during the initial program scan and 
initialization. When the report executes, the value of $tablename is used to 
recompile the query and select the data. Use the ON-ERROR argument to the 
BEGIN-SELECT command to trap errors in table names, as described in the next 
section."

Hope this clears up your confusion :-)

Peter Clark
PeopleSoft Support
Veterans Affairs Canada

>>> Jeffrey Bedell <jkbedell@AIS.SYR.EDU> 2000/08/31 1:22:28 pm >>>
Greetings fellow users.  I avidly read all of the postings hoping to
find some new pearl of SQR knowledge but usually never post.  Well, I am
finally stumped.  This is not so much a show stopper as just a curiosity
item.  In the Peoplesoft delivered SQR ben100.sqr there is a dynamic
variable in a select statement that I have never seen before and cannot
find any documentation that describes what it resolves to.  The variable
is set in the following code:

  move 'PS_LIFE_ADD_BEN D' to $tblname
  move 'PS_LIFE_ADD_BEN D2' to $tblname2
  MOVE 'Insert-Benefit-Records' TO $WHERE_FROM
  do Insert-Benefit-Records
  move 'PS_DISABILITY_BEN D' to $tblname
  move 'PS_DISABILITY_BEN D2' to $tblname2
  do Insert-Benefit-Records

And is used in the following select statement:

BEGIN-SELECT
...........
FROM  [PS_DISABILITY_BEN D : $tblname]
where D.EMPLID           = $current_emplid
  and D.EMPL_RCD#        = #current_empl_rcd
  and D.COVERAGE_ELECT <> 'T'
  and (D.COVERAGE_END_DT > $AsOfDate
       or
       D.COVERAGE_END_DT IS NULL)
  and D.EFFDT = (SELECT MAX(D2.EFFDT)
                 from [PS_DISABILITY_BEN D2 : $tblname2]
                 where D.EMPLID    = D2.EMPLID
                   and D.EMPL_RCD# = D2.EMPL_RCD#
                   and D.PLAN_TYPE = D2.PLAN_TYPE
                   and D2.EFFDT   <= $AsOfDate)

What I want to know is what does the text inside the brackets resolve
to?  And what is the meaning of the colon(:)?  Is it an or statement?
And if so, does it mean select from the first and then from the second
if it is different from the first?  Got me confused!  Would appreciate
anyone who could shed some light on this for me.
Thanks - Jeff Bedell - Syracuse University