Re: [sqr-users] Dynamic Query Variable Error

["jiju vengal" <jijucvengal@gmail.com>]

Hi Violet....

You dont need to use the 'square brackets' at all....this statement would
just be fine....

*AND C.INSTITUTION = $ALL_or_ONE*
**
Dynamic SQL statements are not required when you are simply assigning the
column value to a variable....

That should work!

Regards,

Jiju Vengal.
Peoplesoft Practice Group
Cognizant Technology Solutions.





On 4/3/06, Violet Martin <vmartin@vccs.edu> wrote:
>
> 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