RE: [sqr-users] Dynamic Query Variable Error

["the dragon" <ceprn@hotmail.com>]

Violet,

I think the problem is the splats (*) in the where clause D.CRSE_ATTR = 
'*WDS' AND D.CRSE_ATTR_VALUE = '*OPEN' .  I believe Oracle views the splats 
as wild cards, in which case you wouldn't be able to compare with an equal 
sign.

hth.  let me know.

peace,
clark


PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul. Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent. Be careful lest you let other people 
spend it for you."

Carl Sandburg
(1878 - 1967)

----Original Message Follows----

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

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/


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