hi
here is a problem:
i have a databes with many, many tables
the problem is that i dont know where 'abcd' string is (but it is for sure
in one of that table)
is there any SELECT that could help me with finding this string in database?
--
greets
I'm having problems constructing an SQL statement to find records from a user entered string searching record. a record consists of a comma separated string. for example:
user entered string: charts
record: bars, graphs, charts
i've tried:
SELECT MI_FILENAME, MI_EXT, MI_KEYWORDS
FROM MEDIA_ITEMS
WHERE (MI_KEYWORDS IN ('bars'))
Could u offer any assistance please
Searching through a database with data stored as text places a huge load onthe server. I've seen some applications that use some sort of word indexingthat helps speed things up.Is this the recommended strategy? And if so, how is this implemented andare there any articles that detail this?
View 1 Replies View RelatedHello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
I have a column Sports which contains a string.the string is comma delimited, so may contain e.g. 1,3,2,6,8,19 or 6,22,13 etc.What is (performance wise) the fastest way to select all the rows where the number 2 is in the Sports column.....?(I can't search on ",2," since "2" may also be at the start of the string....)
View 1 Replies View RelatedHi,Does anyone have any suggestions on an approach to speed this upplease?I need to check a given column in a given table to see whether thereare any characters within any of the records that fall outside of arange of valid ASCII characters.My problem is that of speed. The approach I have taken is to write afunction that iterates the string character by character and checks theASCII value at the current position. If it is deemed invalid I drop outof the function and return True. The calling procedure then knows tostop searching as the table column has already failed.Running down a table of million rows, can take up to an hour dependingon the datatype and level of data population.If I write the same thing in Access 2003 VBA and run it via ODBC ittakes a matter of minutes.I would like to find a set-based way of doing this maybe PATINDEX orsomething else creative but am stumped.Does anyone have any brilliant ideas?Thank you!
View 5 Replies View RelatedHi,I'd be interested in people's thoughts about the following. A user on my site will be searching for a venue name, and that could officially include a sponsor which the user might not search for. Now I am using the AutoCompleteDropdown from the AJAX Control Toolkit, so the user will start typing in a few characters and the results will be returned. I can generate the results from sql by doing a simple LIKE '%' + @searchTerm + '%' however, this fills me with great fear of table scans. At the moment, we'd be querying against a table of 5K records, but our application is very new.I'm thinking one option is to split the words into another table - a one to many relationship to hold each word of the venue. The benefit of this would be that you could do a:LIKE @term + '%'but then I have the cost of the join. (And the added complexity which is not a major issue)Any thoughts/tips?Thanks!
View 1 Replies View RelatedI have a text filed in my table.I have sample data looks like <<some status>> << 3/9/2008 10:00:45 PM>> <<personname>>Im interested in searching <<some status>>Â and <<personname>> together by skipping date in between so my query set should return status and same person name i m looking for.
View 11 Replies View RelatedMy problem is that i can't search a field that contains Asian characters (Korean in this case).
The table user_Access_tab have 2 keys:
access_id nVarchar(50)
user_id nVarchar(50)
The sql query below is sent through a oledbcommand to a sql server 2005 database.
"select access_id, access_right from user_Access_tab where user_id ='HQ001kimjo012007-05-07 ì˜¤ì „ 11:50:323401'"
It doesn't show any hits even thogh i know there is a number of matching records.
The question doesn't generate an answer in SQL server manager studio eigher.
If i change the datatype on user_id to Varchar(50) the id is presented (in the database) as:
HQ001kimjo012007-05-07 ?? 11:50:323401
Then the question works, but why doesn't it work with nVarChar(50)?
Regards Martin Jonsson
Hi, i'm writing a SOCKET Port Listener for a Database, it must be multi-threaded and listen on a port for a record that when it comes in, it must write the record to the SQL database (MS SQL Server). I've got the listener to read the data over the port already and write the record into a string which i have already sliced up. Now i need to create a connection to the database and insert the variables into the database.
If Someone will please be able to give me a rough idea of how i could accomplish this with some sample code, then i will be greatful, i'm new to C#, but here is my code that i have so far.
//This is the Connection that i have made and where i am currently stuck, i dunno how to go further. Any help will be welcome.
public class ConnectionToMSDatabase
{
public void InsertDataIntoDatabase(string TableName, string connectionString, string dataFields)
{
string InsertSQLStatement;
InsertSQLStatement = "INSERT INTO " + TableName + " VALUES (" + dataFields + ")";
OleDbConnection ConnectionToDatabase = new OleDbConnection(connectionString);
ConnectionToDatabase.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = ConnectionToDatabase;
command.CommandText = InsertSQLStatement;
command.ExecuteNonQuery();
command.Connection.Close();
}
}
//Here is my connection string, all the retrieved data is stored in a string array call fullRecord
ConnectionStringToDatabase = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=" +
"Administrator" + ";Initial Catalog=FORGE;Data Source=FORGE";
i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, 3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+ 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+ 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+ 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+ 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1 INSERT INTO #SearchedProducts (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank) SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName, (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) * CASE WHEN @Word2 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description) END * CASE WHEN @Word3 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description) END * CASE WHEN @Word4 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description) END * CASE WHEN @Word5 IS NULL THEN 1 ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description) END AS TotalRank FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0 AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1 AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN
/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0
/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)
/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')
/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)
/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT
SELECT @Matches = 0
WHILE LEN(@Phrase)>0 BEGIN SELECT @NextSpacePos = CHARINDEX(' ', @Phrase) IF @NextSpacePos = 0 BEGIN SELECT @ExtractedWord = @Phrase SELECT @Phrase='' END ELSE BEGIN SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1) SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos) END
IF @SoundexWord = SOUNDEX(@ExtractedWord) SELECT @Matches = @Matches + 1 END
/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?
Hi,
This is the first database I have ever created, so please bear with me.
I've created a simple database with 1 column and about 80,000 rows. In each row is a word (basically a dictionary without definitions).
I have written a query which works, and is, as follows (you'll notice that i'm not the most original of people)
SELECT
word
FROM dbo.words
WHERE word= 'hello'
This finds the word hello.
In excel I have a row with 25 letters and then a column with every single combination of letters from 3 to 10 lettered words. (It makes sense to me!)
This comes back with a lot of possibilities (thousands), but is great in the sense that when I change any of the 25 letters the entire column automatically updates.
What I am trying to do is then take all of these possibilities and compare them against the dictionary.
I have written a line in excel which automatically creates a cell a bit like this, for the first couple of thousand possibilities:
WHERE word= 'abc' or word= 'fgm' or word= 'klm' or word= 'pqr' or word= 'uvw' or word= 'bcd' or word= 'ghi' or word= 'lmn' or word= 'qrs' or word= 'vwx'
I then whack this into the query from above and off it goes. The only problem is that the search takes ages, and because of limitations in excel I can't put more than a thousand or so words in the cell.
I am certain there is a faster way of searching through all the possibilities, any help would be much appreciated.
Thanks in advance
Im trying to do a database query based on user input from a text field. I pulled apart the string that was
entered into the search form and stored it in textArray. The problem I
am having is when I include commmon words such as the, near, view and so
on into the search field I end up with zero results. when I only search
for less common words such as rocky and argentina for example the search
works fine. at first I thought that maybe it was generating too many
results and was screwing up but when i search for rocky it works and
when i search for near rocky it doesnt.
I realize the code might not be the most efficient but here it is...
@searches_pages, @searches = paginate(:searches, :per_page => 10,
:conditions => getSearch(textArray,params[:country]))
def getSearch(textArray,selectedCountry )
result = []
string = ""
if selectedCountry != "Optional Field"
string << "country = (?) and "
end
textArray.each do |x|
if textArray[textArray.length - 1] == x
string << "match(country,caption, keywords, notes) against
(?) "
else
string << "match(country, caption, keywords, notes) against
(?) and "
end
end
result << string
if selectedCountry != "Optional Field"
result << selectedCountry
end
textArray.each do |x|
result << x
end
result
end
Im not sure if i supplied enough information but I am trying to finish
this project soon so any type of responses would help. Also, if there
is an easier way to do an sql search based off of what is entered into
the search field please let me know. The reason I did this is because I
wasn't sure how many words the user would be entering into the field.
And without knowing this I could not hard code the conditions => so I
wrote a helper method.
OK I have a search page and the query that is being send from the search box is
"SELECT * FROM [problems] WHERE ((problemBody LIKE '%' + @search_id + '%')OR @search_id IS NULL)"
Now say I have in the column for problemBody "Search the database" If i Type in the search field search the, or the database, or data, ot search, or even just s it will bring back records, But if I do not use exact keywords such as "search database" it will not bring back anything. How do I make it search all the keywords used?? like a normal search engine.
Thanks
I am finishing up my senior project application but I wanted to include a search function that would search all the tables of the database and look for matching text that is input by the user. I am not really looking for code or anything just some pointers in the right direction. I was thinking that I would have to create a view that is populated by a sub-query. My problem is how do I output the results of a search of every relevent table in the database when they all have very different column names and data types? Im guessing column aliases are involved somehow but I am not really sure where I should start. Any suggestions would be appreciated.
I just finished reading an article on how to search encrypted data efficiently and they suggested creating a new column with a Message Auhtentication Code. To be honest, reading the aritcle makes my head hurt. I can hardly understand what they were doing myself and I can't begin to explain it to a developer.
Are there any easier ways to search encrypted columns for a speciifc match? If not, does any have some stored procs that implement this messy MAC stuff?
TIA,
Barkingdog
When I execute this, it works ok but only one the first character of the request.form["d"] is stored to the db.I checked the sproc with another routine and it adds full data, and I've verified that value of request.form["d"] is longer than one chaacter by printing it to the page. Anyone got any ideas why only the first char is getting added to the db?? SqlConnection SqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString);
SqlCommand SqlCommand1 = new SqlCommand("addRoute", SqlConnection1);
SqlCommand1.CommandType = CommandType.StoredProcedure;
SqlParameter SqlParameter1 = SqlCommand1.Parameters.Add("@ReturnValue", SqlDbType.Int);
SqlParameter1.Direction = ParameterDirection.ReturnValue;
SqlCommand1.Parameters.Add("@xmlData", Request.Form["d"]);
SqlConnection1.Open();
SqlCommand1.ExecuteNonQuery();
Response.Write(SqlCommand1.Parameters["@ReturnValue"].Value);
//Response.Write(Request.Form["d"]);
hi there,i have a textbox in my page and a button,when the button is clicked the application will search for the text written in the textbox in my database which is sql server, it works fine in my system but when i upload my website in the web it doesn't work correctly i mean that it dosent find all the matches, why this happens? is it possible that this problem occur because of the different fonts which is used in sql server and the font is used in my application?
thanks for help
Whats the easiest way to search for a keyword in all the tables present in the database.
Or searching in 5-6 tables.
Thanks,
AzamSharp
I've been given read access to a database and I also am looking at a GUI which draws data from the database. I am trying to map the results I see from the GUI to find the columns that the data is in... So.. big database, takes a long time to search the entire thing so I try to narrow it down by the following:
Code:
select * from information_schema.columns where table_schema = 'db19' and table_catalog = 'masterdb' and
table_name in(select table_name from information_schema.tables where table_type = 'base table' and table_catalog = 'masterdb' and table_schema = 'db19' and data_type in ('text' and 'varchar'))
This essentially gives me a list of tables and columns whos data type is either text or varchar. Once I have this list... I then run the following on each:
Code:
select top 1 [col_name] from [table_name] where [col_name] like ' (here is the value I want to search) '
So this runs through each table, looking to see if a value exists and if so, prints the result.
I am then left with a much much smaller list that I can manually look through and find the one I am looking to specifically find.
Is it possible to do this running only one query... where the output gives me all the columns in with a specific data type that contain a value I enter Anything else to make this more efficient
I am aware that there are data mining programs that could probably do this however I only have read access on the database which often causes a problem. The application I am using is "Aqua Data Studio" ....
Is there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.
Any ideas?
Thanks in advance.
Hi friends
Suppose i have a table of 100 cols and 10000 rows i want to search a particular field called 'Newyork' . I dont no what the col is ?
Can anyone tell me how can i search that
Vicky
Hi, this is my first post on these forums, so please excuse me if this topic has already been covered.
I'm currently working in a power station for student vacation work placement. I need to export data from a database that gets it's data from machinery and inputs out in the plant. The machines that provide this input put it into a database, and I need to find the relevant data to export.
My problem is that, in some cases, the sample data that i'm given may be under different field names, in a completely unrelated table. I was looking for a way to search the entire database (250+ tables) for a certain string, so I can find where it is in the database, and run queries on the table it originates from. For example:
My sample data shows me that I have an object with the ID Y03A3DEA_TH1. I know this ID will occur somewhere else in the database, but i'm just not sure where.
If anyone knows of any way that I can search the entire database for specific data, either using tools in MS SQL 2000, or 3rd party apps, i would greatly appreciate their help.
Thanks a lot,
Jack Smith
Dear Sir,
I am building a website some what like B2B portal using asp.net and access database. I want to provide a search facility to the user through which they can search products in our database.
Can you provide me a strong SQL Query for that. Or is there any other way of doing that.
Please help me.
Thanking You
Suraj
Maybe a totally newbie question - I need to find some text in SQL database, I have no idea in which table it may be. Can I do it through SQL Management Studio 2005 or do I need some other utility? What would you suggest then?
Thanks.
I am using C# in  Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.Â
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();
Hi,
I have question, sorry if it is very basic, as SQL is not my thing!
Iam allowing visitors on my IBS site to (lets say) create HTML post. This is enabled by allowing the user to use WYISWG text editor component. This means that users can create all sorts of HTML tags.
Before storing this HTML in the SQL Server, I encode it.
I also need to provide users with searching ability. So what is the best way in achieving this? Can I write search SQL normally, as in, with LIKE operators or do I need to something special?
Thanks
Hello All,SQL 2000, case insensitive databaseI have a situation where I need to find abbreviations in the rows in atable. The rule i came up is, get all the rows from the table wherethere is more than one character is capitalized consequtively eg."USA", "TIMS", "AIR"Here is the sample data:create table test (mystring varchar(100))goinsert into test (mystring) values ('I live in USA')insert into test (mystring) values ('this is a test row. usa(abbreviated wrongly).')go--expected result setmystring----------------------I live in USAHere is the query which I tried.select * from test where mystring collate SQL_Latin1_General_CP1_CS_ASlike '%[A-Z][A-Z]%'But the above query returns both the records. Any help?Thanks
View 2 Replies View RelatedHi,
I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is;
//Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); ";
SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
//If UserName is null, display confirmation, else display errorif (UserName == null) ;
{UserNameCheckLabel.Text = "Username okay";
String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"])));
}else;
{UserNameCheckLabel.Text = "That username is in use";
}
I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice!
Many thanks,
Chima
KRFeb 6, 1:48 pm show optionsNewsgroups: microsoft.public.access.formsFrom: "KR" <kra...@bastyr.edu> - Find messages by this authorDate: 6 Feb 2006 13:48:00 -0800Subject: Extract Number from Fields - SQLReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Remove | Report AbuseI am new to the SQL world, and I am trying to come up with a scriptthat will extract only the numerical data from a column of varchardata type . There is not a pattern to the data entered, except thatthe data thatI am looking to extract is a three digit number. If someone couldpoint me in the right direction that would be great.Thanks in advanceKR
View 5 Replies View Related
I have a database which contains time series data (historical stock prices) which I have to search for patterns on a day to day basis. But searching this historical data for patterns is very time consuming not only in writing the complex t-sql scripts but also executing them.
Table structure for one min data:
[Date] [Time] [Open] [High], [Low], [Close], [Adjusted_Close], [MA], [DI].....
Tick Data:
[Date] [Time] [Trade]
Most time consuming queries are with lots of inner joins. So for example if I have to compare first few mins data then I have to do inner join like:
With IntervalData AS
(
SELECT [Date], Sum(CASE WHEN 1430 = [Time] THEN [PriceRange] END) AS '1430',
Sum(CASE WHEN 1431 = [Time] THEN [PriceRange] END) AS '1431',
Sum(CASE WHEN 1432 = [Time] THEN [PriceRange] END) AS '1432'
FROM [INDU_1] GROUP BY [Date]
)
SELECT [Date] ,[1430], [1431], [1432], [1431] - [1430] As 'Range' from IntervalData
WHERE ([1430] > 0 AND [1431] < 0 AND [1432] < 0) OR ([1430] < 0 AND [1431] > 0 AND [1430] > 0)
------------------------------------------------------------------------
select ind1.[Time], ind1.PriceRange,ind2.[Time], ind2.PriceRange from INDU_1 ind1
INNER JOIN INDU_1 ind2 ON ind1.[Time] = ind2.[Time] - 1 AND ind1.[Date] = ind2.[Date]
where (ind1.[Time] = 2058) AND ((ind1.PriceRange > 0 AND ind2.PriceRange >0) OR (ind2.PriceRange < 0 AND ind1.PriceRange < 0))
ORDER BY ind1.[Date] DESC;
Is there anyway I can use Sql 2005 Data mining models to make this searching faster?
Hello there,
Don't know if this is the right forum to be asking this, but I'll give it a try...
I'm relativelly a beginner in SQL Server and T-SQL in general. The problem I'm trying to solve is the following:
The big picture is that I have data coming from different data sources which I need to store on a database for later reference. Each data source might have a different set of measurements. For example, data source 1 might log Pressure and Humidity while data source 2 logs Pressure and Temperature. Once the data is present on the DB, the users can go ahead and retrieve data for a given [datasource/measurement/time interval] to generate reports or charts.
My implementation so far consists of two tables: series_info and series_data. series_info holds general information for a given series of measurements for a given data source (Pressure for data source 1, Pressure for data source 2, Humidity for data source 1 and Temperature for data source 2, in our example). Each series has a bigint index as primary key.
The table series_data contains all data relative to the series from series_info. Each piece of data has a bigint as a primary key, an associate time (which is always crescent) and a foreign key to the series it represents (in series_info).
Alright, everything is cool so far. However, whenever a user wants to retrieve data for given [data source/measurement/time interval], this takes very long, since all data is interposed in series_data and for every search it's necessary to find where the desired data actually lies.
One obvious solution for this would be to dynamically create a new table to hold the data for each series, but that would just make my database disorganized, since there would be thousands and thousands of tables.
Another thing that comes to my mind is to create a table with information of where lies the data for a given [data source / measurement] for given dates. So when the user requested data for a given [data source/measurement] between, say, january and february, we would first look at this intermediate table and find out that the data lies between indexes 1000 and 2000 on the series_data table, so the next SELECT command to series_data would already contain a restriction like WHERE index>=1000 and index<=2000. This should probably improve the speed of retrieval.
What do you guys (or girls) think? Maybe there's simply a classical solution for such a case.
Thanks in advance!
Hi!
I found a problem while retrieving/searching/filtering Myanmar Text with a Select Statement, In a Myanmar Text table there in a column called HeadWords.
It's Sample Data
HWID,HeadWords
1,|က|
2,|ကာ|
3,|ကá€|
4,|ကီ|
5,|ကဲ|
I want to make the following search:
SELECT * FROM TableName WHERE HeadWords LIKE '%|က|%' this should give me all entries that have a "|က|" *ANY* place in the HeadWords column. Right?
However, it gives me unproper results. you may see last two records have 2 characters between pipe(|).
HWID,HeadWords
1,|က|
3,|ကá€|
5,|ကဲ|
Since the wildcard character % means no or all characters it should work. And I've tried pipe, comma, forward slash and back slash.
The problem only seems to occur when the wildcard character is used for the any part of character. Let me know alternative way to search that matters. I've tried in MSAccess. There are same problem like MSSQL.
It's any problem in searching support for National Characters (UTF8). I've tried in OpenOffice Database with those data.
It's work fine.
If you not see Myanmar characters, please download fonts from http://www.fontmm.com/font_downloads.htm
Does anybody have an explanation to this, please let me know.
Thanks in advance!
Ngwe Tun