i have a list of ID's (indexed) more than 2500 items.
first i have to select these records from one and then Update in
another table. when i pass the list to sql with IN() operator in
select query it takes too long (about 2 mins) and when i run update
query with same criteria it takes too much time.
i am using VB6 for development.
the field is indexed.
what i should do to improve performance???
Khurram Rao
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
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:
Just a quick question that I really couldn't find using google or anything because it was too bland.
How do you return the whole database while still using the LIKE operator.
I have a textbox that you can use to search the database but I want all the data to be displayed underneath the box so you can "browse" the data if you don't know the name of what you are looking for.
What would I put as the default value? I used just defaultvalue="%" and that worked in 1 area, but not another. Is this the right way?
Thanks,
Chris
I have a query and for the life of me I cant figure it out.
I have a web form with a number of textboxes on it e.g. Title, subtitle, isbn. I also have a search button. This search page allows a user to enter any combination of fields in the form and then when they click the search button, a datagrid is diaplyed showing the results (gets the results from a SQL DB).
I have got the code working if the user selects say one item, but I dont know how to allow it so that the user can enter any combination of items in the form and the search button will search for it based on what the user says. I think im gettng confused with my logical operators in the sql statement.
If any one can take a look I would be greatful.public void BindData(){String @BookID = TxtBookID.Text;String @Title = TxtTitle.Text;String @Subtitle = TxtSubtitle.Text;String @ISBN = TxtISBN.Text;String @AuthorFName = TxtForename.Text;String @AuthorLName = TxtSurname.Text;
SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID_Internal, Title, Subtitle, ISBN, Edition, Publication_Date, Author.First_Name, Author.Last_Name FROM Book, Author WHERE Book.Author_ID = Author.Author_ID AND Book.Book_ID_Internal = '" + @BookID + "' AND Book.Title = '" + @Title + "' AND Book.Subtitle = '" + @Subtitle + "' AND Book.ISBN = '" + @ISBN + "' AND Author.First_Name = '" + @AuthorFName + "' AND Author.Last_Name = '" + @AuthorLName + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");GridView1.DataSource = ds;GridView1.DataBind();}
protected void BtnSearch_Click(object sender, ImageClickEventArgs e){BindData();}
Hi :)
I have a select statement that consists on something like this :
SELECT * FROM Products WHERE Ref LIKE '%" & Search & "%'
well this works ....but the problem is that i have some Ref in this format "MC-909" and if the user makes a search with the word "MC909" ...it dosent return any value ...
How can i solve this ??
Thanks in advance
Anyone know why this simple query won't work??
this does work:
declare @stat char(10)
set @stat = 'A'
select * from table where status in (@stat)
this does not work:
declare @stat char(10)
set @stat = "'A','B'"
select * from table where status in (@stat)
i've tried 'A,B'
hello,
I got a condition in the query , as shown below:
where nt.Project_type='A' and nt.status='Done' and nt.project_id like '06%'
I also need to add an additional condition : like '07%'. How can I do this, i. e I need the projects where the type = A , status = Done and project_id starting with 06 and 07.
Thanks for any help
Hi ,
This is my first message on this forum. Well, I want to write a query which finds the length of the employee names whose name starts with J, A or S?.
This is what I did:
SELECT LENGTH(ENAME) FROM EMP
WHERE ENAME LIKE 'A%' OR LIKE 'J%' OR LIKE 'S%'
This query does not work. Can anyone tell me what is wrong with it?
Thanks,
Using Like operator
I want to display Names ending with XYZ. But, the starting letter should be either A, B OR C.
Example:
AXYZ
BXYZ
CXYZ
Tell me how to do that
Thanks in Advance
I have a column named tags which contains tagnames seperated by commas now i have to compare this column with a parameter named tagname which contains a single tagname how to do this.Please help me
here is my procedure
ALTER PROCEDURE [dbo].[ProcSoftwaresGetSoftwaresbyTags]
(
@FileTag Varchar(64)
)
AS
BEGIN
SELECT Category.CategoryName, Softwares.SoftwareFileID, Softwares.CategoryID, Softwares.FileName, Softwares.FileImage,Softwares.FilePath, Softwares.FilePublisher,
Softwares.Adminrating, Softwares.FileDownloadCount,substring(Softwares.FileDescription,1,200)as FileDescription,convert(Varchar(12),Softwares.FilePostedDate,109)as FilePostedDate,Softwares.FileSize, SystemRequirements.OperatingSystem
FROM Category INNER JOIN
Softwares ON Category.CategoryID = Softwares.CategoryID INNER JOIN
SystemRequirements ON Softwares.SoftwareFileID = SystemRequirements.SoftwareRequirementID where substring(Softwares.FileTag,0,3) LIKE substring(@FileTag,0,3) and FileStatus=1
END
Hello everyone,
I have two tables athat are structurally identical. I wish to obtain rows that are in the first table that are not in the second one (e.g. the EXCEPT operator). EXCEPT is not supported directly in TSQL...
Does anyone know how to do this??!?!?
Thank you for your time
Chris
Hi,
Can you just tell me how to use LIKE Operator(Syntax) please.I am new to SSRS2005.
Hi,
I'm having problems using the between operator in a query statement.
Select *
from <mytable>
where date between @date1 and @date2
The date values with a hour specified, aren't returned. What is the approach you would recommend here?
Thx
EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @fromDate + '00:00:00' and @toDate + '23:59:59'
this seem to work, but I'm not sure if this is correct
Hi,
I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated.
Thanks
Hi,
please do help. Have been wrestling with this for about 3 hours and read a buncha forums + searched the tutorial lists.. AARRGH!
Anyhow,
I have to paginate a datalist (and I really can't use a datagrid because of the layout, blame the bluddy graphic designer)
I want to return the top 8 rows, the next 8 top rows and so on.
This is the sql string I have:
'retrieve pagination in order to construct the rest of the sql string
Dim startrec As Integer
If pageno = 1 Then
startrec = 0
Else
startrec = (pageno - 1) * pagesize
End If
' this builds the sql string, getting ONLY the records we need for this. Page size is a constant expressed on the base page
' startrec is the record where I want to start in the db.
strsql = "select top " & pagesize & " * " & strsqlbasic & " and itemID>" & startrec & " order by itemnotes1 asc"
noresults.text = strsql & " <br> " & searchwhat & searchfor
strsqlbasic is constructed elsewhere and is just the 'from X where y = var
Of course, this returns all records where the value of itemID is greater than the value of startrec. How would I change this so it returns the next 4 rows starting from the row number given by the value of startrec?
thanks for any help.
strsql = " select * from dic where english='" & SearchBox.Text Or TextBox4.Text & "'"The machine said Input string was not in a correct format. How can i select database with or operator in asp.net .Is it possible to select database in sql from more textbox ?
View 1 Replies View RelatedConsider the following SQL query:
SELECT ENAME,SAL
FROM EMP,ASG,PAY,PROJ
WHERE EMP.ENO=ASG.ENO
AND PAY.TITLE=EMP.TITLE
AND ASG.PNO=PROJ.PNO
AND ASG.DUR=48 AND BUDGET>200000
Give the possible operator trees:right-deep,left-deep and bush
Tank you very much!
How to use "INTERSECT" Operator with SQL Server 2000 ?
I tried this :
select ...
Intersect
select ...
but it doesn't work ! I have an error message : "Incorrect syntaxt near the keyword intersect" !
Is using some variation of (i forget which... Charindex / Patindex) operator better than using a query with a like operator.
Our app requires wildcard searching. So what is the absolute best way to get the searches done fast???
Hi,
I have a table which has the data as follows
it has firstname and lastname:
Firstname lastname
1. luke Duke
2.E%ade sony
3.U%rrer desert
4.Arr%the Densis
etc...
Now my question is when i query for the retrieval of data asking for the firstname which has second character as %. so now how do i query it.
select firstname, lastname, age from Emp_US2233 where firstname like '%_%';
is this query is correct and if not can u plz tell me how do i query it.
regards,
Uma.
SELECT SkillNo, SkillName FROM [Skill] WHERE SkillName = " & oRs.Fields(lngCol).Value
whats wrong with this
I am trying to figure out this query and they are using *= to compare two items. What does *= do? I cant find it anywhere?
View 2 Replies View RelatedHoping someone can shed some light on this one, using MSSQL 2000,
I have a query like this;
Code:
Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data T where T.TransactionRecordClass like '10000002%'
which will return around 10000 records in under 1 second.
My issue is that I need to make this part of a function, but as soon as I add parameters/variables, it goes from returning in under 1 second, to 11 minutes!!!
Code:
DECLARE @TRC_ID nvarchar (9)
SET @TRC_ID = rtrim('10000002') + '%'
Select T.TransactionRecordClass, T.ClassDetail from [remoteserver].otherdb.dbo.Data T where T.TransactionRecordClass like @TRC_ID
There is a non-clustered index on the 2 fields being returned , and about 77 million records in the table. I have attempted using Index hints, but found they are not allowed on remote queries..... So... any clues????
Thanks in advance for any help
Hello folks,
I've got a table like this
PRODUCT_ID LABEL_ID
----------- -----------
6 1
6 2
7 1
1 1
2 1
I need to have a select statement to take all the products with the LABEL_ID containing 1 AND 2. I can't use the IN operator as it will return me results with LABEL_ID 1 OR 2. Is there any solution to this? Sorry I'm relatively new to mysql and not familiar with the select statements.
I'm currently working on something like a search engine. Hope to have some good advice here :)
Hey guys,
I was just thinking, is it possible to send alerts via the Managment/SQL Server Agent/Operators/Alerts to an SMS system?
Is it even technically posibble?
Actually I am trying to develop an alert system that reads the database table periodcally, the data being read is a datetime, once it reaches the time, I need to send an sms to the person.
Any suggestions? ideas? Quickest solution?
Hello,
I have to pass a string list separated by Commas ( For example, 'Item1','Item2') from a C# code to run a Stored procedure which has a Select query which has an IN Operator in it.
While passing though the data are there in the database, Stored procedure doesn't recognise the passed string list as a list instead it recognise as a whole string, so query doesn't return the expected resultset.
Please help me on this how to pass a stringlist to a stored procedure from C#?
Thanks,
Muthu
I have the following SQL statement"
DECLARE @SORT VARCHAR(256)
DECLARE @FROM VARCHAR(8000)
DECLARE @SELECT VARCHAR(8000)
DECLARE @ON VARCHAR(8000)
DECLARE @SQL VARCHAR(8000)
DECLARE @WHERE VARCHAR(8000)
SET @SORT = 'PaymentAmount'
SET @SELECT = 'SELECT PY.PaymentId
, PY.PayeeId
, PY.PartyAddressId
, PY.DistributionId
, PY.EntitlementId
, PY.DeliveryTypeEnumItemId
, PY.AccountPaymentId
, PY.ParentPaymentId
, PY.PaymentAmount
, PY.PaymentDate
, PY.PaymentStatusEnumItemId
, PY.PaymentStatusDate
, PY.ReleaseRunId
, PY.ReleaseDate
, PY.AccountTransactionLogId
, PY.AccountStatusEnumItemId
, PY.AccountStatusDate
, PY.AccountPaidAmount
, PY.ReconciledInd
, PY.UndeliverableInd
, PY.ReissueNote
, PY.CreateDate
, PY.CreateId
, PY.ModifiedDate
, PY.ModifiedId
, DS.Description
, AC.Description
, AC.AccountProvider
, AC.AccountId
, PT.Name
, PA.AddressLine1
, PA.AddressLine2
, PA.City
, PA.State
, PA.Zip5
, PA.Zip4
, PE.clm_no
, CM.clmnt_idno '
SET @FROM = 'FROM Payment PY (NOLOCK)
JOIN (SELECT DISTINCT
PY.AccountPaymentId, PY.' + @SORT +
', ROW_NUMBER() OVER(ORDER BY PY.' + @SORT +') AS RowNum
FROM Payment PY (NOLOCK)) AS SQ '
SET @ON = 'ON (SQ.AccountPaymentID = PY.AccountPaymentID)
JOIN Distribution DS (NOLOCK)
ON (DS.DistributionId = PY.DistributionId)
JOIN Account AC (NOLOCK)
ON (AC.AccountId = DS.AccountId)
JOIN PartyAddress PA (NOLOCK)
ON (PA.PartyAddressId = PY.PartyAddressId)
JOIN Party PT (NOLOCK)
ON (PT.PartyId = PA.PartyId)
JOIN Payee PE (NOLOCK)
ON (PE.PayeeId = PY.PayeeId)
JOIN clm CM (NOLOCK)
ON (CM.clm_no = PE.clm_no) '
SET @WHERE = 'WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
AND ((@PayeeName IS NULL) OR (PT.[Name] LIKE '''%''' + @PayeeName + '''%'''))
AND ((@AccountId IS NULL) OR (AC.AccountId = @AccountId))
AND ((@DistributionId IS NULL) OR (DS.DistributionId = @DistributionId))
AND ((@PaymentDate IS NULL) OR (PY.PaymentDate = DATEADD(day, DATEDIFF(day, 0, @PaymentDate), 0))) -- Ignores the time
AND ((@PaymentNumber IS NULL) OR (PY.AccountPaymentId = @PaymentNumber))
AND ((@IsReconciled IS NULL) OR (PY.ReconciledInd = @IsReconciled))
AND ((@AmountIssued IS NULL) OR (PY.PaymentAmount = @AmountIssued))
AND ((@AmountPaid IS NULL) OR (PY.AccountPaidAmount = @AmountPaid))
AND ((@IssueStatus IS NULL) OR (PY.PaymentStatusEnumItemId = @IssueStatus))
AND ((@AccountStatus IS NULL) OR (PY.AccountStatusEnumItemId = @AccountStatus))
AND ((@PayeeId IS NULL) OR (PE.PayeeId = @PayeeId))
ORDER BY PY.' + @SORT
SET @SQL = @SELECT + @FROM + @ON --+ @WHERE
SELECT @SQL
When I try to execute it, I get the following error:
Msg 402, Level 16, State 1, Line 71
The data types varchar and varchar are incompatible in the modulo operator.
It appears to not like this line:
SET @WHERE = 'WHERE RowNum BETWEEN (((@Page * @PageSize) - @PageSize) + 1) AND ((@Page * @PageSize) - @PageSize) + @PageSize
Could anyone shed some light on why it's puking on this? And is you have any possible suggestions to get around this?
Thanks a million!!!
Im not getting data when I execute the below 0 Rows:
select mydate from [dbo].mytable
where convert(varchar, mydate,101) between '11/18/2013' and '02/08/2014'
However the below gives results...
select mydate from [dbo].mytable
where convert(varchar, mydate,101) between '01/01/2013' and '02/08/2014'
We have a column in table which represents operator like
>,>=,<,<=, =
And we want to use it in sql query depending on the condition for each id column in table
Our table is
id int
operator nchar(10)
1,>=
2,<=
3,>
4,<
5,=
.
.
.
.
I need to use the operator from table into where clause for using condition for all the ids instead of single operator. I tried it using the dynamic query but how can we execute it for each id in single query.
Is it possible to write a select statement using IF operator just as we use CASE
Case WHEN #1 IS NULL THEN #0
WHEN #0<=#1 and #1 IS NOT NULL THEN #0
when #0>#1 and #1 IS NOT NULL THEN #1
End
can we do the same using if, this is what i did and failed
IF #1 is NOT NULL THEN
IF #0<#1 THEN #0 Else #1 END
ELSE #0
End
Thanks in Adv
Anybody know how to use the In Operator in the select stmt when one of the values is NULL? Here is my code:
select * from Client
where AgencyId is not null
--and AgencyName is not null
and left(controlnumber,2)<>'72'
and left(controlnumber,2) <>'13'
and CPAFirmId IN ('0','',NULL)
Since the IN operator requires all values to be in quotes, it does not work properly when NULL is specified. I also tried 'NULL' but that gives an error.
I suppose to write query using in operator which contains dynamic values.
declare @var varchar(30)
set @var = '''anatr''' + ',' + '''alfki'''
select * from TableName where ColumnName in (@var)
Above written batch doesn't returns any values,
whereas below return query returns value
select * from TableName where ColumnName in ('anatr','alfki')
What would be the problem..