New topic: Database Error 7009 in REALSQLServer
<http://forums.realsoftware.com/viewtopic.php?t=30591> Page 1 of 2 [ 16 posts ] Go to page 1, 2 Next Previous topic | Next topic Author Message BillC Post subject: Database Error 7009 in REALSQLServerPosted: Wed Oct 21, 2009 6:50 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca When RB tries to execute the following line it says "Only a single result allowed for a SELECT that is part of an expression." Code: sql="SELECT Word,CodedWord from tblDict where CodedWord='"+SearchWord+"' Order by Word IN (SELECT Word,CodedWord FROM tblDict WHERE word LIKE '"+LikeString+"')" The idea is to attack the table by parsing on one column and then on the results against another column. >From my reading of SQL query documents it seems to me it should be able to >handle this. I think it is interpreting the subquery as an expression. Any suggestions would be appreciated. Bill Top npalardy Post subject: Re: Database Error 7009 in REALSQLServerPosted: Wed Oct 21, 2009 7:05 pm Joined: Sat Dec 24, 2005 8:18 pm Posts: 5349 Location: Canada, Alberta, Near Red Deer BillC wrote:When RB tries to execute the following line it says "Only a single result allowed for a SELECT that is part of an expression." Code: sql="SELECT Word,CodedWord from tblDict where CodedWord='"+SearchWord+"' Order by Word IN (SELECT Word,CodedWord FROM tblDict WHERE word LIKE '"+LikeString+"')" The idea is to attack the table by parsing on one column and then on the results against another column. >From my reading of SQL query documents it seems to me it should be able to >handle this. I think it is interpreting the subquery as an expression. Any suggestions would be appreciated. Bill The IN clause can only use a list of values, but there can only be one value per list entry ie / word IN ('hello', 'goodbye' , 'and so on') The query in your sub select returns a list that has two values per entry ie / ( 'word codeword', 'word codeword') etc And the syntax is incorrect for using order by at any rate In words describe what it is you want as the results. The SQL query doesn't make much sense to me _________________ My web site Great White Software RBLibrary.com REALbasic learning Top BillC Post subject: Re: Database Error 7009 in REALSQLServer - nplardyPosted: Wed Oct 21, 2009 7:11 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Thanks for the critique. I'll see what I can figure out. Back to studying the sql book. Bill Top BillC Post subject: Re: Database Error 7009 in REALSQLServer - nplardyPosted: Wed Oct 21, 2009 7:17 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Wow! I pulled out the last occurrence of "codedword" in the IN section and it ran perfectly! Thanks a Million! Top BillC Post subject: Re: Database Error 7009 in REALSQLServer -nplardyPosted: Wed Oct 21, 2009 7:39 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Well, I guess I was a little premature. It did parse the table of 100,000+ entries down to 5 but you were correct, the ORDER BY did not work. I went back to my documentation and can't see why it should not work. But, you apparently saw something that my inexperience is not showing me. Would you be so kind as to tell me why the Order By is incorrect? What I want to end up with is a list of sorted Words. While my sample run limited the results to only 5, other runs will result in 1,000s of entries. Thanks, Bill Top timhare Post subject: Re: Database Error 7009 in REALSQLServerPosted: Wed Oct 21, 2009 7:56 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6603 Location: Portland, OR USA Your ORDER BY clause is strange. Word In (Select ...) will evaluate to a boolean true/false or zero/one. Not a very useful sort order in my opinion. Maybe that is what you want, but it would be a fairly advanced usage, so I'm guessing that isn't what you had in mind. Tim Top BillC Post subject: Re: Database Error 7009 in REALSQLServer - TimPosted: Wed Oct 21, 2009 8:02 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Hi Tim! Word is a column in tblDict. The table has two fields, Word and Codedword which presents two different methods of attacking a word problem. My current code is: Code: sql="SELECT Word from tblDict where CodedWord='"+SearchWord+"' ORDER BY Word IN (SELECT Word FROM tblDict WHERE word LIKE '"+LikeString+"')" This appears to do both the sub query and final query correctly (and most importantly cumulatively) but does not seem to do the final sort by Word. Any thoughts would be appreciated. I output the list of words to a list box. Regards, Bill Top timhare Post subject: Re: Database Error 7009 in REALSQLServerPosted: Wed Oct 21, 2009 8:52 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6603 Location: Portland, OR USA Your query doesn't do what you think it does. As npalardy said, can you state in words what you want to achieve? Maybe we can help, but it isn't clear from what you have now. Top timhare Post subject: Re: Database Error 7009 in REALSQLServerPosted: Wed Oct 21, 2009 9:08 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6603 Location: Portland, OR USA I'm going to make a wild guess that you were after 1) Select all the rows that match Word like LikeString. 2) From that set, select the rows that match CodedWord = SearchWord You could do that subquery with Code:"select Word,CodedWord from (select Word, CodedWord from tblDict where Word like '"+ LikeString + "') as table1 where CodedWord='"+SearchWord+"'" However, that is the same as simply saying Code:"select Word, CodedWord from tblDict where Word like '"+LikeString+"' and CodedWord='"+SearchWord+"'" Tim Top BillC Post subject: Re: Database Error 7009 in REALSQLServer-TimPosted: Wed Oct 21, 2009 9:12 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Hi Tim! I think I can do that. tblDict has two columns: Word which is a list of regular words like you might find in a spelling list and CodedWord which is doing a pattern on the word using numbers. As an example, the word GUARANTEED would be 1234356778. That is each new letter gets a replace character which replaces it. The replacement list has a non-alpha character for 26 different letters (it is just that the first 10 are 1 through 0 then it goes off to other characters such as @#$, etc.). I further take that codedword: 1234356778 and decide (by intuition) that the 3rd and 5th characters might be the letter A. So, my search is for words that now have a pattern of 12A4A56778. The 'LikeString' in this case is '__A_A____" which I think finds all the words that have an A in those two positions. >From that list, I want to pick out only the ones that also match the pattern >1234356778 which further cuts down the list. The query returns 5 entries out >of 110,000 words, the last of which is GUARANTEED which is why I thought it >was working. I am certainly no SQL person but it just seems to me it should work as I read the texts. The other odd thing is that if I move the ORDER BY to the end after the final ) or remove the order by word phrase, no results are returned at all. I guess it could just be a fluke that there are only 5 words that match the 1234356778. But, I would find it curious that they all have the two A's in the correct position as well. Thanks, Bill Top timhare Post subject: Re: Database Error 7009 in REALSQLServerPosted: Wed Oct 21, 2009 9:19 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6603 Location: Portland, OR USA You don't need a subquery for this. See my post just above that I posted while you were posting. Top BillC Post subject: Re: Database Error 7009 in REALSQLServer - TiimPosted: Wed Oct 21, 2009 9:30 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Hi Tim! I'll try your two suggestions. Perhaps I am thinking I need a mountain rather than the proverbial mole hill. Of course what I was trying to do was isolate the two queries so that the second one was looking through a much shorter list. My first choice would have been to do the pattern search and then the like string. Frankly my SQL results so far have been so fast that it probably isn't an issue. I'll let you know how it comes out and thanks again for helping. I am thinking I just add the Order By to the end of the statement. Regards, Bill Top BillC Post subject: Re: Database Error 7009 in REALSQLServer-TimPosted: Wed Oct 21, 2009 9:41 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Hi Tim! I haven't tried your first suggestion but the second does in fact work. I added the Order By and that seems to be working as well. You were also correct. I wasn't looking closely. There are now only two results the other three did not in fact have the two A's in the proper position. (I was so happy with only 5 results including the one I was targeting that I was mesmerized!) I will also try your other first suggestion as well. I have been thinking about this pattern match for a long time and decided to write the program as a learning experience for RB. I always thought it would help my word searches but the fact it only returned two hits is significantly better than I had dreamed. Now, I need to concentrate more on SQL. Thanks again, Bill Top timhare Post subject: Re: Database Error 7009 in REALSQLServerPosted: Wed Oct 21, 2009 9:55 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6603 Location: Portland, OR USA The db engine is smart enough to optimize the query such that you won't see much difference between the two. I would use the simpler, second version. Tim Top BillC Post subject: Re: Database Error 7009 in REALSQLServer-TimPosted: Wed Oct 21, 2009 10:04 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 44 Location: Orange County Ca Thanks again, Tim. So far, I am pretty happy with the DB and RB products even though I don't know enough about it. I had written a similar app a long time ago in VB but without the dictionary lookup. But this one is much better. Still have a LOT to learn though. Regards and thanks again, Bill Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 2 [ 16 posts ] Go to page 1, 2 Next -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
