How To Use Operator In Where Clause
Feb 13, 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.
View 2 Replies
ADVERTISEMENT
Oct 3, 2007
I am migrating a classic ASP application that ran on Access to ASP.NET with SQL Server 2005 DB. I am having some trouble getting a query to work. It runs part of our courses section of the web site.
SELECT CREDIT_DATA_LOCCODE.Loc_Code, CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Fiscal, CREDIT_DATA_COURSE.Term, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Status_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Course_Number2, CREDIT_DATA_COURSE.Section_Number, CREDIT_DATA_COURSE.Course_Title, CREDIT_DATA_COURSE.Credits, CREDIT_DATA_COURSE.Credits_Other, CREDIT_DATA_COURSE.Web_Website_link, CREDIT_DATA_COURSE.Web_Description, CREDIT_DATA_COURSE.Web_Instructor_1, CREDIT_DATA_COURSE.Web_Instructor_2, CREDIT_DATA_COURSE.Web_Location, CREDIT_DATA_COURSE.Web_LocCode, CREDIT_DATA_COURSE.Web_Dates, CREDIT_DATA_COURSE.Web_ETV_Dates_Times, CREDIT_DATA_COURSE.Web_ETN_Dates_Times, CREDIT_DATA_COURSE.Web_Times, CREDIT_DATA_COURSE.Enrollment_Limit, CREDIT_DATA_COURSE.Web_Icon_1, CREDIT_DATA_COURSE.Web_Icon_2, CREDIT_DATA_COURSE.Web_Note, CREDIT_DATA_COURSE.Web_Prereq, CREDIT_DATA_COURSE.Web_Special_Message, CREDIT_DATA_COURSE.Web_Alt_Major_Code, CREDIT_DATA_COURSE.Web_Alt_Course_Number1, CREDIT_DATA_COURSE.Web_Alt_Course_Number2, CREDIT_DATA_COURSE.Web_Alt_Section_Number, CREDIT_DATA_COURSE.Deleted, CREDIT_DATA_COURSE.Update_DateFROM CREDIT_DATA_LOCCODE INNER JOIN CREDIT_DATA_COURSE ON CREDIT_DATA_LOCCODE.Loc_Code = CREDIT_DATA_COURSE.Web_LocCode WHERE (CREDIT_DATA_COURSE.Fiscal = N'078') AND (CREDIT_DATA_COURSE.Term = N'10') AND (CREDIT_DATA_COURSE.Status_Code <> 5) ORDER BY CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Section_Number
But when I run this query with the ORDER BY Clause in place against the SQL 5 tables Sheila dumps into I get this Error:
View 5 Replies
View Related
Apr 9, 2015
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)
View 2 Replies
View Related
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
Nov 4, 2015
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
View 13 Replies
View Related
Jul 23, 2005
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View 3 Replies
View Related
Oct 25, 2007
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.
When on SQL Server 2000 the following statement ran without issue:
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
GROUP BY TrackID
HAVING MAX(LegNum) = 1 AND
TrackID + 'x1' IN
(
SELECT
dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
WHERE Processed = 0 AND LegNum = 1
AND TrackID IN
(
SELECT TrackID
FROM dbo.Track_ID
WHERE TrackID + 'x1' IN
(
SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))
FROM dbo.Track_ID INNER JOIN dbo.transactions
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
GROUP BY TrackID
HAVING MAX(LegNum) = 1
)
View 3 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
May 27, 2008
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.
View 1 Replies
View Related
Jan 14, 2008
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
View 4 Replies
View Related
Apr 30, 2008
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();}
View 6 Replies
View Related
Jun 1, 2004
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
View 4 Replies
View Related
Nov 19, 2001
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'
View 3 Replies
View Related
Apr 23, 2007
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
View 4 Replies
View Related
Jun 16, 2007
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,
View 3 Replies
View Related
Feb 2, 2008
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
View 3 Replies
View Related
Feb 14, 2008
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
View 5 Replies
View Related
Jun 6, 2006
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
View 3 Replies
View Related
Sep 25, 2006
Hi,
Can you just tell me how to use LIKE Operator(Syntax) please.I am new to SSRS2005.
View 8 Replies
View Related
Jun 6, 2006
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
View 5 Replies
View Related
Jun 18, 2007
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
View 6 Replies
View Related
May 31, 2004
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.
View 3 Replies
View Related
Apr 11, 2005
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 Related
Jan 3, 2006
Consider 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!
View 2 Replies
View Related
Aug 22, 2001
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" !
View 1 Replies
View Related
Nov 7, 2000
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???
View 2 Replies
View Related
Jan 31, 2005
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.
View 12 Replies
View Related
Aug 20, 2004
SELECT SkillNo, SkillName FROM [Skill] WHERE SkillName = " & oRs.Fields(lngCol).Value
whats wrong with this
View 2 Replies
View Related
Aug 9, 2006
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 Related
Nov 1, 2007
Hoping 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
View 3 Replies
View Related
Jan 31, 2005
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 :)
View 6 Replies
View Related