RE: [sqr-users] Dynamic database links

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

In Oracle, tables that are accessed via a database link are written in the
form "payruleids@kdev".  It may be different in other databases, but I
suspect you cannot have a space between the table name and the database
instance name.  Also, the SQR compiler wants to validate your fields, so it
needs to know a table with the same layout as your dynamically selected
table.  You might write the FROM clause as:

from [$table_at_db : payruleids@kdev] a

That tells SQR to use the copy of payruleids in the kdev database instance
to determine whether your fields are valid and what type they are.  If you
are not always connected to kdev (or any instance that includes payruleids),
you can identify the type of each field - but I forget the syntax.

-----Original Message-----
From: Bob Stone [mailto:bstone@fastenal.com]
Sent: Tuesday, April 05, 2005 2:55 PM
To: 'This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] Dynamic database links


I've got 2 different ideas for you.

First, maybe try letting $dblink default to something you know will work
before all the if statements (it's def on KDEV so 'let $dblink = 'KDEV' '
and then change it once you know what $kronos_db is).  That way it compiles
knowing that $dblink  will have a value.  As is, it looks like all the ifs
could fail, and then $dblink has no value/doesn't exist.  I'm speaking in
terms of possibility...even though you may NEVER have a value other than
those 4, there is no 'else !other ' statement that ensures the compiler that
$dblink is set in one of those checks.

Second, now I've never tried this, but maybe you could make the entire from
clause dynamically.  That way it will have to parse it at run time instead
of compile time?  maybe?  (i could be wrong on this...dynamic from clauses
isn't something I've tried).  Just a suggestion...something to try.  

let $from_clause = 'FROM payruleids ' || $dblink || ' a'

begin-select loops=1
a.name  
  Let $found_new_bu = 'Y'
[$from_clause]
where ...
end-select


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Debbie Larney
Sent: Tuesday, April 05, 2005 4:40 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Dynamic database links


Hello Everyone,

I was wondering if it was possible to dynamically define database links in
sqr. I am trying to access a vendor's database via a database link. I know
that the database link is set up properly because if I hardcode the link
name in my sql statement I am able to run my program to success. The problem
I have is that I want to define this value dynamically based on run control
parameters. I get compile errrors when I try this dynamically because the
database link variable name is not determined until after the program
compiles.

I presume I could define the tables in the database that I am executing my
program from but I really don't want to have to do that. Anyone have any
great ideas.

Snippets of my code and the errors from the log file are listed below.
Thanks in advance for any assistance
--Definition of the database link
if $kronos_db = 'KPRD'
      let $dblink   = {dblink_kprd}--This is defined gloablly
else
  if $kronos_db = 'KTST'
      let $dblink   = {dblink_ktst}--This is defined gloablly
  else
    if $kronos_db = 'KDEV'
        let $dblink   = {dblink_kdev}--This is defined gloablly
    else
      if $kronos_db = 'KSYS'
          let $dblink   = {dblink_ksys}--This is defined gloablly
 
      end-if
    end-if
  end-if
end-if 
--One of the procedures with a compile error
!---------------------------------------------------------------------------
--!
! Procedure  :  Check-New-BU
! Called From:  Get-RC-Params
! Description:  Checks to see if payrules already exist for the selected 
!               clone to bu
!---------------------------------------------------------------------------
--!
begin-procedure Check-New-BU
   move 'Check-New-BU' to $operation_name
   #ifdef debugf
      show 'Procedure: ' $operation_name
   #end-if

let $found_new_bu = 'N'

begin-select loops=1
a.name  

   Let $found_new_bu = 'Y'

   show 'Error: new BU payrules already exist = ' $new_bu

from payruleids [$dblink] a                  
where substr(a.name,1,5) = $new_bu 


End-Select

end-procedure Check-New-BU

error below notice that the dblink is not defined at compile time and not
part of the sql

SQR 5528) ORACLE OCIStmtExecute error 942 in cursor 32: 
ORA-00942: table or view does not exist 
SQL: SELECT a.name from payruleids 
Error on line 260: (SQR 3716) Error in SQL statement. 



******************************************************************** 
CONFIDENTIALITY NOTICE: 
The information contained in this e-mail is legally privileged and
confidential information intended only for the use of the individual or
entity to whom it is addressed.  If the reader of this message is not the
intended recipient, you are hereby notified that any viewing, dissemination,
distribution, or copying of this e-mail message is strictly prohibited.  If
you have received and/or are viewing this e-mail in error, please
immediately notify the sender by reply e-mail, and delete this e-mail from
your system.

Thank you. 
******************************************************************** 



_______________________________________________
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

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