Keyword Query
I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
Can this be done? What would the query look like?
I am looking for a list like:
bull
barrel
rodeo
western
cowboy
Any suggestions?
Thanks,
Rob
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Need Help With SQL Query. Keyword Matching.
Hi all,I have two tables:workgroups (wg_id, wg_name)workgroups_keywords (wgk_wg_id, wgk_keyword)Each workgroup has an associated list of one or more keywords.What I want do to at first was given a particular list of keywordsbring back a list of workgroups that have at least one matching keywordassociated with it.I have the following query:select distinct(wg_id), wg_namefrom workgroups, workgroups_keywordswherewgk_keyword in (#QuotedValueList(Keywords.wgk_keyword)#)andwg_id = wgk_wg_idorder by wg_nameThis works great.However, is there a way in a single query to order the returned rows bythe number of keywords that are found to be matching (in other words anorder by relevancy, the more keywords that match the more relevant thereturned row)?Thanks in advance.David
View Replies !
View Related
Very Slow Query When Using In Keyword
hey guys i have a query that takes too long that, actually i never got it to finish excuting the query looks like tht select referer,count(*) from t1 where referer is not null and referer in( select distinct(Referer_Direct) from t2 where Referer_Direct is not null )group by referer the inner select just returns 5 rows so when i replace the inner select with actual values like tht select referer,count(*) from t1 where referer is not null and referer in('val1','val2','val3','val4','val5' )group by referer it excutes immediatly any clues how to solve this issue the db is running on sql server 2005 express SP2 thx in advance
View Replies !
View Related
How To Query By Keyword For Image Field.
If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks
View Replies !
View Related
Problem With Query Where Column Name Is The Same As A Keyword
hi I am having trouble with the following query within my store procedure. as you can see, i am making an union of 2 separate queries. in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc" is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *) CREATE PROCEDURE topcat.getTransHistory ( @contact_id numeric(9) ) AS BEGIN DECLARE @phone_no varchar(255) set @phone_no = (select top 1 phone_num from topcat.class_contact where _id = @contact_id) select cast(trans_new.trans_date as varchar(50)) date, '' code, cast(payment.date_paid as varchar(50)) datepaid, '' "desc", case payment.payment_type when 'cheque' then trans_new.item_total else '' end pledged, '' mail, case payment.payment_type when 'cheque' then '' else trans_new.item_total end received, '' receipt from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id where trans_new.contact_id = @contact_id union select cast(trans_old.date as varchar(50)) "date", trans_old.code, cast(trans_old.datepaid as varchar(50)) "datepaid", trans_old.desc, cast(trans_old.pledged as varchar(128)), trans_old.mail, cast(trans_old.received as varchar(128)), trans_old.receipt from topcat.MMTRANS$ trans_old where phone = @phone_no END GO Cheers James :)
View Replies !
View Related
Uppercase T-sql Keyword In Query Editor
In Query Editor I type statements like this: "select * from ...." Does Query Editor support a "macro" facility where I could, via keystorke, uppercase all t-sql keywords? (so it would look like SELECT * FROM...) TIA, barkingdog P.S. You think I'm lazy? I knew a programmer who was so lazy that his password was one character long!
View Replies !
View Related
Incorrect Syntax Near The Keyword 'SELECT'.Incorrect Syntax Near The Keyword 'else'.
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT 2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end 3 FROM abc 4 Anyone can help? Thanks a lot.
View Replies !
View Related
In Keyword With INT
I am trying to pass several ids to use in a where clause. 1 For Each row as GridViewRow In gv_child.Rows 2 If row.RowType = DataControlRowType.DataRow Then 3 Dim chk as CheckBox = CType(row.FindControl("cb_Approve"),CheckBox) 4 If chk IsNot Nothing AndAlso chk.Checked Then 5 Dim id As Integer = CInt(CType(row.FindControl("lbl_id"),Label).Text) 6 ids &= "," & id 7 End If 8 End If 9 If ids.Length > 1 Then ids = ids.Substring(1) 10 'Submit to sql with ids as param... 11 Next I am getting an exception. Error converting '38,39' to a column of datatype int. What am I doing wrong?
View Replies !
View Related
BETWEEN Keyword
I need to retrieve records where the date is in between the current date and 4 days previous. I've tried: WHERE DateSubmitted BETWEEN GetDate() AND GetDate() - 4 it doesn't work... Can someone help out?
View Replies !
View Related
Using Keyword Question...
Hi everyone, I have a few classes representing my data layer for my ASP.Net 2.0 Web Application. For example, UserDAL, DocumentDAL, etc... All these classes derive from BaseDAL, which stores the SqlConnection in it. The DAL classes access it through a property. The property is defined like that: private SqlConnection connection;public SqlConnection Connection{get{ if (connection == null) connection = new SqlConnection(); if (connection.State != ConnectionState.Open) { connection.ConnectionString = ConnectionString; connection.Open(); } return connection;}} The constructor of each DAL class is overloaded and takes a SqlConnection object. So if I need to access two DAL classes, I pass the connection of the first, to the second to prevent opening and closing the connection again and again. Here is a sample: UserDAL userDAL = new UserDAL(); userDAL.DoSomethingAndAccessConnectionProperty(); // Now userDAL.Connection is opened. Pass the connection to the constructor of DocumentDAL DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection); documentDAL.AlsoDoSomething(); So, if I do the following:using (UserDAL userDAL = new UserDAL()) { userDAL.DoSomethingAndAccessConnectionProperty(); DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection); documentDAL.AlsoDoSomething(); } I assume that the connection is removed from memory, even if the DocumentDAL class has also used it. Am I right? I want to be sure that there will be no open connections.Thanks
View Replies !
View Related
Search By Keyword
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
View Replies !
View Related
Keyword Search
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name. At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
View Replies !
View Related
Top Keyword And Sorting
I heard a claim recently at a SQL Server users group meeting that theTOP keyword forces sorting on a database server. I can't find anyreason this might be true and the Books Online say nothing about it.Can someone verify this claim one way or the other?
View Replies !
View Related
EXEC Keyword
I am in the process of importing an Oracle database into SQL Server.Once of the tables has a field called "EXEC".SQL Server seems to reject any queries that include that particularfield because EXEC is a keyword. For eg.SELECT ID, EXEC from USERSresults in a syntax error near keyword EXEC.I can't change the fieldname becuase it will require reworking of awhole bunch of scripts.What can I do to adjust the query?Bijoy
View Replies !
View Related
Use Of DISTINCT Keyword
If I use DISTINCT isn't there a rule where it must be the first field selected? Also, there can only be one DISTINCT field in a query, correct? ie, SELECT DISTINCT fieldA, fieldB FROM tableA but not SELECT fieldA, DISTINCT fieldB FROM tableA or SELECT DISTINCT fieldA, DISTINCT fieldB FROM tableA thanks again, this is a great forum ddave
View Replies !
View Related
Keyword Search
Hi, I have a table like ProductId, Description, Description2 where Description and Description2 are text datatypes. I'm trying to return all records where myKeyword exists as a singular word in either of these two fields. Should I create a child table where each word in each of these fields has its own row for each product and query against that or is there an efficient way of querying this result without creating the extra child table? Many thanks for any pointers Dan
View Replies !
View Related
Use Of (optional) Keyword
I'm using an application that is generating some SQL scripts for SQL Server 2005. I'm trying tweak it so that I can run it on SQL Server 2000. The line that I'm having trouble with is: CREATE INDEX FKFFF41F9960601995 ON alf_access_control_entry (permission_id);(optional) The key word "(optional)" is causing trouble. I understand this keyword, when used in SQL Server 2005, let's the script continue and complete when errors are detected. What is the alternative syntax to use in SQL Server 2000? Thanks, -Q
View Replies !
View Related
T-SQL Keyword Not Highlighted
I'm working in the query analyzer from SQL Server management studio for SQL Server 2005. For some reason, my varchar keyword isn't getting highlighted. It doesn't cause any issues when i run the query, its just annoying. Anyone have any ideas to correct this?
View Replies !
View Related
Substitute For First Keyword?
IS there ny substitute for this ? =First(Fields!TouchedBy.Value, "Dataset1") instead of keyword 'First' i need the current record , but also i need to refer dataset name also , is there any way to do that ? Any idea? thanks
View Replies !
View Related
Regarding Distinct Keyword
hi guys i have a query that contains several table joins when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds is adding distict keyword or order by acts as an index for the query or what ? . . . . here is my query : SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT FROM dbo.TECHNICAL_MAIN p INNER JOIN (SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j FROM technical_main m WHERE revision_no IN ('0', '1', '2') GROUP BY char_field1, char_field2_ar, subcat_id) b ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2') INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j Good luck for all the folks
View Replies !
View Related
Using The DISTINCT Keyword
Hi all, I have two datatables in my database. The first table, named Books, has two columns: BookID and Author (BookID is the primary key). The second table, named Purchases, has three columns: PurchaseID, BookID, BuyerID (Purchase ID is the primary key). The idea here is that the Books datatable contains information regarding the book and its author while the Purchases datatable contains information on who has purchased what book. Now, say I want to write an SQL query to extract a list of all the authors who have written a book purchased by buyer X. How would I go about doing this without having any duplicate entries? I figured that the following would work: SELECT DISTINCT * FROM Books INNER JOIN Purchases ON Books.BookID=Purchases.BookID But this ends up generating duplicate BookIDs if the Purchases table contains several buyers who have bought that Book. I know I could use BookID rather than * in the above query and that would work, but in reality I'm dealing with more complex tables and I would rather keep the * in there to actually get all the data out in one go.
View Replies !
View Related
Incorrect Syntax Near The Keyword 'AS'.
Hi,I was just wondering if some kind soul on here could help me. I created a table in a database with called 'EuropeBroadcastList', with the following columns: Name, First Name, First, Last Name, Last, Title, Company, Photo.I have created a query whereby I want to create a new table with only specific columns called 'EuropeBroadcastSorted' and sort the names in 'EuropeBroadcastList' by the 'Last' column. The query is: CREATE TABLE europebroadcastsorted AS (SELECT [name], title, company, photo, [Last] FROM EuropeBroadcastList ORDER BY Last ASC); When I execute it I get the following syntax errors: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'AS'.Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'ORDER'.Can any kind person help me resolve this?Thanks Rob
View Replies !
View Related
Incorrect Syntax Near The Keyword 'AS'.
HiI am getting the following error on my Select statement: Incorrect syntax near the keyword 'AS'. SELECT [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], RowFROM(SELECT ROW_NUMBER() OVER (ORDER BY [J] DESC)AS Row, [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K]FROM [TABLE]WHERE (([A] LIKE '%' + @A+ '%') OR ([K] LIKE '%' + @K+ '%')) AS LogWithRowNumbersWHERE (Row >=91 AND Row <= 100) I used the following select statement as a template (which works fine): SELECT [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], [L], Row FROM (SELECT ROW_NUMBER() OVER (ORDER BY [H] DESC) AS Row, [A], [B], [C], [D], [E], [F], [G], [H], [I], [J], [K], [L] FROM [TABLE] WHERE (([J] = @J) AND ([E] >= @E)) AND (([K] < [L]) OR (([K] = 0) AND ([L] = 0)))) AS LogWithRowNumbers WHERE (Row >= 82 AND Row <= 90) What is the difference that would make one work and the other not work?Thanks if you can help,Jon
View Replies !
View Related
Incorrect Syntax Near The Keyword 'FROM'.
Getting this error.. the page runs fine but it after entering the data it produces the following.. ERROR: Incorrect syntax near the keyword 'FROM'. with the following code...Please help! <head runat="server"><title>Parts Lookup</title></head><body style="text-align: center"><form id="form1" runat="server"><div style="text-align: center"><br /><brpan style="font-size: 10pt; font-family: Tahoma"> Enter a Part Number</span> <asp:TextBox ID="Productnbr" runat="server" Columns="4" Width="177px"></asp:TextBox><br /><asp:Button ID="DisplayPartNumberButton" runat="server" Text="Display Price, Description, Unit of Measure" Font-Names="Tahoma" /><br /> <br /> </div><asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="PartFilterDataSource" EnableViewState="False" Width="431px" CellPadding="4" ForeColor="#333333" GridLines="None" Font-Bold="False"><Columns><asp:BoundField DataField="PartNbr" HeaderText="Part Number" SortExpression="PartNbr" /><asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" /><asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" /><asp:BoundField DataField="UnitOfMeasure" HeaderText="Unit of Measure" SortExpression="UnitOfMeasure" /></Columns><FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /><RowStyle BackColor="#F7F6F3" ForeColor="#333333" /><EditRowStyle BackColor="#999999" /><SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /><PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /><HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /><AlternatingRowStyle BackColor="White" ForeColor="#284775" /></asp:GridView> <asp:SqlDataSource ID="PartFilterDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ManManSQLConnectionString %>" SelectCommand="SELECT PartNbr, Description, UnitOfMeasure, Price; FROM Tbl_ODBC_PartsList; WHERE PartNbr = @Productnbr"><SelectParameters><asp:ControlParameter ControlID="Productnbr" Name="Productnbr" PropertyName="Text"/></SelectParameters></asp:SqlDataSource></form></body></html>
View Replies !
View Related
Incorrect Syntax Near The Keyword 'ELSE'.
Hi,I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:Incorrect syntax near the keyword 'ELSE'.I am using Sql Server 2005.SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate]( @ReportDate smalldatetime, @SiteID int, @DataFeedID int, @FromCode varchar, @Sent int, @Delivered int, @TotalOpens REAL, @UniqueUserOpens REAL, @UniqueUserMessageClicks REAL, @Unsubscribes REAL, @Bounces REAL, @UniqueUserLinkClicks REAL, @TotalLinkClicks REAL, @SpamComplaints int, @Cost int)ASDECLARE @PKID INTDECLARE @TagID INTSELECT @TagID=ID FROM Tag WHERE SiteID=@SiteID AND FromCode=@FromCode SELECT @PKID=PKID FROM DimTag WHERE TagID=@TagID AND StartDate<=@ReportDate AND @ReportDate< ISNULL(EndDate,'12/31/2050')IF @PKID IS NULL BEGIN SELECT TOP 1 @PKID=PKID FROM DimTag WHERE TagID=@TagID AND SiteID=@SiteIDENDDECLARE @LastReportDate smalldatetime, @LastSent INT, @LastDelivered INT, @LastTotalOpens Real, @LastUniqueUserOpens Real, @LastUniqueUserMessageClicks Real, @LastUniqueUserLinkClicks Real, @LastTotalLinkClicks Real, @LastUnsubscribes Real, @LastBounces Real, @LastSpamComplaints INT, @LastCost INT SELECT @Sent=@Sent-Sent,@Delivered=@Delivered-Delivered,@TotalOpens=@TotalOpens-TotalOpens,@UniqueUserOpens=@UniqueUserOpens-UniqueUserOpens,@UniqueUserMessageClicks=@UniqueUserMessageClicks-UniqueUserMessageClicks,@UniqueUserLinkClicks=@UniqueUserLinkClicks-UniqueUserLinkClicks,@TotalLinkClicks=@TotalLinkClicks-TotalLinkClicks,@Unsubscribes=@Unsubscribes-Unsubscribes,@Bounces=@Bounces-Bounces,@SpamComplaints=@SpamComplaints-SpamComplaints,@Cost=@Cost-Cost FROM CrmPublisherSummary WHERE @LastReportDate < @ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDUPDATE CrmPublisherSummary SET Sent=@Sent, Delivered=@Delivered, TotalOpens=@TotalOpens, UniqueUserOpens=@UniqueUserOpens, UniqueUserMessageClicks=@UniqueUserMessageClicks, UniqueUserLinkClicks=@UniqueUserLinkClicks, TotalLinkClicks=@TotalLinkClicks, Unsubscribes=@Unsubscribes, Bounces=@Bounces, SpamComplaints=@SpamComplaints, Cost=@Cost WHERE ReportDate=@ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDELSE SET NOCOUNT ON INSERT INTO CrmPublisherSummary( ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes, Bounces, SpamComplaints, Cost, DataFeedID, TagID) SELECT @ReportDate, @SiteID, @PKID, @Sent, @Delivered, @TotalOpens, @UniqueUserOpens, @UniqueUserMessageClicks, @UniqueUserLinkClicks, @TotalLinkClicks, @Unsubscribes, @Bounces, @SpamComplaints, @Cost, @DataFeedID, @TagIDSET NOCOUNT OFF
View Replies !
View Related
TOP Keyword SQL Server 2005
I am using the top keyword as follows in the same SP. The select and the update statements are one below the other: Select TOP(1) col1,col2 from table1 where col3='val1' Update TOP(1) table1 set col2= 'val2' where col3='val3' There is a primary key on the column which is not included in either the select or the update statement. Can anyone confirm that the both the statments will return the same row?
View Replies !
View Related
Keyword Not Supported: ',server'.
Hello there. I'm developing an eCommerce solutions based on the ASP.NET 2.0 Commerce Starter Kit, architechture. It uses the Provider Pattern. In my web-application, i use the CatalogProvider, to retrieve data from a SQL Server 2005 database. I call the methods through a handler class, whoch excists inside the WebApp. I also use a ShoppingCartProvider, OrdersProvider, ShippingProvider etc. in the same way. In my Web.Config file, i have all the provers listed, and on each provider, the name of the connectionString to use are given. My connection string looks like this:"connString" connectionString="Server=xxxx;Database=xxxx;Trusted_Connection=True;" providerName="System.Data.SqlClient" /> The problem is, that suddently, when browsing the website, that connects to the database through the providers, i get this error: Keyword not supported: ',server'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.ArgumentException: Keyword not supported: ',server'.Source Error: Line 31: public static IDataReader GetProductsByCategory(int categoryID) Line 32: { Line 33: return Commerce.Providers.CatalogProvider.Instance.GetProductsByCategory(categoryID); Line 34: } Line 35: Source File: d:DevelopmentASPNETSeoShopApp_CodeHandlersCatalogManager.cs Line: 33 Stack Trace: If i then go back to my web.config file, and removes the providerName section, of the connectionString, the website works again, for a short period. When the error return, i undo the deletion of the providerName, and it will work again... For a short time... I've also tried to use another connectionsString, like this:Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI But then the keyword which is not supported is: ', data source' Does anyone know what the issue might be? Thanks in advance...
View Replies !
View Related
Keyword Search On A DataTable
HiUp to now I have been filtering my dataset using RowFilter. However, I also need a free-text search on the Keywords column. I have been looking for tutorials but don't really understand what's required. Do I have to specify a Primary Key column or something in order for this to work?Anyone know any clear tutorials?!Many thanksShaun
View Replies !
View Related
Incorrect Syntax Near The Keyword 'END'.
Hi Guys, This one is driving me nuts... I have been getting this error in my Asp.Net page. I run the profiler and then I can grab the actual query, and when I run this query in query analizer, it works just fine... I have no clue at all about what's going wrong with my code or query... cmd.CommandText = "exec mydatabase.dbo.mytable @select = 'Select convert(varchar(20),J.Datecreated,107)as Date, max(J.datecreated)As DateOrd from table1 J Left Join table2 C on J.CustID = C.CustID where C.Mode = 1 group by convert(varchar(20),J.DateCreated ,107) order by max(J.datecreated) DESC', @sumfunc = 'count(ID)', @pivot = 'Staff', @table = 'table1'" -========================= STORED Procedure CREATE PROC myTable ( @select varchar(8000), @sumfunc varchar(100), @pivot varchar(100), @table varchar(100) ) AS DECLARE @sql varchar(8000), @delim varchar(1) SET NOCOUNT ON SET ANSI_WARNINGS OFF EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2') EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null') SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' ) SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) WHEN 0 THEN '' ELSE '' END FROM tempdb.information_schema.columns WHERE table_name='##pivot' AND column_name='pivot' SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot DROP TABLE ##pivot SELECT @sql=left(@sql, len(@sql)-1) SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ') print @select EXEC (@select) SET ANSI_WARNINGS ON SET NOCOUNT OFF thanks in advance...
View Replies !
View Related
Large Keyword Search
I’m working on a project that will allow a user to search through approx 100,000 records in a SQL table. Three of the columns are ‘text’ fields that hold paragraphs of text. The user interface has a ‘general search’ option so that they can enter a number of key words and the database will return a count of the records found containing the keywords. At the moment I split the input and then build a query based on their input. For instance if they enter ‘hello world’ the input is split into two strings ‘hello’ and ‘world’. I then build the query in a loop and get a query like so: Select Count(ID) as myCount FROM myTable WHERE (colOne like ‘%hello%’ AND colOne like ‘%world%’) OR (colTwo like ‘%hello%’ AND colTwo like ‘%world%’) OR (colThree like ‘%hello%’ AND colThree like ‘%world%’) Unfortunately this query runs EXTREMELY slowly and just seems wrong. Is there a more efficient way I should be doing these types of searching? This method works ok on 100 records, but this is the first time I have worked on such a large database. Is it also possible to search a text column and look for exact matches? For instance I have 2 records with their textfield containing: Rec 1: the news for today is blah blah. Rec 1: this is a new item If I currently search for ‘new’ (select colID from myTable where colOne like ‘%new%’) I will get both these records, but I’d really only like to pull out the second record. Any help would be great appreciated! :)
View Replies !
View Related
Keyword Not Supported: 'driver'
y am i having keywork not supported? is there anything wrong with my connection string ?? ==========web config================ <appSettings> <add key="db" value="icms" /> <add key="db_user" value="sqladmin" /> <add key="db_server" value="server" /> <add key="db_pwd" value="12345" /> <add key="session_timeout" value="600" /> </appSettings> ==========DB.vb============== Dim myDB As New SqlConnection Dim myCMD As New SqlCommand Public Sub New() Dim db_server = AppSettings("db_server") Dim db = AppSettings("db") Dim db_user = AppSettings("db_user") Dim db_pwd = AppSettings("db_pwd") Dim DBConnection As String = "DRIVER={SQL Server};" & _ "SERVER=" & db_server & ";" & _ "DATABASE=" & db & ";" & _ "UID=" & db_user & ";" & _ "PWD=" & db_pwd & ";" & _ "OPTION=3;" myDB.ConnectionString = DBConnection myCMD.Connection = myDB End Sub =============================
View Replies !
View Related
Incorrect Syntax Near The Keyword 'WHERE'
Can someone help me? I am trying to script an SQL statement that would allow someone to INSERT a new username into a database where it is not a duplicate entry. The table is like this: UserID - int, 4, identity(1,1) UserName - nvarchar(50) UserPass - nvarchar(50) The code to execute this where i am getting the errors is this: Function ChooseUName() If Page.IsValid Then Dim objCon As New SqlConnection(con) Dim sqlInsert As String = "INSERT INTO tblUser (UserName) " & _ "VALUES (@Username) WHERE NOT EXISTS (SELECT UserName FROM tblUser)" Dim cmd As New SqlCommand(sqlInsert, objCon) cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 50) cmd.Parameters("@Username").Value = txtUsername.Text Dim id As Integer Try objCon.Open() id = cmd.ExecuteScalar() Finally If objCon.State = ConnectionState.Open Then objCon.Close() End If End Try Response.Write("Your User ID is: " & id.ToString()) Response.End() End If End Function This is the error: Incorrect syntax near the keyword 'WHERE'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'. Source Error: Line 73: Try Line 74: objCon.Open() Line 75: id = cmd.ExecuteScalar() Line 76: Finally Line 77: If objCon.State = ConnectionState.Open Then PLEASE HELP!! I'M ON A STRICT DEADLINE!!! :o THANKS IN ADVANCE!
View Replies !
View Related
Multi Keyword Search SP
Is it possible to write a Stored Procedure that takes a string ofsearch keywords as argument and returns the recordset? At the mo I ampassing the WHERE String as argument.I got this technique from an Extreme Ultradev tutorial by Rick Curtisit looked quite ok:http://www.princeton.edu/~rcurtis/u...tutorial12.htmlI have to admit, one of the main reason for passing the WHERE string isthat I do not know how to do the string splitting / parsing and puttingtogether in a Stored Procedure. I bet T-SQL would be just as powerfulas VBScript if I just knew it well enough.What I liked about having built them on the web script was theflexibility allowing to potentially build an advanced search withouthaving to change the stored procedure - but this is not crucial I couldalways write several stored procedures or add parameters to the SP.Here is what I have achieved in this way:User can enter one ore more keywords separated by space.Search algorithm returns results across a number of fields where ALLsearch words are contained in any of these.Search results will always be formatted a certain way and displayed ina html table no matter how the search procedure / criteria is varied.Here is the algorithm (that now works in ASP)1. split search string into separate keywords2. build where condition based on single keyword, concatenating allsearched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")3. concatenate all these where conditions and pass to stored procedure.4. stored procedure takes care of all other logic (e.g. Joins, whichfields are searched etc.). It uses a string variable @SQL to build thecomplete search string and then doesexecute (@SQL);to create the recordset.I bet there is a way to move 1. 2. and 3. into the SP (and I would feelbetter if it was) but I don't have the expertise to do this. If anybodywants to help me this is very welcome.I can also post my original code to clarify, just want to avoid toolong posts.CheersAxel
View Replies !
View Related
Incorrect Syntax Near The Keyword 'THEN'
Hi Everyone,I really tried to not post this question but I gave up. I tried brackets,parenth...etc but nothing worked. I get this error message: Incorrect syntaxnear the keyword 'THEN'. Please help, I am learning SQL Server.thanks in advance.Ismailuse misselect CLAIM_DETAILS_HCVW.INTEREST, CLAIM_DETAILS_HCVW.NET, CLAIM_HMASTERS_VS.CLAIMNO,'AMOUNT' =CASE WHEN (CLAIM_DETAILS_HCVW.INTEREST IS NULL THEN '0' ELSECLAIM_DETAILS_HCVW.INTEREST + CLAIM_DETAILS_HCVW.NET)END,FROM CLAIM_HMASTERS INNER JOIN CLAIM_HMASTERS ON CLAIM_HMASTERS_VS.CLAIMNO =CLAIM_DETAILS_HCVW.CLAIMNOwhere CLAIM_HMASTERS_VS.CLAIMNO like '200601119%'--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200608/1
View Replies !
View Related
Incorrect Syntax Near The Keyword 'WHERE'.
Any Ideas as to this error message. I am trying to learn using ms sqlserver 7.0Below is the code I am using for an update to a MS Sql Database.<%@ Language=VBScript %><% Option Explicit %><html><head><title>Sample Script 2 - Part 3 </title><!-- copyright MDFernandez ---><link rel="stylesheet" type="text/css" href="../part3sol/style.css"></head><body bgcolor="#FFFFFF"><!--#include virtual="/adovbs.inc"--><center><%Dim oRSDim ConnDim IdDim NameDim StreetAddressDim CityDim StateDim ZipDim PhoneNumberdim sqlId = request.form("Id")Name = request.form("Name")StreetAddress = request.form("StreetAddress")City = request.form("City")State = request.form("State")Zip = request.form("Zip")PhoneNumber = request.form("PhoneNumber")Set Conn = Server.CreateObject("ADODB.Connection")Conn.open =("DRIVER=SQL Server;SERVER=(local);UID=;APP=AspRunnerProfessionalApplication;WSID=COMPAQAM;DATABASE=FriendsContactI nfo;Trusted_Connection=Yes")'Conn.Opensql="update FPFriends"sql=sql & " set Name='" & Name & "',"sql=sql & "StreetAddress='" & StreetAddress & "',"sql=sql & "Ciy='" & City & "',"sql=sql & "State='" & State & "',"sql=sql & "Zip='" & Zip & "',"sql=sql & "PhoneNumber='" & PhoneNumber & "',"sql=sql & " WHERE Id=" & Idset oRS=Conn.Execute (sql)response.write "<font face='arial' size=4>"response.write "<br><br>The record has been updated."response.write "</b></font>"' close the connection to the databaseConn.Close%><!-- don't include in sample code display ---><form><input type="button" value=" Close This Window "onClick="window.location='aboutus.htm'"><br><button onClick="window.location='menu1_1.asp'">Update anotherrecord</button></form></center></body></html>
View Replies !
View Related
Keyword Density/Count
Hi All,let me try to explain what I'm trying to accomplish - I really hope someonecan help.I have a table (tblArticles) which has the following:vcrKeywords varchar(2000)txtBody text(8000)vcrType varchar(128)and this is a sample of the datavcrKeyWords || txtBody || vcrTypekey1,key2,key4,key7,key9 || <snipped body|| Site5 Newskey1,key3,key6,key8,key9 || <snipped body|| Site5 Newskey1,key3,key4,key5,key9 || <snipped body|| Site5 Newskey1,key2,key5,key7,key8 || <snipped body|| Site5 NewsWhat I'm trying to accomplish is to return a keyword count based on thecontent of vcrKeywords (i.e. each comma seperated entry as a count.My SQL statement originally was :SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticleswhere vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESCHowever, this simply matches the entire vcrKeyword Column and not each commaseperated value.Is there a way in SQL that I can achieve this or do I need to use some kindof scripting language to accomplish it...?Regards,Carl.
View Replies !
View Related
DEFAULT Keyword Performance
I have a function which performs a query and returns a table. The oneparameter that can get passed in is a date which defaults to NULL.There is an IF statement in the function that will set the paramter toan actual date if null. If I call the function while passing in a datethe function comes back a second or 2 later. But if I pass in DEFAULTto the function, the same query takes 8 minutes. See code below andsample call below.CREATE FUNCTION fCalculateProfitLossFromClearing (@TradeDate DATETIME = NULL)RETURNS @t TABLE ([TradeDate] DATETIME,[Symbol] VARCHAR(15),[Identity] VARCHAR(15),[Exchange] VARCHAR(5),[Account] VARCHAR(10),[Value] DECIMAL(18, 6))ASBEGIN-- Use previous trading date if none specifiedIF @TradeDate IS NULLSET @TradeDate = Supporting.dbo.GetPreviousTradeDate()-- Make the queryINSERT @tSELECT@TradeDate,tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account],SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -tblTrade.[Price])) AS [Value]FROMHistorical.dbo.ClearingTrade tblTradeLEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@TradeDate =tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] ANDtblTrade.[Identity] = tblPos.[Identity])WHERE([TradeTimestamp] >= @TradeDate AND [TradeTimestamp] < DATEADD(DAY,1, @TradeDate))GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]RETURNENDIf I call the function asSELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')it returns in 2 seconds.If I call the function asSELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)in which GetPreviousTradeDate() will set @TradeDate to 09/25/2003 itreturns in 8 minutes.
View Replies !
View Related
No Records Returned Using Contains Keyword
Hi, This is my 1st thread. Hopefully somebody can help me with the full-text serach. I enabled full text serach on sql server 2k and created a catalog for a table I tried using the following sql statement but it doesn't seem to return any records although I used a like statement and got some records. Can anybody answer why this is so? Thanks! Select * from table1 where contains (field1, '"ESTATE PLAN"') Regards, Ronald
View Replies !
View Related
Equivalent Of FIRST Keyword In Grouping
Hi All I should probably know the answer to this after all my time with TSQL But my brain seems to have run out of steam. Have you ever tried Googling an answer for "FIRST Keyword equivalent" D'oh. Essentially I'm looking for an alternative to the SyBase/Access/etc FIRST Keyword Anyway I have LocationRef (Many Locations) PKeyResidentRef (a location has one or more Residents) PKeySP_Flag (I only want locations that have a Resident with an SP_Flag set in it)Primary_YN (a resident may or not be a Primary) I want to Return only one Location & one Resident per Location (Preferably the one that is Primary OR one that is not Primary so long as the they have the SP_Flag Set) If we had a FIRST Keyword I would Simply SELECT FIRST and Order By Primary having removed locations without a resident with SP_Flag set - Job done. The same Statement must run against both SyBase ASA & SQL Sever via Linked servers & OPENQUERY hence my frustration. I include the statement as it stands at the moment for completeness & to show I'm dealing with the complexities of a Crap & SubCategorized Schema (naturally not of my doing). SET @SQL ='SELECT * FROM OPENQUERY(Client3,''SELECT r1.LOCATION_REF LocRef, r1.Resident_Def ResRef,r1.Title,r1.First_Name, r1.Last_Name, r1.Date_Of_Birth, r1.Primary_YN FROM Resident r1 INNER JOIN Resident r2 ON r1.Resident_Def = r2.Resident_Def AND r2.Primary_YN = ''''Y'''' WHERE r1.Resident_Def IN (SELECT Entity_Ref FROM ATTR_DEF INNER JOIN ATTR_CHOICE ON ATTR_DEF.ATTR_CHOICE_REF = ATTR_CHOICE.ATTR_CHOICE_DEF AND ATTR_DEF.ENTITY_TYPE = 5 INNER JOIN ATTR_CATEGORIES ON ATTR_CHOICE.ATTR_CATEGORY_REF = ATTR_CATEGORIES.ATTR_CATEGORY_DEF INNER JOIN RESIDENT ON ATTR_DEF.ENTITY_REF = RESIDENT.RESIDENT_DEF WHERE UPPER(ATTR_CATEGORIES.TEXT) = ''''SP USER'''' AND UPPER(ATTR_CHOICE.TEXT) = ''''YES''''AND RESIDENT.Date_Of_death IS NULL) ORDER BY 1'')'Note: The Inner Join does'nt work for me because it excludes locations that have a resident with an SP_Flag but they are not a primary Any Ideas ? ThanksGW
View Replies !
View Related
Problems With Top Keyword In MS SQL 2000
I have a very strange problem, when i try to execute a query that includes the keyword TOP i get an error message: Incorrect sysntax near '3'. The query: =========== SELECT top 3 * FROM press_releases WHERE press_release_active=1 ORDER BY press_release_date DESC when i execute the query without the keyword top there is no problem. Service pack 3 is installed, if it matters.
View Replies !
View Related
Keyword Search Program
Hi, I am in the middle of developing procedure for keyword search for our website. Input parameter is a string consisting keywords in comma delimited format. This is the example of data I get from business group which I use to populate my key_search table. product_id keyword 1 Microsoft, training 1CA, DBA 1CA, developer 1CA, network admin 1AZ, DBA 1AK, developer 1MN, DBA 1MN, developer 1OH, developer 2Microsoft, training 2AZ, DBA 2AZ, developer 2IL, developer 2MN, DBA 2CA, developer 2NY, business analyst 2NY, DBA 2NY, developer 2NY, programmer 3Oracle, training 3finance 4Oracle demo 4logistic 4Oracle Financials 4Financial Analyzer They have provided search string examples like 'Microsoft, DBA, CA' or 'CA' or 'Microsoft, developer' or 'training' I have script ready to remove comma from the string and store words from the input string in a temporary table. But this is the easiest part. The Confusing part now for me is to write the optimal code for retrieving the exact match from the key_search table as there is no limit on how many words can be in the string, it can be 1, 2, 3 or more. Any suggestions on how should I handle this search?
View Replies !
View Related
Order By Using Reserved Keyword?
Hi, We have a table with a column called 'text' Yes, text is a reserved keyword for data type. I can select it 'SELECT text FROM thistable' but when I try to use it in the order clause, 'SELECT text FROM thistable ORDER BY text', I get this error: 'The text, ntext, and image data types cannot be used in an ORDER BY clause' I know, you're not supposed to used reserved keywords but the person who initially created this didn't know. We would prefer not to change the column name as this would require tons of changes in the code & COM object. Is there a way to order this column? Thanks.
View Replies !
View Related
Stored Procedure And The IN Keyword
I have an ASP page that sends parameters to a stored procedure on a SQL 7.0 Database. However the page allows for multiple selections (ie multiple selections from a list box), mandating that my SP must handle the data with the IN keyword (ie select * from tableX where X in (@parameter). However I cannot get the multiple values for the same parameter to be accepted by the SP. My problem is that the value for the parameter (sent by the ASP page) is something like "x, y, z" and the SP places this in the query it is executing as follows : Select * from tableX where Y in ("x, y, z" ) rather than Select * from tableX where Y in ("x", "y", "z" ) How can I get my SP to take the parameter and place it into the SP correctly? Thanks
View Replies !
View Related
|
TRACKER
