Query filter - 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 filter

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-24 : 16:26:35

I am working with the following statement in a SP. Currently, the last two lines apply two different filters. I want these filters to be applied only if they are both true. How can I do this?

SELECT DISTINCT LEFT(MFGPRO.dbo.psANA.Parent, 5) AS par5ana, MFGPRO.dbo.[ANA No Dups].DESC1
into #temp
FROM MFGPRO.dbo.psANA LEFT OUTER JOIN
MFGPRO.dbo.[ANA No Dups] ON MFGPRO.dbo.psANA.Parent = MFGPRO.dbo.[ANA No Dups].ITEM
WHERE (MFGPRO.dbo.psANA.Component LIKE @src + '%')
aND (NOT (MFGPRO.dbo.[ANA No Dups].DESC1 like '%PH%'))
AND (NOT (MFGPRO.dbo.[ANA No Dups].item like '%100__%'))

Thanks!
Lane

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-24 : 18:21:28

I think this is a simple bracketing problem.

SELECT DISTINCT LEFT(MFGPRO.dbo.psANA.Parent, 5) AS par5ana, MFGPRO.dbo.[ANA No Dups].DESC1
into #temp
FROM MFGPRO.dbo.psANA LEFT OUTER JOIN
MFGPRO.dbo.[ANA No Dups] ON MFGPRO.dbo.psANA.Parent = MFGPRO.dbo.[ANA No Dups].ITEM
WHERE (MFGPRO.dbo.psANA.Component LIKE @src + '%')
AND ((NOT (MFGPRO.dbo.[ANA No Dups].DESC1 like '%PH%'))
AND (NOT (MFGPRO.dbo.[ANA No Dups].item like '%100__%')))

Let me know if this doesn't work.

Jeremy

lane0618
Posting Yak Master

134 Posts

Posted - 2002-05-24 : 18:54:38

Thanks, but it doesn't seem to be working.

Here is the query I'm using to test:

select item, desc1
from MFGPRO.dbo.[ANA No Dups]
where item like '01402-001' and (item not like '100__-___'
and not desc1 like '%ph%')

Item 01402-001 does have "ph" in it DESC1 field. When I run the query I get no results, but it should return 01402-001 since its not "like '100__-___'".

Thanks,
Lane

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-24 : 20:56:07

Lane,

Can you post some sample data, expected results and table stucture? If you can do this, I will try to come up with a solution.

Two more questions:
1) Why are you using like operators instead of = operator?
2) Are you trying to get items the are equal to '01402-001'
and both item not equal to '100__-___' and desc1 not like %ph%?

I am almost thinking you are wanting two differen data sets from this query. 1) all items equal to '01402-001' and 2) all items not equal to '100__-___' and desc1 not like %ph%.

If this is the case, you can use add OR instead of AND liek follows:

select item, desc1
from MFGPRO.dbo.[ANA No Dups]
where item like '01402-001'
or (item not like '100__-___'
and desc1 not like '%ph%')

Jeremy

Jeremy

Edited by - joldham on 05/24/2002 21:03:47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources