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

Using LIKE Operator In An O BY Clause On SQL Server 2K5

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

T-SQL (SS2K8) :: How To Ignore Between Operator End Value If It Is 0 In Where Clause

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

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Nvarchar

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

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Datetime.

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

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

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

GROUP By Clause Or DISTINCT Clause

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

Filtering Results In The Where Clause Vs A Having Clause

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

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

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

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

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

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

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

SQL LIKE Operator And %

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

SQL AND/OR Operator

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

LIKE Operator ..???

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

IN Operator

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

Using Like % Operator

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

LIKE AND OR OPERATOR

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

Like Operator

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

Like Operator

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

EXCEPT Operator

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

Like Operator

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

Between Operator

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

Dynamic Operator Help

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

Limiting And Using The TOP Operator

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

Sql Operator(OR) Probelm

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

Help Me Please, About Operator Tree

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

&#34;Intersect&#34; Operator ...

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

Like Operator - Alternatives?

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

Like Operator Usage

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

Missing Operator

Aug 20, 2004

SELECT SkillNo, SkillName FROM [Skill] WHERE SkillName = " & oRs.Fields(lngCol).Value

whats wrong with this

View 2 Replies View Related

MSSQL Operator

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

Odd Issue With LIKE Operator

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

Substitute Of 'IN' Operator

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







Copyrights 2005-15 www.BigResource.com, All rights reserved