November 11, 2004 at 2:00 am
Hi,
I am trying to query large table by looking for numbers that match temp table corresponding rows. Usually I would do this 'WHERE number in (SELECT number FROM #tmpTable)', but here is the trick. I need to match the last four digits from large table against 4 digits in temp table number column. Individually I would do 'WHERE number like '%1234'', but how to compare multiple values against multiple values. Hard coding one by one is not an option because it is going to be sp where number of search criteria is unpredictable.
My query executes thru sp_executesql because table name changes dynamically.
Any ideas I would greatly appreciate. Thanks.
Jonas
November 11, 2004 at 3:10 am
SELECT largeTable.[field1], ...
FROM
largeTable
WHERE
exists(SELECT * FROM #tmpTable WHERE #tmpTabel.Number = largeTable.Number %10000)
If for each row in temp table only one matching row in the large table exists you could use
SELECT largeTable.[field1], ...
FROM
largeTable INNER JOIN #tmpTable ON
#tmpTabel.Number = largeTable.Number %10000)
In both cases, if the number of rows in the temporary table is big, a primary jey on the number field shoul be defined.
November 11, 2004 at 4:12 pm
Thanks for reply.
Thats the first time I see wildcard used this way. Very interesting.
Here is the query that I have:
SET @lrgtblNAme = 'mdCallAttempt' + @year + @month
SET @resQuery = N'SELECT * FROM ' + @lrgtblNAme + N' WHERE (Exists (SELECT number FROM #tmpTable WHERE #tmpTable.number = ' + @lrgtblNAme + N'.number%10000) '
I am getting this error:"Syntax error converting the varchar value 'T018 ' to a column of data type int." Why does it need to convert?
T018 is one of the values in largetable. Both large and temp table has same data type (varchar).
Now I might sound stupid but it is first time I am using it this way. Does number%10000 has to look exactly this way. I am matching row values in temp table (4 characters or less) with large table row values that range from null to 15 char.
November 12, 2004 at 12:58 am
You can read about it in BOL under "data type precedences".
Explicitely convert this value to varchar should do the trick.
And let me add, that it is a ggod idea NOT to start multiple threads on the same topic! That makes it difficult for all to keep track where what has been answered.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply