Re: Dynamic Query Variable Error

[Violet Martin <vmartin@COURTS.STATE.VA.US>]

     Thanks for everyones help.

     The double quotes did not work on my database (Oracle).
     But the single double quotes did work.


______________________________ Reply Separator _________________________________
Subject: Re: Dynamic Query Variable Error
Author:  mrisman (mrisman@PAINEWEBBER.COM) at internet,mime
Date:    10/31/00 12:35 PM


Violet,

The issue here is that when you try to specify a quoted string (your "SQR string
") - in your case, '(C.BUSINESS_UNIT = '10300' or C.BUSINESS_UNIT = '10301')' -
SQR will look for the beginning single quote and the ending single quote.  In yo
ur code, it thinks the string ends after the first = sign.  You need to find som
e other way of communicating the 10300 string (along with 10301, 10400 and 10401
) to your database.  As has been suggested, you can use double quotes if your da
tabase allows it, since SQR distinguishes between single and double quotes; or y
ou can use a "double single quote" (two single quotes together) to tell SQR that
 the SQR string continues, and there is a single quote or apostrophe inside the
string, e.g.:


Double quotes:
                                Let $Unit = '(C.BUSINESS_UNIT = "10300" or
                                                C.BUSINESS_UNIT = "10301")'


Double single quotes:
                                Let $Unit = '(C.BUSINESS_UNIT = ''10300'' or
                                                C.BUSINESS_UNIT = ''10301'')'


(I apologize if some mail readers make both of these lines look identical)

Hope this helps,
- Mark

                -----Original Message-----
                From:   Violet Martin [mailto:vmartin@COURTS.STATE.VA.US]
                Sent:   Tuesday, October 31, 2000 11:08 AM
                To:     SQR-USERS@list.iex.net
                Subject:        Dynamic Query Variable Error

                     I am trying to use a Dynamic Query Variable in my select cl
ause to
                     only pick out specific Business Units, depending on a value
 in an
                     input value.  I am getting the following error:

                        Error on line 152:
                        (SQR 4008) Unknown function or variable in expression: 1
0300'
                                                C.BUSINESS_UNIT = '10301')'
                     I know the error has to do with the ', but I can't find the
 correct
                     syntax in any books.

                     The code from my sqr is:
                     If $Mags = 'Y'
                                Let $Unit = '(C.BUSINESS_UNIT = '10300' or
                                                C.BUSINESS_UNIT = '10301')'
                        Else
                                Let $Unit = '(C.BUSINESS_UNIT = '14000' or
                                                C.BUSINESS_UNIT = '14001')'
                     End-If


                     begin-SELECT
                     A.NAME
                     FROM  PS_PERSONAL_DATA A,
                           PS_JOB           C
                     WHERE
                       C.EMPLID     = A.EMPLID
                       AND C.EFFDT =
                           (SELECT MAX(H.EFFDT)
                            FROM   PS_JOB H
                            WHERE  H.EMPLID    = C.EMPLID
                              AND  H.EMPL_RCD# = C.EMPL_RCD#
                              AND  H.EFFDT    <= $AsOfDate)
                       AND C.EFFSEQ =
                           (SELECT MAX(I.EFFSEQ)
                            FROM   PS_JOB I
                            WHERE  I.EMPLID    = C.EMPLID
                              AND  I.EMPL_RCD# = C.EMPL_RCD#
                              AND  I.EFFDT     = C.EFFDT)
                       AND C.EMPL_STATUS IN ('A','P','L')
                       AND [$Unit]
                       AND C.SCV_CLE_REQ_CREDTS > 0
                       AND to_char(C.SCV_CLE_END_DATE,'YYYY') <= $YYAsOfDate
                        ORDER BY SUBSTR(C.DEPTID,3,2), C.LOCATION, A.NAME

                     end-SELECT
                     end-procedure


                     Thanks for any help you can give.