August 24, 2018 at 5:30 am
Hi,
I am trying to filter values in my database and am currently looking to provide all claims with a blank description in the Reason field and the claim type is not related to Taxis or Parking. The query i have for this is:
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'
However this is not retrieving the correct values. Can you help me identify where this has gone wrong?
Thanks,
August 24, 2018 at 5:35 am
cjeans - Friday, August 24, 2018 5:30 AMHowever this is not retrieving the correct values. Can you help me identify where this has gone wrong?
What's incorrect about the data you are receiving? Can you provide sample data and expected results, along with the behaviour you're getting? Don't forget, we can't see your data, so we can't run your query to get the results.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 24, 2018 at 7:02 am
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'
I think you need to add some brackets:SELECT * FROM dbo.Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%')
Or one of these:IF OBJECT_ID('tempdb..#Table_1','U') IS NOT NULL DROP TABLE #Table_1
SELECT * INTO #Table_1
FROM (VALUES('abc','my taxi dfg parking sdf'),
('abc','my taxi dfg sdf'),
('abc','my dfg parking sdf'),
('abc','my sdf'),
('','my taxi dfg parking sdf'),
('','my taxi dfg sdf'),
('','my dfg parking sdf'),
('','my sdf')
) T(Reason,SubTypeDesc)
SELECT * FROM #Table_1
SELECT * FROM #Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'
SELECT * FROM #Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%')
SELECT * FROM #Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE '%Parking%'
August 24, 2018 at 7:23 am
Thom A - Friday, August 24, 2018 5:35 AMcjeans - Friday, August 24, 2018 5:30 AMHowever this is not retrieving the correct values. Can you help me identify where this has gone wrong?What's incorrect about the data you are receiving? Can you provide sample data and expected results, along with the behaviour you're getting? Don't forget, we can't see your data, so we can't run your query to get the results.
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE 'Parking%')
With this i still have values in the SubTypeDesc column that is 'Parking - car park'. Everything else is correct.
August 24, 2018 at 7:29 am
cjeans - Friday, August 24, 2018 7:23 AMSELECT * FROM dbo.Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE 'Parking%')
With this i still have values in the SubTypeDesc column that is 'Parking - car park'. Everything else is correct.
That makes sense. "Parking" is not like "Taxi" and "Taxi" is not like "Parking". If would be like having the clause:WHERE i <> 1 OR i <> 2
If i has a value of 1, then it's not 2 (so the statement is true), if i has a value of 2 then it's not 1 (so the statement is true). If i has a value of 3, then it;s not 1 or 2 (so the statement is true).
You need to use AND:SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE 'Parking%'
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 24, 2018 at 7:36 am
cjeans - Friday, August 24, 2018 5:30 AMHi,I am trying to filter values in my database and am currently looking to provide all claims with a blank description in the Reason field and the claim type is not related to Taxis or Parking. The query i have for this is:
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'
However this is not retrieving the correct values. Can you help me identify where this has gone wrong?
Thanks,
You've mistranslated your English description into conditional logic. The proper (direct) translation is SELECT * FROM dbo.Table_1 WHERE Reason = '' AND NOT ( SubTypeDesc LIKE '%Taxi%' OR SubTypeDesc LIKE '%Parking%' )
And when you distribute the NOT, you have to switch AND and OR, so this becomes
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE '%Parking%'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 24, 2018 at 10:11 am
drew.allen - Friday, August 24, 2018 7:36 AMcjeans - Friday, August 24, 2018 5:30 AMHi,I am trying to filter values in my database and am currently looking to provide all claims with a blank description in the Reason field and the claim type is not related to Taxis or Parking. The query i have for this is:
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'
However this is not retrieving the correct values. Can you help me identify where this has gone wrong?
Thanks,
You've mistranslated your English description into conditional logic. The proper (direct) translation is
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND NOT ( SubTypeDesc LIKE '%Taxi%' OR SubTypeDesc LIKE '%Parking%' )And when you distribute the NOT, you have to switch AND and OR, so this becomes
SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE '%Parking%'Drew
I guess they don't teach Boolean logic any more.
August 29, 2018 at 8:27 am
Thanks all. It worked by changing the OR to AND.
Couldn't see the own fault in my logic -_-
August 29, 2018 at 9:06 am
cjeans - Wednesday, August 29, 2018 8:27 AMThanks all. It worked by changing the OR to AND.Couldn't see the own fault in my logic -_-
Unless you have specifically been taught Boolean logic, it can be tough to understand at first. Once you learn it though, not to hard.
I had the benefit of a microcircuits course in college many years ago where we needed to understand Boolean logic while working with AND, NAND, OR, NOR, XOR gates and such.
August 29, 2018 at 9:17 am
Lynn Pettis - Wednesday, August 29, 2018 9:06 AMUnless you have specifically been taught Boolean logic, it can be tough to understand at first. Once you learn it though, not to hard.
I had the benefit of a microcircuits course in college many years ago where we needed to understand Boolean logic while working with AND, NAND, OR, NOR, XOR gates and such.
You have just reminded of my GCSE Electronics course, Lynn... I never wanted to be reminded of that. I swear, all we ever did (every lesson) was tests on boolean logic and resistor bands. Every school day for 2 years... Oh, and threw magnets onto the metal ceiling. :hehe: (that part was fun at least).
I suppose, at least it helped me with my boolean logic in computer languages (I can't remember my resistor band values at all though). 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 29, 2018 at 9:38 am
Thom A - Wednesday, August 29, 2018 9:17 AMLynn Pettis - Wednesday, August 29, 2018 9:06 AMUnless you have specifically been taught Boolean logic, it can be tough to understand at first. Once you learn it though, not to hard.
I had the benefit of a microcircuits course in college many years ago where we needed to understand Boolean logic while working with AND, NAND, OR, NOR, XOR gates and such.You have just reminded of my GCSE Electronics course, Lynn... I never wanted to be reminded of that. I swear, all we ever did (every lesson) was tests on boolean logic and resistor bands. Every school day for 2 years... Oh, and threw magnets onto the metal ceiling. :hehe: (that part was fun at least).
I suppose, at least it helped me with my boolean logic in computer languages (I can't remember my resistor band values at all though). 🙂
I have found that learning Boolean logic has been vital to what I do. It is amazing how many people not exposed to Boolean logic don't understand why some queries that look like they should work, don't.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply