Re: Using LIKE in Begin-Select

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

I assume it is the first statement which does not work?  It looks like, in the 
first statement, you have quotes embedded in your string.  The quotes would 
then be part of what gets compared to your FIRST_NAME_SRCH field, and you 
wouldn't have a match unless the data in the field had quotes in it as well.

In the second statement you would need the extra quotes because you are 
effectively specifying literal SQL.  In the first statement, you are building a 
string to be used as a bind variable.  The quotes would not be necessary here.

- Mark

                -----Original Message-----
                From:   Anne-Marie Matula [mailto:amatula@OIT.UMASS.EDU]
                Sent:   Thursday, November 30, 2000 2:19 PM
                To:     SQR-USERS@list.iex.net
                Subject:        Using LIKE in Begin-Select

                Hi all,

                I have a select statement that works if I use the LIKE in a 
dynamic clause
                but doesn't if I simply place try to use it in the statement.
                Any ideas why?


                Here's the 2 statements:

                LET $um_where_fname = ''''||$um_fname1||'%'''
                Begin-Select DISTINCT
                cvsrchpr29.EMPLID

                FROM PS_PERSONAL_DATA cvsrchpr29,
                     PS_CV_NAMES_TMP cvsrchpr30
                Where
                     cvsrchpr29.EMPLID           =   cvsrchpr30.EMPLID      and
                     cvsrchpr30.LAST_NAME_SRCH   =   $last_name_srch        and
                     cvsrchpr30.FIRST_NAME_SRCH LIKE $um_where_fname   and
                     cvsrchpr29.SEX              =   $sex                   and
                    [$um_where_birthdate]
                     NOT EXISTS (Select cvsrchpr31.EMPLID_SRCH from 
PS_CV_MATCH_TMP
                cvsrchpr31 where
                                        cvsrchpr31.CV_LEGACY_SID    = 
$cv_legacy_sid     and
                                        cvsrchpr31.EMPLID_SRCH      = 
cvsrchpr29.EMPLID  and
                                        cvsrchpr31.CV_MATCH3_NBR    = 1)

                ------------------------

                LET $um_where_fname = 'cvsrchpr30.FIRST_NAME_SRCH LIKE
                '''||$um_fname1||'%'''
                Begin-Select DISTINCT
                cvsrchpr29.EMPLID

                FROM PS_PERSONAL_DATA cvsrchpr29,
                     PS_CV_NAMES_TMP cvsrchpr30
                Where
                     cvsrchpr29.EMPLID           =   cvsrchpr30.EMPLID      and
                     cvsrchpr30.LAST_NAME_SRCH   =   $last_name_srch        and
                     [$um_where_fname ] and
                     cvsrchpr29.SEX              =   $sex                   and
                    [$um_where_birthdate]
                     NOT EXISTS (Select cvsrchpr31.EMPLID_SRCH from 
PS_CV_MATCH_TMP
                cvsrchpr31 where
                                        cvsrchpr31.CV_LEGACY_SID    = 
$cv_legacy_sid     and
                                        cvsrchpr31.EMPLID_SRCH      = 
cvsrchpr29.EMPLID  and
                                        cvsrchpr31.CV_MATCH3_NBR    = 1)
                Thanks!
                Have a great day!
                Anne-Marie

                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Anne-Marie Matula
                Student Information System Project
                Office of Information Technologies
                University of Massachusetts
                161 Whitmore Administration Building
                Amherst Massachusetts, 01003-8165
                Phone: 413/577-0685
                Fax: 413/545-2150