Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kien
Starting Member
27 Posts |
 Posted - 2002-07-02 : 16:02:42
|
Hi everyoneIs it possible to embed this following two select statements: "INSERT INTO..." with "SELECT DISTINCT v.*"Thanks for your advice  ----CREATE TABLE #SearchResults (RecordID int NOT NULL, Latitude decimal(9,7) NOT NULL, Longitude decimal(10,7) NOT NULL, Counter int) INSERT INTO #SearchResults(RecordID, Latitude, Longitude, Counter) Select RecordID, Latitude, Longitude, count(*) hits FROM SEQUENCE INNER JOIN vwTest ON vwTest.Query like '%' + Substring(' ' + @keyword + ' ',seq, CharIndex(' ' , ' ' + @keyword + ' ' , seq) - seq) + '%' WHERE seq <= len(' ' + @keyword + ' ') and Substring(' ' + @keyword + ' ', seq - 1, 1) = ' ' and CharIndex(' ' , ' ' + @keyword + ' ' , seq) - seq > 0 Group by RecordID, Latitude, Longitude ORDER BY Hits DESC SELECT DISTINCT v.* FROM vwRecordSetSearch AS v, #SearchResults AS S WHERE (v.Latitude = S.Latitude) AND (v.Longitude = S.Longitude) |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-02 : 19:30:54
|
| What do you mean "embed the following two select statements..."? Embed them where? Why? Are you trying to avoid using a stored procedure? What are you trying to accomplish? Is the temporary table really being used, or did you just provide that for our convenience in trying your code?It looks like you are just inserting some values into a temp table and then selecting the results of that joined with a view. Why use the temp table at all? What's the overall goal? |
 |
|
|
kien
Starting Member
27 Posts |
Posted - 2002-07-03 : 09:59:09
|
Hi AjarnMarkThe code above is part of a stored procedure that accepts a space deliminated query, parses through it and searches for matches under one "full text like" column.I am inserting the ID's of the matched records in the temp. table, sorted by the ranking. Then I select records that match up coordinates from the temp. table to the ones in the view.What I mean embed is: actually i should have said "inner join" or something along this idea - combine the two select statements into one (subqueries), or even eliminate the last select statement.If it is possible to eliminate the use of the temp table, that would be even better. Is this possible?Basically, how can I make the above code more efficient (full text indexing etc. will be implemented later on)?Thanks !  PS: reference code fromhttp://www.sqlteam.com/item.asp?ItemID=5857 |
|
|
|
kien
Starting Member
27 Posts |
Posted - 2002-07-05 : 12:42:54
|
Please help  Here's the problem. I'm continuing someone's project and it's filled with lots and lots of coding (sql 2000, VB 6, asp)In a stored procedure, if i use more than one "select" statement without any unions, the data does not return properly to VB, and I have no idea why. So i'm trying to bybass this by attempting to combine the code presented in the first posting (into one big select statement, with sub queries). Either that, or can I execute another stored procedure within this stored procedure so that i can acheive only one select statement.This may seem like I'm bybassing the problem all together, but trust me, i've tried a long time to figure out what was wrong with VB, and i'm not having any sucess =(Your help will be much appreciated  |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-05 : 13:00:53
|
| Basically if you have more than 1 select you are returning multiple recordsets (unless using FOR XML in which case you are returning a stream) and thus your VB is probably coded to get 1 and only 1 recordset and so cannot access the next recordset as you could if you called the .NextRecordset method of the Recordset object.HTHJasper Smith |
|
|
|
kien
Starting Member
27 Posts |
Posted - 2002-07-05 : 13:32:15
|
| Hi JasperSo is there way around this with the stored procedure that I'm working on?Thanks |
|
|
|
|
|
|
|
|
embedding queries