RE: [sqr-users] Dynamic Query Variable Error

["Violet Martin" <vmartin@vccs.edu>]

Thanks to everyone who gave their input.  This is the solution that
worked:
        Let $institution = 'AND C.INSTITUTION = ''' ||
UPPER(&RUNCNTL.INSTITUTION)|| ''''

And replace the whole line with

[$institution]


Violet A. Martin
Systems Analyst
Application Support Center
Virginia Community College System
(804) 423-6746
-----Original Message-----
From: sqr-users-bounces+vmartin=vccs.edu@sqrug.org
[mailto:sqr-users-bounces+vmartin=vccs.edu@sqrug.org] On Behalf Of
Alexander, Steven
Sent: Monday, April 03, 2006 2:06 PM
To: 'This list is for discussion about the SQR database
reportinglanguagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] Dynamic Query Variable Error

Look at the SQL statement that was sent to Oracle.  It says "AND
C.INSTITUTION = $institution".  Oracle doesn't know what $institution
is.
It doesn't even know that $institution is a variable within your SQR
program.  You cannot have an SQR variable within the text of a dynamic
variable.

You wrote:

AND C.INSTITUTION = [$institution]

That was an unnecessary use of a dynamic variable; after all, it worked
when
you wrote:

AND C.INSTITUTION = $institution

Or, you could write:

Let $institution = 'AND C.INSTITUTION = ''' ||
UPPER(&RUNCNTL.INSTITUTION)
|| ''''

And replace the whole line with

[$institution]




-----Original Message-----
From: Violet Martin [mailto:vmartin@vccs.edu] 
Sent: Monday, April 03, 2006 7:21 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Dynamic Query Variable Error

I am receiving the following error message when I use a Dynamic Query
Variable in my sqr.  The sql runs fine when it is coded C.INSTITUTION =
$INSTUTITION, without the Dynamic Query Variable.

It looks like the dynamic SQL was built correctly, but I cannot
determine what ORA-00911: invalid character is not being built
correctly.

 

 

$start_dt 01-JUL-2005  $end_dt 30-JUN-2006 $institution RC278
$ALL_or_ONE $institution
Report Date 04/03/2006
(SQR 5528) ORACLE OCIStmtExecute error 911 in cursor 5:
   ORA-00911: invalid character
SQL:  SELECT A.EMPLID, A.ACAD_CAREER, A.INSTITUTION, A.STRM,
A.CRSE_GRADE_OFF,
      A.CLASS_NBR, A.SESSION_CODE, A.STDNT_ENRL_STATUS, B.NAME,
C.ACAD_CAREER 
      FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C,
PS_CLASS_ATTRIBUTE D
      WHERE A.EMPLID = B.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT =
(SELECT
      MAX(B_ED.EFFDT) FROM PS_NAMES B_ED WHERE B.EMPLID = B_ED.EMPLID
AND
      B.NAME_TYPE = B_ED.NAME_TYPE AND B_ED.EFFDT <= :1) AND
A.ACAD_CAREER =
      C.ACAD_CAREER AND A.INSTITUTION = C.INSTITUTION AND A.STRM =
C.STRM AND
      A.CLASS_NBR = C.CLASS_NBR AND C.SESSION_CODE = A.SESSION_CODE AND
      C.END_DT BETWEEN :2 and :3 AND C.ENRL_TOT > 0 AND C.CRSE_ID =
D.CRSE_ID
      AND C.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND C.STRM = D.STRM AND
      C.SESSION_CODE = D.SESSION_CODE AND C.CLASS_SECTION =
D.CLASS_SECTION
      AND D.CRSE_ATTR = '*WDS' AND D.CRSE_ATTR_VALUE = '*OPEN' AND
      C.INSTITUTION = $institution order by b.name, a.emplid 

 
Error on line 203:
   (SQR 3722) Could not set up cursor.

 

 

This is how I am defining the variable:

begin-procedure Get-Values

            let $institution = UPPER(&RUNCNTL.INSTITUTION)

            let $start_dt                               =
&RUNCNTL.START_DATE

            let $end_dt                                            =
&RUNCNTL.END_DATE

            let $ALL_or_ONE = $institution

 

This is the where clause in the sqr:

                                                            

  FROM PS_STDNT_ENRL A, PS_NAMES B, PS_CLASS_TBL C, PS_CLASS_ATTRIBUTE D

  WHERE A.EMPLID = B.EMPLID

     AND B.NAME_TYPE = 'PRI'

     AND B.EFFDT =

        (SELECT MAX(B_ED.EFFDT) FROM PS_NAMES B_ED

        WHERE B.EMPLID = B_ED.EMPLID

          AND B.NAME_TYPE = B_ED.NAME_TYPE

          AND B_ED.EFFDT <= $asoftoday)

     AND A.ACAD_CAREER = C.ACAD_CAREER

     AND A.INSTITUTION = C.INSTITUTION

     AND A.STRM = C.STRM

     AND A.CLASS_NBR = C.CLASS_NBR

     AND C.SESSION_CODE = A.SESSION_CODE

     AND C.END_DT BETWEEN $START_DT and $END_DT

     AND C.ENRL_TOT > 0

     AND C.CRSE_ID = D.CRSE_ID

     AND C.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR

     AND C.STRM = D.STRM

     AND C.SESSION_CODE = D.SESSION_CODE

     AND C.CLASS_SECTION = D.CLASS_SECTION

     AND D.CRSE_ATTR = '*WDS'

     AND D.CRSE_ATTR_VALUE = '*OPEN'

     AND C.INSTITUTION = [$ALL_or_ONE]

     order by b.name, a.emplid

 

Any help would be greatly appreciated.

 

Violet A. Martin

Systems Analyst

Application Support Center

Virginia Community College System

(804) 423-6746

 

_______________________________________________
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