Problem Querying 2 Fields
Feb 7, 2006
I just want to know if the searched for item appears in either Field in my table. The search works when i just use 1 Field, but i always get the below error whenever i add the second field to the query..
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'TERM Glossary.LIKE '%c%' Visible= True'.
I looked around and found some info on using UNION ALL but i get the same error. So, both the below queries give me an error.
PHP Code:
SELECT * FROM Glossary
WHERE AKNOWAS LIKE '%c%' OR
TERM LIKE '%c%' Visible= True ORDER BY TERM ASC
PHP Code:
SELECT * FROM Glossary WHERE
Glossary.AKNOWAS LIKE '%c%'
UNION ALL
SELECT * FROM Glossary
WHERE TERM LIKE '%c%' Visible= True ORDER BY TERM ASC
View 2 Replies
ADVERTISEMENT
Jun 5, 2008
Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database.
for example instead of declaring a parameter like this:
@ProjectDescription varchar(500)
I would like to do something like this:
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
Thanks for the help!
View 4 Replies
View Related
Sep 19, 2006
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size." Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung") AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
View 1 Replies
View Related
May 16, 2007
Hello:
I have designed a CV database with complete CV stored in a TEXT field. There is a keyword search which queries the TEXT field also. The query conditions are defined in T-SQL submitted through an ASP page. There is about 20,000 records now. Now while querying the database for keyword search I am receiving time out errors. Is there any solution other than Index server to rectify this situation. How can I speed up the query execution time. Please advise.
Rgds
pooja
View 3 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jan 26, 2008
sir
I have got this error message to establish connction with recordset vb .net, Can you please rectify this
Too many arguments to 'Public Overridable ReadOnly Default Property Fields() As ADODB.Fields'
my code like this
rs = New ADODB.Recordset
rs.Open("Select * from UserLogin where userid='" & txtUserName.Text & "'", gstrDB, DB.CursorTypeEnum.adOpenStatic)
If txtUserName.Text = rs.Fields.Append(userid) Then
MsgBox("OK", MsgBoxStyle.OKOnly, "Confirmation")
End If
thanks
View 1 Replies
View Related
Jul 23, 2005
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
View 3 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Jul 20, 2005
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
View 1 Replies
View Related
Jun 30, 2000
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like:
UPDATE "TABLENAME"
SET DATAFIELD1 = DATAFIELD2
WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
View 2 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Jan 21, 2008
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
View 1 Replies
View Related
Sep 9, 2007
I'm a newbie to SQL and wanted to see if someone could help me write a query. I store historical options data in a SQL data base and I'm trying to create a query that will retrieve all ATM (at the money options).
For each unique stock symbol (STOCK_SYMBOL) in my database find the strike price (STRIKE) which is closest to the stock price (UND_LAST). This will be refined by the EXPIRATION and OPT_TYPE
The following query returns all options not just ATM options.
SELECT STOCK_SYMBOL, UND_LAST, STRIKE, EXPIRATION FROM DATA
WHERE EXPIRATION > '2007-02-01 00:00:00'
AND EXPIRATION < '2007-02-28 00:00:00'
AND OPT_TYPE = 'CALL'
Related DB fields.
STOCK_SYMBOL
STRIKE
UND_LAST
OPT_TYPE
EXPIRATION
Any help would be appreciated.
View 2 Replies
View Related
Nov 8, 2007
I am using MS SQL Server 2005 Import and Export Wizard (.net framework Data Provider for mySAP Business Suite. )
as a interface between SAP and MS SQL 2005.
Once the connection is created i can use basic query to fetch the data from SAP tables
ie., SELECT * FROM AFPO
But say I want to EXTRACT data just for month of July from AFPO.
AFPO does not have date field so you have to join AFPO to AUFK and AUFK to COEP to fetch the date as a criteria to restrict data for the month of July .
This can be done in ABAP by view or Interim Table, if you have Developer Key.
Can anyone please tell me if aforesaid is possible with MS SQL Server 2005 Import and Export Wizard query ?
View 3 Replies
View Related
Nov 9, 2006
Hello,
I want to find all the entries where the date field is a specific date; so I may have 5 entries with the date 1/1/2006, and I want to find all of those. However, datetime fields in SQL Server also have the time, so how do you handle that? Can you just say where requestdate = getdate() to get all of the entries that have records for today?
How does time affect this?
View 5 Replies
View Related
Sep 28, 2007
Hi,I am new to sql and was wondering if someone could help with this select statement. please don't laugh! SELECT * FROM product_detailsWHERE MemberId = @MemberIdINNER JOIN Member_Ratings ON product_details.MemberId = member_ratings.MemberIdSELECT COUNT(*) FROM member_ratings AS FinalHighRating WHERE member_ratings.MemberId = product_details.MemberId AND member_ratings.Rating = 5 Any questions? Thanks!
View 7 Replies
View Related
Oct 31, 2007
Hey,
What is the best way to query two datetime values to see if they are alike? I need to determine if an [AccountEndDate] is today (both values are datetime). Using "Like" does not work.
Thanks!
View 4 Replies
View Related
Jan 27, 2008
If you create a view such as CREATE VIEW TestView AS Select * FROM customers_table WHERE CustomerID between 213 AND 3443 Each time you want to select from the view - is the View going to perform the WHERE clause on the customers_table, or not? Or does the view already have the virtual rows?, meaning it does not have to perform a where clause (WHERE CustomerID between 213 AND 3443) on the customers_table, and just performs an inner join with the rows it already has?
View 5 Replies
View Related
Aug 10, 2005
I have a usage table Aaccessdate (datetime)useridpageidI need to write a sql that would do a report on a monthly basis. ( how many pagehits per month)All is fine - and my SQL looks likes thisselect count(pageid)from A.The twist here is , I do not want to count those records if the same user visits the page on the same day.For Eg02/03/2005 10:09:09.000 user1 page102/03/2005 15:09:09.000 user1 page102/03/2005 11:09:09.000 user2 page202/03/2005 16:09:09.000 user3 page3I want my count to give me back 3 instead of 4.Can this be done?ThanksRajini
View 4 Replies
View Related
Mar 20, 2006
I have a table containing prices. This table will be queried very often to provide quotes for clients.So to ease the burden on the server I want to cache the table and then just query the cached version.However it seems that I can only cache the table as a datatable. This means I have to query the datatable to get the prices for each quote.I'm not sure how to query a datatable. Is ther syntax similar to querying a SQL table?In fact is this best way to go about things?Any help would be appreciated.G
View 4 Replies
View Related
Apr 20, 2006
Hi guys
im struggling to see what im doing wrong with the following SQL, wondering if it has something to do with the subquery being a view...cant think why though
select value from table
where type = 'Extra Mailbox'
and active = 1
and value not in
(select login from view)
order value
The view returns a list of logins and the type field is also a list of logins. I want to filter the list of logins that do not appear in the subquery....
Any ideas?
Thanks in advance for any responses.
Ging
View 2 Replies
View Related
Oct 1, 2004
I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?
View 5 Replies
View Related
Apr 30, 2008
Hello all,
I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
does anyone have an idea?
thanks in advance.
View 2 Replies
View Related
May 23, 2008
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
View 3 Replies
View Related
Jun 12, 2008
how to write a query to find the no of tables present in that database.
Any suggestions.
View 4 Replies
View Related
Jun 12, 2008
Hi All
Thx in advanced for any help anyone can offer me.
I have 2 tables Staff and Class which are as follows
Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .
Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Thx for any help Scott.
View 2 Replies
View Related
Jan 23, 2007
Hi there,
Is there a way where I can run a query or a store procedure where i can retrieve all the contraints attached to the tables in the database?
thanks
View 1 Replies
View Related
Apr 21, 2007
Hi All,
I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates
(
@SupplierGUID uniqueidentifier
)
AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS
(SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
GO
I would appreciate any help!
Thanks,
pp
View 2 Replies
View Related
Sep 8, 2007
Guys ...
I have a SQL SERVER 2005 running on my local machine. My local machine is also configure for web applications through IIS.
There is a remote oracle db, which is maintained by another department. They gave us read only access but they discourage web access over their oracle database.
Even if you try to access it through web ... they come to know that certain logins are trying to access it through web ... How ... i dont know.
Now what i did is linked that oracle database with my SQL server 2005 and developed an ASP.NET application which is indirectly accessing oracle database though my SQL Server 2005.
Whenever a user visits my web application on the backend the authentication done on my sql server but the query runs on linked oracle server... as I open connection to my sql server 2005
do you guys think that Oracle database administrator can ever know that his oracle db is access through a web application. whereas they allow us to copy required data on our machines through SQL Server 2005?
View 4 Replies
View Related
Jul 23, 2005
Hello all,I'm trying to run a query to make a report. My database is a incidentreporting database. I'm tryng to make a monthy report for incidents.The field I need to query in the date field which is a nvarchar in theform of 01/01/04 and 01/01/2004. I ran a query that looks like this:SELECT incident, doccur, IDFROM dbo.IncidentWHERE (doccur between '01/01/2004' and '01/31/2004')I get some results that look like this:Unsecured doors01/19/0492INTOXICATION 01/17/0477Bill Door entry door 01/28/03130Hit & Run01/21/04105Customer complaint01/02/0370Customer complaint01/02/0491PRINTER MALFUNCTION01/22/04111Customer complaint01/30/042322Trash Smoldering01/15/0451LOST01/02/0380BROKEN GLASS PANEL01/13/0442B.I.A. Assist01/04/03189GAS LEAK01/06/048UNCHANGED CASH BOX01/11/0440Intoxication01/17/0469Intoxication01/02/0471Intoxication01/17/0472Employee accident01/17/0473GREASE FIRE01/18/0474Verbal Dispute01/17/0475PANHANDLING01/17/0476Near Miss/Water backup01/18/0478Unsecured Arcade Door01/19/0493Intoxication01/18/0479Intoxication01/02/0481SUSPECT/WANTED01/18/0482Intoxication01/18/0483Property Damage01/20/0384Unsecured Bingo Snack Bar01/18/0485PANHANDLING01/18/0486Employee accident01/19/0487Unauthorize of proper exit01/19/0488Safety Hazard01/19/0489Key control violation01/02/0390Cracked keno ball01/23/04116Employee accident01/19/0494delay in drop01/27/2003128test01/01/20053763As you can see, the querey will give me the month and day I ask for,butnot the right year. Some to the data has 2 digit years and some have 4digits. How do I design the query to give me the year I ask for.Any assistance will be greatly appreciated
View 7 Replies
View Related
Jan 10, 2007
Hi,I'm having two databases in the name of n1,n2 having same designI want to query both the databases by giving the database name in queryDatabasesN1N2Table(both database contains same table)DetailTable detailsNo -intName -varchar(10)Now i want to check the detail table in both n1 and n2 using the selectqueryFor that I have written query it is not workingselect * from N1.DetailSelect * from N2.DetailThe above query is not working in query analyser,I'm getting error as"Invalid object name"In query analyser the Northwind database is currently selected, anywayI have given the database name it has to query from N1 database. How touse a select query with database name?Mani
View 1 Replies
View Related
Jan 31, 2007
I realize that this may not be the correct ng for this question, andif so, I'd appreciate a pointer to the correct group.I am not including DDL/DML for this question, because I do not believeit is relevant.What I have is a SQL query, which when executed in Query Analyzertakes about 10-12 seconds. From the time I issue the query, I see aninitial lag of about 1 seconds after which the grid starts gettingpopulated, and then runs its course.The UI responsiveness is impressive. Almost immediately, well, within1 second at most, I see data appear from the server into my queryanalyzer grid.I'd like to get the same responsiveness if possible on my clientapplication written in C# using ADO.NET.I use the SqlDataReader class off the SqlCommand class to make thesame query (identical), and yet it takes close to 9 or 10 secondsbefore even the first row is retrieved by the application. I am notsaying that it takes 9 or 10 second for my DataGrid to start beingpopulated; it is simply that the first Read operation of theSqlDataReader after I connect (which is instantaneous), that takes 9to 10 seconds. Once the data retrieval starts, the grid populates inshort order.Are there any pointer by which I can get just a few rows fast enoughto give the user the impression that the query is executing. In otherwords, what techniques can I use to get the same sort ofresponsiveness as in the SQL query analyzer?In case it is relevant, there are some 250,000 rows returned by thequery.Thanks for all helpful responses in advance.
View 8 Replies
View Related