February 20, 2006 at 1:13 am
i have a SQL query(below) which does not work.
SQL query
==========
select channelName
from SiteBehaviour
where features='0,0,0,1,0,0,0,0,1,0,
0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1'
this query does not work in my sql server 2000.
but i have the column whose field value is '0,0,0,1,0,0,0,0,1,0,
0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1'
why this does not work !!!
what i have to do ?
February 20, 2006 at 1:54 am
is there anybody here ?
February 20, 2006 at 1:55 am
i am confused , why this does not work.
is it because of there is "," in the values ?
or what is the problem ?
February 20, 2006 at 2:21 am
Hi,
Write a query
where feature = 0 and 1 instead of typing all 0's and 1's and this will work .
with regards,
Eshwar
February 20, 2006 at 2:24 am
hi,
probabily, this is not the mistake.
my mistake is somewhere else.
i have used 3 "AND" operator with this SQL query.
How many "AND" operator could be used for a SQL query ?
February 20, 2006 at 3:54 am
This works perfectly well for me.
Could it be that the query you're looking for is actually:
select channelName
from SiteBehaviour
where features = '0,0,0,1,0,0,0,0,1,0,0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1'
and NOT
select channelName
from SiteBehaviour
where features='0,0,0,1,0,0,0,0,1,0,
0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1'
If you put a carriage return in your query editor then SQL Server is going to search for whatever you've typed in (literally).
February 20, 2006 at 3:57 am
create table Test (Col1 varchar(500))
insert into Test values('0,0,0,1,0,0,0,0,1,0,0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1')
select * from Test where Col1 = '0,0,0,1,0,0,0,0,1,0,0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1'
It works for me.. is there anything else which you are having in where clause?
February 21, 2006 at 7:35 am
Karl previously identified what is most likely your problem:
I ran the following code, which produced the output listed at the end of this post:
--DROP TABLE SiteBehaviour
--GO
CREATE TABLE SiteBehaviour
(
sbid int IDENTITY(1,1)
, channelName varchar(20)
, features varchar(100)
)
GO
SET NOCOUNT ON
INSERT SiteBehaviour (channelName, features) VALUES ('Channel 1', '0,0,0,1,0,0,0,0,1,0,
0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1')
INSERT SiteBehaviour (channelName, features) VALUES ('Channel 2', '0,0,0,1,0,0,0,0,1,0,0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1')
SET NOCOUNT OFF
SELECT channelName
FROM SiteBehaviour
WHERE features='0,0,0,1,0,0,0,0,1,0,
0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1'
SELECT channelName
FROM SiteBehaviour
WHERE features='0,0,0,1,0,0,0,0,1,0,0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1'
--
-- RESULTS -----------------------------------------------------------
--
channelName
--------------------
Channel 1
(1 row(s) affected)
channelName
--------------------
Channel 2
(1 row(s) affected)
February 21, 2006 at 10:12 am
You mention 3 AND operators, are they in this query as well. i.e. There is more to the WHERE clause than first posted? Keep in mind that anytime you start throwing in AND's to your where clause every item must evaluate as TRUE for a response to be returned. e.g.
SELECT channelName FROM SiteBehaviour WHERE features='0,0,0,1,0,0,0,0,1,0,0,1,1,1,0,0,0,1,0,1,1,1,1,1,1,1,1' AND name = 'MyName' AND something = 'MyValue'
This will only return a result if the [features] column is equal to that mess AND the [name] column is equal to 'MyName' AND [something] is equal to 'MyValue'. For any given record if any one of the three statements is false the whole clause becomes false and that record will not be returned. If you have more than one statement in your where clause, do a truth table on it to figure out if it is really doing what you want it to do.
As far as I recall you can pretty much string AND's and OR's in your where clause until you are blue in the face. There probably is some limit somewhere (e.g. your server runs out of memory) but I suspect that it is unlimited for all practical purposes.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply