RE: [sqr-users] Dynamic Query Variable Error

["Jim Womeldorf" <jwomeldo@fastenal.com>]

Hi Violet,

I believe you are getting your error at compile time.  SQR has a bit of
trouble getting things correct when you have subqueries within a dynamic
SQL query.

Try the following:

let $blank = ''

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 [$blank] 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
 
I believe this will move the statement into the run time arena.

Jim

-----Original Message-----
From: sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+jwomeldo=fastenal.com@sqrug.org] On Behalf Of
Violet Martin
Sent: Monday, April 03, 2006 9: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