RE: [sqr-users] RE: SQR problem

[Sanjay Sambhe <ssambhe@cisco.com>]

 thanks all, for your valuable information

-----Original Message-----
From: sqr-users-bounces+ssambhe=cisco.com@sqrug.org
[mailto:sqr-users-bounces+ssambhe=cisco.com@sqrug.org] On Behalf Of White .
Denise
Sent: Tuesday, January 02, 2007 7:58 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] RE: SQR problem

I believe that this is necessary because the program would not compile with
a variable table name in the BEGIN-SELECT (the variable value is not set
until after the program has begun execution).  Therefore, it is necessary to
'trick' the compiler by providing a valid table name to be used at compile
time.  Once the variable value has been set, the new table name will be in
effect.

Denise M. White
EBS Programmer/Analyst III
Dynamics Research Corporation
------------------------------

Message: 2
Date: Tue, 2 Jan 2007 03:49:40 -0600
From: "Jim Womeldorf" <jwomeldo@fastenal.com>
Subject: RE: [sqr-users] SQR problem
To: "This list is for discussion about the SQR database reporting
        language        from Hyperion Solutions." <sqr-users@sqrug.org>
Message-ID: <42E50BB7EAB3764E8654A54710243EC0071F72AB@excmb01.backup>
Content-Type: text/plain;       charset="US-ASCII"

This is a new one to me.  
I have not found any documentation about this but running the following
program snippet sheds some light on the subject:

Begin-Program
show 'Here I am'
let $substitute = 'xlattable'       !Example 1
!let $substitute = 'psrecfield'     !Example 2
do demo
show 'done'
End-Program

begin-procedure demo
begin-select
count(fieldname) &c
    show 'count ' &c
from [xlattable : $substitute]
end-select
end-procedure demo

-----------------
If the program is run as shown then the count is 10965 which is indeed the
count of our records in xlattable.

If the program is run as with the 'Example 2' line active then the count is
198812 which is correct for psrecfield.

Apparently the $ value after the colon is substituted for the table name
shown, which, by the way, must be a valid table name.

-----------------
The code with the colon in it is, interestingly enough, compiled at
compile-time using the table name to the left of the colon and then is
compiled again at run-time using the value of $substitute.  The only purpose
of the compile-time compile that comes to mind is that it might provide an
'early warning' if there is something fundamentally wrong with the
statement.  Otherwise it is necessary to wait until run-time to find that
out.  

Jim

-----Original Message-----
From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of
Sanjay Sambhe
Sent: Monday, January 01, 2007 11:25 PM
To: 'This list is for discussion about the SQR database reporting language
from Hyperion Solutions.'
Subject: [sqr-users] SQR problem

Hi all,
 
while debugging, I have came accross one statement in SQR i.e
[PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name].
Here both are the record names. $e.cs_event_view_name is view in PS which is
dynamically selected in another procedure.
Can anybody please explain what is the logic behind this?
 
BEGIN-SELECT
X.EMPLID                        &x.emplid
X.CS_EVENT_TYPE                &x.cs_event_type
TO_CHAR(trunc(X.CS_EVENT_ENTRY_DT),'YYYY-MM-DD')
&x.cs_event_entry_dt
TO_CHAR(trunc(X.CS_EVENT_EFFDT),'YYYY-MM-DD')
&x.cs_event_effdt
X.CS_PRIORITY_FLAG              &x.cs_priority_flag
X.CS_EVENT_PARAM                &x.cs_event_param
  show 'Processing Employee: ' &x.emplid ', Event: ' &x.cs_event_type ',
Effdt: ' &x.cs_event_effdt
  show '&x.cs_priority_flag ' &x.cs_priority_flag ',&x.cs_event_param '
&x.cs_event_param
  do Insert-Employee-Event
FROM
         [PS_CS_EMPL_EVT_T01 X : $e.cs_event_view_name] WHERE
  not exists (select * from ps_cs_wr_tbl where emplid = x.emplid) and (
!** entire line for Transition project **
        (X.CS_EVENT_EFFDT between trunc(SYSDATE)-{RECENCY_DAYS} and
trunc(SYSDATE)+#future_days)
OR
        (X.CS_EVENT_ENTRY_DT >= trunc(SYSDATE)-{RECENCY_DAYS} AND 
        X.CS_EVENT_EFFDT <= trunc(SYSDATE)+#future_days))  !** ) For
Transition project ** END-SELECT
 
 
 
Thanks and Regards,
ss

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users