|
same way you just need to create the dynamic query..
Best Regards,
Apurva Kaushal
|
|
|
|
|
It means we cannot use a single query.We have to give it in the loops i.e with cursors.Is that right
Thank u.
Regards,
Uma
|
|
|
|
|
No not at all...
What exactly is your requirement?
Best Regards,
Apurva Kaushal
|
|
|
|
|
I have two tables.
one contians a column called keywords which contains individual words.
other contains a column called list which contains a list of words separated by comma.Now i want to write a query to retrieve details from first table by searching the second table.How to do this
Regards,
Uma
|
|
|
|
|
for this no need to go for the dynamic query you can directly use the method suggested by Colin.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then it will not work.now the table data is like this.I hope this will make you clear with my requirement.
table1
col1
hyderabad
delhi
mumbai
table2
col1
hyderabad,chennai,bangalore
bangalore,kolkata,delhi
bangalore
mumbai,hyderabad
now if I give select * from table1 where col1 in (select col1 from table2)
i will not get the output
can u check it once
Regards,
Uma
|
|
|
|
|
Uma Kameswari wrote: Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then...
...it breaks the first normal form of database normalisation.
The easiest solution from this point is to go to the dynamic SQL and use cursors to navigate through the rows. However, that is a very slow solution.
If you can, it would be better if you could find some way of normalising the data so that it can be used more efficiently.
|
|
|
|
|
He wanted to use the result of another SELECT statement inside the IN() expression which is a valid thing to do.
|
|
|
|
|
Actually what she has given was some values in a variable thats why I had given that type of solution.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Apurva Kaushal wrote: Actually what she has given was some values in a variable thats why I had given that type of solution
But if you read the pseudo sql that she provided you can see what she is trying to do:
declare @list nvarchar(1000)
set @list = select words from sample
select * from emp where dept in(@list)
The second line, being an apparent result from a SELECT statement gives the indication that it isn't necessarily a variable she wants, but the result of the SELECT statement.
|
|
|
|
|
yep and there exactly I got confused and gave that solution.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Apurva Kaushal wrote: yep and there exactly I got confused and gave that solution.
Don't worry about it. It wasn't explained too well. You sometimes need to read something like that a couple of times to really understand what is wanted.
|
|
|
|
|
Colin Angus Mackay wrote: You sometimes need to read something like that a couple of times to really understand what is wanted.
Yep that is correct.. Thanks for the suggestion.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Now it looks like we were both a little bit correct and a little bit wrong.
C'est la vie!
|
|
|
|
|
You can put a select statement inside the IN expression
SELECT *
FROM Table1
WHERE SomeColumn IN (SELECT Something FROM Table2)
And no need for pesky dynamic SQL.
|
|
|
|
|
Yes we can put a select statement inside the IN expression but if the column contains a list of values separated by comma(,) then it will not work.now the table data is like this
table1
col1
hyderabad
delhi
mumbai
table2
col1
hyderabad,chennai,bangalore
bangalore,kolkata,delhi
bangalore
mumbai,hyderabad
now if I give select * from table1 where col1 in (select col1 from table2)
i will not get the output
can u checkl it once
Regards,
Uma
|
|
|
|
|
You can create a table-valued function which uses a loop to split the csv list in table2 and insert them individually into a table variable.
<br />
CREATE FUNCTION [dbo].[function_string_to_table]<br />
(<br />
@string VARCHAR(MAX),<br />
@delimiter CHAR(1)<br />
)<br />
RETURNS @output TABLE(<br />
data VARCHAR(256)<br />
)<br />
BEGIN<br />
<br />
DECLARE @start INT, @end INT<br />
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)<br />
<br />
WHILE @start < LEN(@string) BEGIN<br />
IF @end = 0 <br />
SET @end = LEN(@string) + 1<br />
<br />
INSERT INTO @output (data) VALUES (SUBSTRING(@string, @start, @end - @start))<br />
SET @start = @end + 1<br />
SET @end = CHARINDEX(@delimiter, @string, @start)<br />
END<br />
<br />
RETURN<br />
<br />
END<br />
<br />
Then you can do this:
<br />
DECLARE @values VARCHAR(MAX)<br />
SELECT @values = col1 FROM table2 WHERE FOO = 'BAR'<br />
<br />
SELECT * FROM table1 <br />
WHERE col1 IN (<br />
SELECT data FROM dbo.function_string_to_table(@values, ',')<br />
)<br />
<br />
|
|
|
|
|
http://www.projectdmx.com/tsql/sqlarrays.aspx[^]
You should be able to use CharIndex as the article in the link above suggests as one solution.
select t1.*
from table1 t1
join table2 t2 on charindex(',' + t1.col1 + ',' , ',' + t2.col1 + ',') > 0
|
|
|
|
|
Hello,
I am developing a dictionary library and utility for a heavy-duty linguistic application. Because the wordlist is used very intensely, I am preloading it at startup. There are approx. 1 million records in the wordlist table, each containing a textual representation of a symbol, accompanied by a few numeric fields. There are other tables as well, but they are accessed less often and don't have to be preloaded.
What database management system would you recommend for my application?
I tried MSDE, but it required dozens of seconds for the preload operation, while the same information loaded from a custom-format binary file loads in approx. 2 seconds. I haven't tried the new SQL Server, but I suspect it'd be similar in efficiency to MSDE. Crafting a custom storage solution could be nice, but requires significantly more resources than a ready-made thing.
What do you think?
|
|
|
|
|
Bartosz Bien wrote: I tried MSDE, but it required dozens of seconds for the preload operation, while the same information loaded from a custom-format binary file loads in approx. 2 seconds.
What is more important to you: The speed of the pre-load operation, or the speed of retrieval?
To me a "pre-load" operation is something that happens in advance and is not time critical. If you need very fast search and retrieval then having a slightly slower preload operation where it properly indexes everything for you is much more advantageous.
You might also want to consider SQL Server 2005 Express Edition instead of MSDE.
|
|
|
|
|
hi,
Suppose, a dataset is filled with some data from the database, after that some changes where done to the database. But the value in the dataset will be the same as before.
The updated data has to be obtained form the database.
Is there any solution for problem?
sooraj
|
|
|
|
|
Actually the dataset is not connected to the database once you get the data. To update the dataset with the current data you need to get the data again from the database.
Best Regards,
Apurva Kaushal
|
|
|
|
|
Google on cocurrency issue with dataset for this. There are plenty of workaround available in this workaround.
saud
|
|
|
|
|
Hi, I want to know if I made a transaction that include a SQL statement which delete some data from a table in SQL DB, and then i committed this transaction, can I return back that deleted data? or they are missed????
Thanks alot
Hamody
|
|
|
|
|
Once you delete the data and commit the data, the original data is gone. If this is not the behaviour that you want, you need to use a different method to "remove" the data. Normally this is done by setting a flag to mark the record as deleted somehow. This means that all the selects that you do for live data would also need to use this flag.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|