Re: Dynamic Query Variable Error

[Cameron Smyth <csmyth@IDIRECT.COM>]

Try this:

Assigned the ascii value of a quote to a variable
        LET $Q = chr(39)
then concatenate that variable where you would normaly put a quote
                Let $Unit = '(C.BUSINESS_UNIT = ' || $q || '10300' || $q ||
' or
                                C.BUSINESS_UNIT = ' || $q || '10301'|| $q ||
' )'

Cameron

Violet Martin wrote:

>      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.