Problem In Seaching With Like Or Equals
Jan 14, 2008
Hello,
I perform queries on tables with 500,000 plus records, when I use the like clause or the = for record names with large numbers I get different counts with each run. As an example if I was searching retail stores and k-mart was one I get counts like the following
1st run: k-mart 2200
2nd run: k-mart 2240
3rd run: k-mart 2197 etc. etc.
The records may be k-mart inc or kmart inc or k-mart etc.
If anyone has a better understanding of how the items are searched for and knows a better way please respond.
Thank you in advance.
View 9 Replies
ADVERTISEMENT
Jun 2, 2004
I get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
View 3 Replies
View Related
May 18, 2004
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
View 3 Replies
View Related
Jul 23, 2005
Hello,I need to do a seach in multiple columns for a certain word. With SQLI have to use one specific column right?I.E. select * from DB where Column1 like '%search%'That works.But what if I want to seach multiple columns in the table for thesearch word?You can't do this:select * from DB where Column1, Column2, Column3 like '%search%'Is there a way to do this?Thanks,Tmuld.
View 1 Replies
View Related
Mar 13, 2002
i'm struggling to write an sql select statement that will return records on this criteria:
any time a certain word (not part of a word) in a certain cell, i.e. if i'm looking for 'and' i dont want to get 'the sand was hot' but i do want to get 'cats and dogs'. i cant presume that there will always be a space on iether side of the word because of punctuation marks and times when the word is at the begining of the cell 'and then we...'
anyone have any advice??
thanks,
vauneen
View 1 Replies
View Related
Mar 7, 2006
hi,In MySql,we can run below query
select appid,dvbpath,business from ocgxmldbinfo group by appid
but in MSSql,it can't!so i want to know whether the following query equals it?
select distinct appid,dvbpath,business from ocgxmldbinfo order by appid
thanks!
View 6 Replies
View Related
May 27, 2008
why following happens? can anyone explain the logic?
if 'a ' = 'a'
select 'equal'
else
select 'not equal'
--------
result: equal
View 13 Replies
View Related
Aug 8, 2006
I have a table called "Inc_Exp_Accounts". The table has ledger account numbers and names. It also has a third column called "IsExpense". The IsExpense column is a bit field = 0 (false) if the account is an income account and = -1 (true) if the account is an expense account. I only have income and expense accounts. I do not have other accounts such as asset accounts. The distinguishing thing about income accounts is that the account number (an nvarchar(11) type) is a '3'. Anything else makes it an expense account.
I would think I can use the following SQL statement to correctly set the IsExpense column:
UPDATE Inc_Exp_Accounts
SET IsExpense = IIF(LEFT(Account_Number, 1) = '3', False, True)
But nooo, I cannot! I cannot even run it because Visual Studio SQL Syntax checker rejects it. It complains about the second '='. If I change the second '=' to '>', then it complains about '>'. Therefore, I think I have the particular character about which it complains.
Ths specific complaint (error message) is: "Error in list of function arguments: '=' not recognized.
Unable to parse query text.".
So what is wrong?
View 3 Replies
View Related
Jul 6, 2006
Is it possible to use wildcards with an equals statement? Such asSELECT * FROM Table WHERE City = '%' AND State='Ca'Bascially just stating where city equals anything...I know you can do it with a LIKE statement such as...SELECT * FROM Table WHERE City LIKE '%' AND State='Ca'but is that very efficient?The reason I want to do this is because I want to programmitcally set the city, so just ommiting it won't work
Also, using City LIKE '%' seems to not include NULL...is there anywayto include NULL as well as anything else?
Thanks for your help!
View 2 Replies
View Related
May 30, 2006
Hi,
How do I round when number is ending with 5 for example:
When the number next to number 5 is an even number
24.5 = 24.0 round down
0.245 = .24 round down
1265 = 1260 round down
when the number next to number 5 is an odd number
23.5 = 24.0 round up
0.235 = .24 round up
1755 = 1760 round up
Thanks in advance!
View 5 Replies
View Related
Nov 13, 2014
I have a table with a column of type date. I want to select all records where the date column = today. I tried
Select * from myTable where DateSent = GetDate()
but it returns no records.
Do I really have to use this slow method?
select datesent from Messages where convert(varchar,DateSent,103) = CONVERT(varchar, GetDate(), 103)
View 2 Replies
View Related
Feb 11, 2008
This seems to be a rather old problem (http://www.themssforum.com/SQLServer/Does-empty/) but I couldn't find an answer yet.
The problem is: I have two tables t1 and t2 where t1 is a staging area of t2.
t1: (id int not null, phone varchar(30))
t2: (id int not null, phone varchar(30))
Data in t1: (1, '') <- empty string
Data in t2: (1, ' ') <- a blank
Comparing t1.phone with t2.phone results in equality which in my opinion isn't correct.
The question ist: How can I change the behaviour of SQL-Server to result in inequality so that the change in my staging table is detected correctly?
Thanks in advance
Fridtjof
View 4 Replies
View Related
Aug 2, 2007
Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.
Thanks
Gary
View 3 Replies
View Related
Jun 27, 2007
Hello,I currently have Table1 and View1.View1 is a query from 2 or 3 tables that works fine on its own.However in my current query if I try to use it...something like...SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3FROM View1 a JOIN Table1 b on a.col1 = b.col1WHERE a.col2 <b.col2 OR a.col3 <b.col3It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannotresolve collation conflict for not equal to operation."Clearly I need to use collation between Table1 and View1, But I dontknow where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" andhow? this is the collation set on Table1.Thank you!Yas
View 1 Replies
View Related
Sep 3, 2015
Using this IIF statement:
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
View 4 Replies
View Related
