query problem NOT LIKE.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query problem NOT LIKE....

Author

Topic

petem
Starting Member

44 Posts

Posted - 2005-01-10 : 11:31:31

Hi. I have a slight problem with what I think is a pretty simple query.

basically my statement includes

AND (fr.type NOT LIKE '%trade%' or fr.type is null)

yet it is still bringing back records with a Type of 'trade'

Does anyone know what may be happening?

Thanks,

Pete

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-10 : 11:36:01

Try it just with that table and include the pk in the query of the offending row. That will give you something to play with.
select *
from tbl fr
where fr.pk = 'whatever'
AND (fr.type NOT LIKE '%trade%' or fr.type is null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-10 : 11:43:24

We'd need to see the entire predicate


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(type varchar(8000))
GO

INSERT INTO myTable99(type)
SELECT 'My trade is x' UNION ALL
SELECT 'My tirade is y' UNION ALL
SELECT 'My etrade is z'
 

SELECT * FROM myTable99 
 WHERE type NOT LIKE '%trade%' or type is null
GO

SET NOCOUNT ON
DROP TABLE myTable99
GO

Brett

8-)

petem
Starting Member

44 Posts

Posted - 2005-01-10 : 11:47:19

sorry, what do you mean by pk?

if I do:

select *
from tbl fr
where fr.model = 'e300'   -for Example
AND (fr.type NOT LIKE '%trade%' or fr.type is null)

as a statement in Query Analyzer it works.

But it wont work in the Stored Proc...

petem
Starting Member

44 Posts

Posted - 2005-01-10 : 11:57:57

OK, Full code is

SELECT DISTINCT model FROM CModel cm, Usedfile fr 
WHERE companyID = 8 AND
	cm.type = @cmType
AND
fr.FullMakeModelName1 LIKE '%' + Model + '%'
AND
Price1 >= @price_lower AND 
Price1 <= @price_higher AND 
    SpeedoReading1 < @speedo_higher AND 
    SpeedoReading1 >= @speedo_lower AND 
    datediff(m,  registereddate1, getdate()) >= @agemonth_lower AND
    datediff(m,  registereddate1, getdate()) < @agemonth_higher AND
    (make1 LIKE @make+'%' OR
    fullmakemodelname1 LIKE @make+'%') 
    AND companyid=8 
	AND FullMakeModelName1 like  @make AND
	Location1 like '%%'  and
	Fueltype1 LIKE '%' and
     ExteriorColour1 like '%%' and
    (AvailableFrom1 IS NULL OR AvailableFrom1 < GetDate()) 
	AND EngineSize1 > 400
	AND RegisteredDate1 IS NOT NULL
	AND (fr.type NOT LIKE '%trade%' or fr.type is null)

Just realised I can get rid of location, fuelType and Colour - but left them in in case they're causing the problem...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-10 : 14:11:20

You mean you have a model with a type of trade being returned?

I suspect it's in the join of CModel to Usedfile which may be the problem

fr.FullMakeModelName1 LIKE '%' + Model + '%'

Does your Model apear as a substring in a model which is being returned?
Or same model for two different makes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-10 : 15:02:06

How did you happen to choose that as the culprit?

And where's the join?


SELECT DISTINCT model 
  FROM CModel cm, Usedfile fr 
 WHERE companyID = 8 
   AND cm.type = @cmType
   AND fr.FullMakeModelName1 LIKE '%' + Model + '%'
   AND Price1 >= @price_lower 
   AND Price1 <= @price_higher 
   AND SpeedoReading1 < @speedo_higher 
   AND SpeedoReading1 >= @speedo_lower 
   AND datediff(m,  registereddate1, getdate()) >= @agemonth_lower 
   AND datediff(m,  registereddate1, getdate()) < @agemonth_higher 
   AND (	make1 LIKE @make+'%' OR	fullmakemodelname1 LIKE @make+'%') 
   AND companyid=8 
   AND FullMakeModelName1 like  @make 
   AND Location1 like '%%'  
   AND Fueltype1 LIKE '%' 
   AND ExteriorColour1 like '%%' 
   AND (	AvailableFrom1 IS NULL OR AvailableFrom1 < GetDate()) 
   AND EngineSize1 > 400
   AND RegisteredDate1 IS NOT NULL
   AND (fr.type NOT LIKE '%trade%' OR fr.type is null)

Not fo nothing but even if you do get it to work...it's very ineefecient

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-10 : 16:37:19

I think it's
fr.FullMakeModelName1 LIKE '%' + Model + '%'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

petem
Starting Member

44 Posts

Posted - 2005-01-11 : 15:54:03

hmmm - went in today - rewrote it - and it works!!!

oh well!

I spose

X, you were right abou the Join aswell - completely forgot!!! doh - thats what happens when you rush everything!

So, how could I make this more efficient?

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-11 : 16:50:51

Well anything that is

LIKE '%anything'

With a leading wildcard will cause a scan.

That's the first problem...

And what's with


   AND Location1 like '%%'  
   AND Fueltype1 LIKE '%' 
   AND ExteriorColour1 like '%%'

That's completely worthless...actually it's worse than that (or used to be unless M$ figured out to not look at it)

And not that it matters, but you should use ANSI JOIN syntax...it would have pointed out to you that you forgot the JOIN Predicates...

And the formatting thing....it's a lot easier to "see" what's going on if it's nice and clean...

Sooooo

How did you rewrite it?

Brett

8-)

petem
Starting Member

44 Posts

Posted - 2005-01-13 : 05:53:06

X,

Definitely see what you mean with the worthless code! It's a lot better now thanks :)

the problem was something to do with the join and location details.

Ive learnt alot over the past couple of weeks - This is really the first time that I've had to work with terrible, terrible (that is to say, really terrible) real world data.

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources