Re: Dynamic Query Variable Error

["Risman, Mark" <mrisman@PAINEWEBBER.COM>]

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 your code, it thinks the string ends after the first = sign.  You 
need to find some 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 database allows it, since SQR distinguishes between 
single and double quotes; or you 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 
clause 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: 
10300'
                                                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.