Why does <> in my query not work?

Analytics
Enthusiast

Why does <> in my query not work?

Hello,

 

Could someone please help me understand why my WHERE statement is not working? My query below works if I use a case statement,

 but I don't understand WHY the parenthesis does not combine to one logical operation and is instead seen as two seperate ones.

 

- Why does not (Year_num <> 2005 AND Month_num<> 8) not get seen as one combination ?

 

Many thanks in advance for your advice,

Christine

 

SELECT CASE WHEN (s.msa_high >= 50 AND s.msa_high <= 60)  THEN 'low'
            WHEN (s.msa_high > 60 AND s.msa_high <= 70)  THEN 'medium' 
            WHEN  s.msa_high >70 THEN 'high'
       END AS EduCat, SUM(CleanedData.SalesDays) AS TotSalesDays, SUM(CleanedData.Revenue) AS TotRev 
FROM (SELECT EXTRACT(YEAR from saledate) AS Year_num, EXTRACT(MONTH from saledate) AS Month_num, store AS Store, COUNT(DISTINCT saledate) AS SalesDays,
             SUM(AMT) AS Revenue, Revenue/SalesDays AS Avg_Daily_Revenue
FROM trnsact
WHERE stype = 'P' AND (Year_num <> 2005 AND Month_num<> 8)
GROUP BY Year_num, Month_num, Store 
HAVING SalesDays >= 20 ) AS CleanedData
INNER JOIN store_msa s ON s.store = CleanedData.store
GROUP BY EduCat;

Accepted Solutions
Teradata Employee

Re: Why does <> in my query not work?

Christine,

 

This is happenigns due to De Morgan's law of boolean logic / set theory (https://en.wikipedia.org/wiki/De_Morgan%27s_laws). Quickly, when you "distribute" a NOT your ANDs switch to ORs.

 

Your case statement is saying what you want: I want all rows that are not in August of 2005

 

With logical opperators we can write: WHERE NOT (year_num = 2005 AND month_num = 8)

 

To distribute the NOT with logical opperators we end up with: WHERE (year_num <> 2005 OR month_num <> 8) 

 

 

This is confusing to us becase we are looking for rows that are not something and also not something else so we want to use an AND, but check out the Ven Diagrams in the De Morgan's Law link and see if it makes sence why we want OR instead.

 

Good luck!

Michelle

1 ACCEPTED SOLUTION
10 REPLIES
Teradata Employee

Re: Why does <> in my query not work?

Please explain what "doesn't work" means. And please provide the case expression used instead that gets the desired result.

Enthusiast

Re: Why does <> in my query not work?

Hi Todd,

 

Thank you. I mean that it is not giving me the desired result which is the one below.

SELECT CASE WHEN (s.msa_high > 50 AND s.msa_high <= 60)  THEN 'low'
            WHEN (s.msa_high > 60 AND s.msa_high <= 70)  THEN 'medium' 
            WHEN  s.msa_high >70 THEN 'high'
       END AS EduCat, SUM(CleanedData.SalesDays) AS TotSalesDays, SUM(CleanedData.Revenue) AS TotRev 
FROM (SELECT EXTRACT(YEAR from saledate) AS Year_num, EXTRACT(MONTH from saledate) AS Month_num, store AS Store, COUNT(DISTINCT saledate) AS SalesDays,
             SUM(AMT) AS Revenue, Revenue/SalesDays AS Avg_Daily_Revenue, (CASE 
WHEN Year_num= '2005' AND Month_num= '8' THEN 'exclude' ELSE 'include' END) AS Filter FROM trnsact WHERE stype='p' AND Filter='include' GROUP BY store, Year_num, Month_num HAVING SalesDays >= 20 )AS CleanedData
INNER JOIN store_msa s ON s.store = CleanedData.store
GROUP BY EduCat;
Teradata Employee

Re: Why does <> in my query not work?

What are the data types and data representation in those types? In the case expression character compares are being done. In the <> expressions numeric compares are being done.

Enthusiast

Re: Why does <> in my query not work?

Hello,

 

I want to exclude the year 2005 and month of August from sales. Stypye =p( purchase.)

Please see a screenshot of the results obtained.

 

Without case clause and <> (wrong result)

Screen Shot 2017-04-16 at 17.51.00.png

With case clause (right result)

Screen Shot 2017-04-16 at 17.45.46.png

Thanks Todd

Enthusiast

Re: Why does <> in my query not work?

Pardon *stype = purchase

Teradata Employee

Re: Why does <> in my query not work?

Perhaps I was not clear enough with my question:

 

WHEN Year_num= '2005' AND Month_num= '8'  is performing character string comparisons due to the numbers being placed inside quotes.

AND (Year_num <> 2005 AND Month_num<> 8) is performing numeric comparisons - no quotes.

Implicit data type casting has to be done when they types do not match.

 

What data types are Year_num and Month_num? How is the data stored in those columns?

 

 

Enthusiast

Re: Why does <> in my query not work?

Ahh I am sorry, I am new to SQL so I did not correctly understand. Please bear with me, its this what your looking for?

 

Screen Shot 2017-04-16 at 18.17.49.pngScreen Shot 2017-04-16 at 18.18.02.png

Enthusiast

Re: Why does <> in my query not work?

Hello Todd,

 

Could you please kindly clarify what you need from me? I want to learn why my query is not giving me the right result.

Teradata Employee

Re: Why does <> in my query not work?

Christine,

 

This is happenigns due to De Morgan's law of boolean logic / set theory (https://en.wikipedia.org/wiki/De_Morgan%27s_laws). Quickly, when you "distribute" a NOT your ANDs switch to ORs.

 

Your case statement is saying what you want: I want all rows that are not in August of 2005

 

With logical opperators we can write: WHERE NOT (year_num = 2005 AND month_num = 8)

 

To distribute the NOT with logical opperators we end up with: WHERE (year_num <> 2005 OR month_num <> 8) 

 

 

This is confusing to us becase we are looking for rows that are not something and also not something else so we want to use an AND, but check out the Ven Diagrams in the De Morgan's Law link and see if it makes sence why we want OR instead.

 

Good luck!

Michelle