RE: [sqr-users] Connect by clause problem

[Kajal.Somaiya@bbh.com]

I think it is searching for an environmental variable $folder to be 
defined in unix environment while trying to execute the SQR code.
may be you should try out without using braces, I am not very sure about 
it though.




"Ray, Christine Marie" <chmray@indiana.edu> 
Sent by: sqr-users-bounces+kajal.somaiya=bbh.com@sqrug.org
11/21/2005 12:55 PM
Please respond to
"This list is for discussion about the SQR database reporting   language 
from Hyperion Solutions." <sqr-users@sqrug.org>


To
"This list is for discussion about the SQR database reportinglanguage from 
Hyperion Solutions." <sqr-users@sqrug.org>
cc

Subject
RE: [sqr-users] Connect by clause problem






The SQL does work in the UNIX environment.  The problem seems to be the
substitution of the dynamic variable in the SQL. 

The two different enviroments are a Production environment and a testing
environment.  We use RCS for revision control.  The revision checked
into Production was checked out and then I tried to run it without
changing anything.  I received the error message :

(SQR 5528) ORACLE OCIStmtExecute error 1788 in cursor 1:
   ORA-01788: CONNECT BY clause required in this query block
SQL:  SELECT level, ro_tree_entry_lbl, ro_childobjtyp_cd  from
      dss.ie_mstr_cat_alph_v
Error on line 28:
   (SQR 3716) Error in SQL statement.

If I put in a text string instead of [$folder] the code compiles and
runs.  Any suggestions as to what is going on with the dynamic variable?

Christine

-----Original Message-----
From: sqr-users-bounces+chmray=indiana.edu@sqrug.org
[mailto:sqr-users-bounces+chmray=indiana.edu@sqrug.org] On Behalf Of
Paul Hoyte
Sent: Friday, November 18, 2005 7:56 PM
To: 'This list is for discussion about the SQR database
reportinglanguagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] Connect by clause problem

Marie:

First make sure that the SQL statement itself works in the UNIX env.
You
didn't specify which database you are using in the 2 environments but if
it's the same release of Oracle, they should both work fine.  Then use
the
compiler directives (#if commands) to determine which environment is
being
used and then execute that specific block of SQL for the environment.

#IF value comparison_operator value
  {SQR Commands}
[#ELSE
                 {SQR Commands}]
#END-IF

 


Paul Hoyte
(248) 752-6970
www.sharidionne.com
pa_hoyte@si-proservices.com

-----Original Message-----
From: sqr-users-bounces+pi_hoyte=si-proservices.com@sqrug.org
[mailto:sqr-users-bounces+pi_hoyte=si-proservices.com@sqrug.org] On
Behalf
Of Ray, Christine Marie
Sent: Friday, November 18, 2005 4:41 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Connect by clause problem

I have some sqr code that works in one environment but will not compile
to
run from the UNIX command line.

 

Any ideas? No one in my organization has any ideas.

 

begin-program

columns 2 7 12 17 22 27 32 37 42 47

do get_input

do main 

end-program

 

begin-procedure main

begin-select

level &level 

            Move &level to #level 

            use-column #level

            Print #level (+1) edit 888

!lpad(level,2*level -1)||' '|| 

ro_tree_entry_lbl &label (, +2) 

ro_childobjtyp_cd                               &child (, +2) 

from dss.ie_mstr_cat_alph_v 

start with ro_tree_entry_lbl like [$folder]

connect by prior ro_child_tech_nm = ro_parnt_tech_nm

end-select

end-procedure ! main

 

begin-procedure get_input

Input $folder

Let $folder = '''' || $folder || ''''

end-procedure ! get_input

 

ERROR message:

 

Hyperion SQR Server 8.3.1

Copyright (c) 1994-2005 Hyperion Solutions Corporation.  All Rights
Reserved.

 

(SQR 5528) ORACLE OCIStmtExecute error 1788 in cursor 1:

   ORA-01788: CONNECT BY clause required in this query block

SQL:  SELECT level, ro_tree_entry_lbl, ro_childobjtyp_cd  from

      dss.ie_mstr_cat_alph_v

 

Error on line 28:

   (SQR 3716) Error in SQL statement.

 

 

Errors were found in the program file.

 

SQR: Program Aborting.

 

 

 

 

 

 

 

Christine Ray

Indiana University Bloomington

FMS Administration

Poplars 833

 

812-856-2411

 

_______________________________________________
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




-----------------------------------------
*************************** IMPORTANT NOTE
***************************** The opinions expressed in this message
and/or any attachments are those of the author and not necessarily
those of Brown Brothers Harriman & Co., its subsidiaries and affiliates
("BBH"). There is no guarantee that this message is either private or
confidential, and it may have been altered by unauthorized sources
without your or our knowledge. Nothing in the message is capable or
intended to create any legally binding obligations on either party and
it is not intended to provide legal advice. BBH accepts no
responsibility for loss or damage from its use, including damage from
virus. ****************************************************************
********

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