Results 1 to 2 of 2

Thread: WildCard in SQL

  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: WildCard in SQL

    Hi,

    I am developing a coldfusion page. I have the following query in my page:

    <cfquery name = "NewApprovalQuery2" datasource = "first_products">
    SELECT * from tbl_Travel
    WHERE
    (OrderNo is null)
    AND (approved = 'no'
    OR in_VFC = 'no')
    AND tbl_travel.traveler like
    (SELECT tbl_names.[LastName] FROM tbl_names
    WHERE LastName = '#GetAuthUser()#')

    ORDER BY

    <cfswitch expression="#sort#">
    <cfcase value="1">traveler, program</cfcase>
    <cfcase value="2">traveler desc, program</cfcase>
    <cfcase value="3">program</cfcase>
    <cfcase value="4">program desc</cfcase>
    <cfcase value="5">start_date, traveler, program</cfcase>
    <cfcase value="6">start_date desc, traveler, program</cfcase>
    <cfcase value="7">city, traveler, program</cfcase>
    <cfcase value="8">city desc, traveler, program</cfcase>
    </cfswitch>
    </cfquery>

    I would like to know how to put wildcard % characters around the nested query. I tried the following, but to no avail:

    <cfquery name = "NewApprovalQuery2" datasource = "first_products">
    SELECT * from tbl_Travel
    WHERE
    (OrderNo is null)
    AND (approved = 'no'
    OR in_VFC = 'no')
    AND tbl_travel.traveler like
    %(SELECT tbl_names.[LastName] FROM tbl_names
    WHERE LastName = '#GetAuthUser()#')%

    ORDER BY

    <cfswitch expression="#sort#">
    <cfcase value="1">traveler, program</cfcase>
    <cfcase value="2">traveler desc, program</cfcase>
    <cfcase value="3">program</cfcase>
    <cfcase value="4">program desc</cfcase>
    <cfcase value="5">start_date, traveler, program</cfcase>
    <cfcase value="6">start_date desc, traveler, program</cfcase>
    <cfcase value="7">city, traveler, program</cfcase>
    <cfcase value="8">city desc, traveler, program</cfcase>
    </cfswitch>
    </cfquery>


    Can someone help me with the syntax?


    Thanks in advance,
    geebee

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up Re: WildCard in SQL

    Hi,

    Use IN instead of like in Ur Query.

    SELECT * from tbl_Travel
    WHERE
    (OrderNo is null)
    AND (approved = 'no'
    OR in_VFC = 'no')
    AND tbl_travel.traveler IN
    (SELECT tbl_names.[LastName] FROM tbl_names
    WHERE LastName = '#GetAuthUser()#')

    ORDER BY
    <cfswitch expression="#sort#">
    <cfcase value="1">traveler, program</cfcase>
    <cfcase value="2">traveler desc, program</cfcase>
    <cfcase value="3">program</cfcase>
    <cfcase value="4">program desc</cfcase>
    <cfcase value="5">start_date, traveler, program</cfcase>
    <cfcase value="6">start_date desc, traveler,
    program</cfcase>
    <cfcase value="7">city, traveler, program</cfcase>
    <cfcase value="8">city desc, traveler, program</cfcase>
    </cfswitch>
    </cfquery>


    U can't use the Wild character '%' as u mentioned in Ur Query.

    The syntax is LIKE('%');
    SATHISH .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •