Why won't my SQL work?

Results 1 to 9 of 9

Thread: Why won't my SQL work?

  1. #1
    Join Date
    Dec 1969
    Posts
    1,912

    Default Why won't my SQL work?

    Hi,<BR><BR>I have an SQL query 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
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Why won't my SQL work?

    Did you try my solution?<BR><BR>John Weeflaar

  3. #3
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Why won't my SQL work?

    Hi Dave, why don&#039t you let me know how you went wrong with your<BR>query. Here is my conclusion:<BR>Branavan solution and my solution should give the same result. My solution is more general as it can handle any kind of SELECT<BR>in your FROM clause, however it involves using saved query.<BR>Branavan solution is more specific to your need. You have TableX<BR>with at least 3 fields: Location, Name and Hobbies. Then his suggestion in my mind is correct. You don&#039t actually need to put<BR>your (SELECT ...) in the FROM clause. Here&#039s his solution (with a little modification)that should work:<BR>SELECT * FROM TableX<BR>WHERE (Location = &#039x&#039 AND Name = &#039y&#039)<BR>AND Hobbies LIKE &#039*Jogging*&#039<BR><BR>One thing I noticed is: <BR>there is no other hobbies that have similar word like Jogging.<BR>So, Hobbies LIKE &#039*Jogging*&#039 is equivalent to:<BR>Hobbies = &#039Jogging&#039.<BR><BR>John Weeflaar

  4. #4
    Join Date
    Dec 1969
    Posts
    1,912

    Default RE: Why won't my SQL work?

    Hi John,<BR><BR>Your assumption is correct. The DB table names that I included were just examples. I understand (and have used) the queries that have been suggested. What I&#039m really looking for is a way of optimizing the query in my original post for better performance. I thought that by generating a temporary table (as in DB2) and then querying that table, I would retrieve a smaller recordset. Since the DB2 query doesn&#039t work, I was looking for a way of doing something along the same lines. <BR><BR>I made a mistake in putting my same post on the board yesterday. I meant to post another message, but I wasn&#039t thinking! I may not have implemented your solution properly, but I can&#039t seem to query the first generated recordset with a second query as you suggested. That was what I meant to write!<BR><BR>-Dave


  5. #5
    Join Date
    Dec 1969
    Posts
    1,912

    Default RE: Why won't my SQL work?

    A quick addition:<BR><BR>What I&#039m really trying to do with the whole thing is some sort of full-text searching. I&#039ve got several attribute fields and then a big memo field in my access database that could contain words like, for example, jogging. This was the primary reason why I wanted to cut down on records - so that I didn&#039t have to search through so many memo fields which could potentially take a long time. <BR><BR>I&#039m not sure if there is a better way of searching through the memo field for key words (besides using the LIKE idea?)

  6. #6
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Why won't my SQL work?

    Hi Dave,<BR>I just wake up in the morning and see your message now. <BR>I will get back to you and see if I can do or learn something (which is also new for me).<BR>I&#039ve got a dreadful apostropes problem currently which is new<BR>for me, but gives me good experience.<BR>You can see it there also.<BR><BR>Thanks,<BR><BR>John Weeflaar

  7. #7
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Why won't my SQL work?

    Hi Dave, <BR><BR>I&#039ve been out for a while and back again now.<BR>OK.<BR>I&#039ll let you check first if my interpretation of your question<BR>is right:<BR>You want to create a big memo field which will contain a set of<BR>little memos. Each little memo contains data of a record.<BR>If my interpretation is right, then it is possible to<BR>create a function that can search for a word you want to find.<BR>The thing is the word you&#039re searching can be located in several places in the memo field. And it needs a way to link which record this word belongs, etc.<BR>Can you give me a little hint of your current tables and fields, and how you&#039re accessing the data in these tables and fields. Then we can think of a better way (if there is) to reduce the time to find the information needed.<BR><BR>Regards,<BR><BR>John Weeflaar

  8. #8
    Join Date
    Dec 1969
    Posts
    1,912

    Default RE: Why won'my SQL work?

    Hi John,<BR><BR>Thanks again for helping me out. Here&#039s a better explanation of the problem:<BR><BR>The table in Microsoft Access looks something like this:<BR><BR>Name Position Type ... Description<BR><BR>Just assume that each of the fields besides Description is of type Text. The Description field itself is of type Memo. So, if I have tons of records (say 10,000), you can see that it would take a long time to search through all of the records to see if there is a match in the Description field (especially if the description is very long). So, what I want to do is finish the query on Name, Position, Type etc. first and then, having pulled everything out that I need, do a query on the resulting table for Description:<BR><BR>SELECT Temp.*<BR>FROM (SELECT * <BR> FROM People<BR> WHERE Name=&#039Name&#039 AND Position=&#039Position&#039 AND Type=&#039Type&#039) AS Temp<BR>WHERE Temp.Description LIKE &#039%Sailing%&#039<BR><BR>Assume that the Description field is just made up of words that are separated by commas:<BR><BR>Sailing, rafting, running, swimming, etc.<BR><BR>Depending on what a person likes to do, that is entered in there.<BR><BR>Hope this gives a better idea of what I&#039m looking to do!<BR>-Dave<BR> <BR>

  9. #9
    Join Date
    Dec 1969
    Posts
    1,315

    Default RE: Why won'my SQL work?

    Hi Dave,<BR><BR>I&#039ve been thinking of a way using Memo. But I think the best<BR>way is to redesign your tables. You probably need at least<BR>2 tables. Just to make things simple, use the first table as the table you showed me.<BR>table1 contains fields: Name, Position, Type.<BR>Now, add 5 or more fields for hobbies. But use Number as the<BR>field data type (instead of Text).<BR>So after Type field, you would have Hobby1, Hobby2, Hobby3,<BR>Hobby4, Hobby5, (and more Hobby fields if needed).<BR>Again these 5 Hobby fields are of type Number instead of Text.<BR>Now, create another table called tblHobby.<BR>What you need is to fill in a complete hobbies you can think of.<BR>It may take 100 or even 200 hobbies, but it is worth for speed.<BR>So, the user is given a list of hobbies he can choose, rather than type in himself (like in the Memo field).<BR>eg:<BR>tblHobby has 2 fields:<BR>HobbyID Hobby<BR>1 Swimming<BR>2 Jogging<BR>3 Riding<BR>4 Sailing<BR>5 Guitar<BR>6 Piano<BR>7 Violin<BR>8 Painting<BR>9 Drawing<BR>10 ...<BR>11 ...<BR><BR>You can categorize into 3. Say the first 4 is Sport.<BR>From 5 to 7 is Music. From 8 to 9 is Arts, etc.<BR>Now, by using these 2 tables, I&#039m sure the speed will increase<BR>rather than using memo fields. The reason is in the first table<BR>it will only contain numbers in the 5 fields for Hobby1, Hobby2 etc. Now to get the Description of this number, use SQL to get the tblHobby to show it.<BR><BR>It&#039s simple but fast when you want to get information from these<BR>tables.<BR><BR>Hope this gives you a little idea. Let me know if you need more help.<BR><BR>Cheers<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
  •