I am facing one issue while using LIKE operator in Update statement. Also want to understand why SQL behaving like this
Below is the scenario:
--Master INFO
Search Operator Set Value
Handling Charge Value 1
Handling Charge - PositioningValue 2
Handling Charge - IntermodalValue 3
Terminal Handling ChargeValue 4
[Code] ....
create table deltavalues (planid varchar(10), baseval float, highval float)
Insert into deltavalues values ('1002', 2000,5000)
Insert into deltavalues values ('1003', 1000,0)
Insert into deltavalues values ('1004', 500,0)
Insert into deltavalues values ('1005', 1000,2000)
--I have below query , basically getting emnployee table balance between deltavalues table baseval column and highval column
select * from employee e join deltavalues d on e.planid = d.planid
where e.balance between d.baseval and d.highval
but here what i am requesting you is how can i change the highvalue to not include if it is 0
for example above example plan 1003, 1004 has highbal as 0 so i want to write a query to get employee data between 1000 and anyvalue (> 1000 - no limit)
same way for 1004 between 500 and no end values (infinity)
Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date]
INTO temp1
FROM [G_L Entry] as A ,[G_L Account] as B
where A.[G_L Account No_]= B.[No_]
SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_]
INTO temp2
[code]....
I have the got below T SQL query to fetch the matching record
DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int
[Code] .....
-- result - 'Orange'
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
-- result - 'Orange'
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies View RelatedI have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
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:
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
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