Suggestions for this SQL???

Results 1 to 3 of 3

Thread: Suggestions for this SQL???

  1. #1
    Join Date
    Dec 1969

    Default Suggestions for this SQL???

    Hi again,<BR><BR>I have an SQL question that seems to work in DB2 but doesn&#039t work with ASP and VBScript and Access for some reason. I&#039m trying to limit the number of records that I have to go through for a LIKE query, so I&#039d like to create a temporary table to query. Something along the following lines:<BR><BR>SELECT Temp.*<BR>FROM (SELECT *<BR>FROM TableX<BR>WHERE Location=&#039x&#039 and Name=&#039y&#039) AS Temp<BR>WHERE Hobbies LIKE &#039%Jogging%&#039<BR><BR>This type of query works in DB2 but I keep getting a syntax error when trying to do this in my ASP page. Does anyone know of a similar query that might do the same thing (ie, limit the number of records that the LIKE needs to be performed on?)<BR><BR>Thanks!<BR>Dave<BR>

  2. #2
    Branavan K Guest

    Default RE: Suggestions for this SQL???

    I don&#039t know if this is what you are looking for. <BR><BR>SELECT FROM TableX<BR>WHERE (Location=&#039x&#039 and Name=&#039y&#039) AND (Hobbies LIKE &#039%Jogging%&#039)<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default RE: Suggestions for this SQL???

    Here is my thought that you can consider:<BR>Access uses Jet SQL which doesn&#039t recognize:<BR>SELECT ....FROM(SELECT .....) etc.<BR>So, what I&#039ll do is:<BR>1. Create a query first:<BR> SELECT * FROM TableX WHERE LOCATION =&#039x&#039 AND Name =&#039y&#039;<BR> (don&#039t forget the semicolon at the end).<BR> Save this query as qry1.<BR><BR>2. Now, create another query based on qry1:<BR> SELECT * FROM qry1 <BR> WHERE Hobbies LIKE &#039*Jogging*&#039;<BR> (I think Access uses * instead of %, I&#039m not sure).<BR> Save this query as qry2.<BR> This qry2 query will give the same result you want.<BR><BR>I hope that helps you.<BR><BR>John Weeflaar

Posting Permissions

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