Solved

T-SQL Simple Not Equal Question

Posted on 2008-10-21
4
358 Views
Last Modified: 2010-05-18
Problem Column: dc.itemclass tinyint (not null)
Problem: I want to display a result set that excludes itemclasses assigned the numbers 1, 9, and 20. I am not able to exclude them with my current code and need some help with the correct syntax.

Ways I have unsuccessfully attempted:
example 1
and di.itemclass not like '1'
and di.itemclass not like '9'
and di.itemclass not like '20'
example 2
and not di.itemclass = (1)
and not di.itemclass = (9)
and not di.itemclass = (20)
example 3
and di.itemclass <> (1)
and di.itemclass <> (9)
and di.itemclass <> (20)



Current Code Below:
select
dc.customernk,
dc.customername,      
di.itemclass,
di.itemclassdesc,
      di.ItemSubClass,
di.itemsubclassdesc,
      di.itemnk,
      di.itemdesc,
      sum(f.quantitysold)as 'Net Qty Sold',
Sum((ExtendedPrice - ExtendedPromotions) + (MarketingAllowances + Deposits +
SalesTax + FreightServiceFees + TelxonFees + Othercharges))as  'Net Sales',
case when argroup in (' ') then ('35145-8') else argroup end
FROM
      DimItem as di
JOIN      FactCustSales as f
ON       di.ItemWK = f.ItemWK
JOIN      DimCustomer as dc
ON      f.CustomerWK = dc.CustomerWK
JOIN      DimCalendar as dcal
ON      f.SalesDateWK = dcal.datewk
WHERE       dc.argroup in ('H04', 'H05')
or customernk = ('351458')
AND      dcal.DateNK >= 'Jan 1 2008'
AND di.itemclass not in (1,9,20)
AND      di.Chargetype = 'ITEM'
and f.quantitysold > (0)
and f.creditinvoiceflag <> 'RB'
GROUP BY

dc.customernk,
dc.customername,
argroup,
      di.ItemClass,
      di.ItemClassDesc,
      di.ItemSubClass,
      di.ItemSubClassDesc,
      di.Itemnk,
      di.ItemDesc
ORDER BY
dc.customernk,
dc.customername,      
argroup,
      di.ItemClass asc,
      di.ItemSubClass asc,
      sum(f.quantitysold) desc
0
Comment
Question by:SasDev
  • 2
4 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 22769521
Your WHERE clause is definitely a problem.  Let's examine the logic.  

The basic problem, I believe, is that you have effectively two WHERE clauses - one before the OR and one after.  To solve this, you will need to include the OR'd items within a set of parentheses, like this:

WHERE
    (
        dc.argroup in ('H04', 'H05')
        or customernk = ('351458')
    )
    AND      dcal.DateNK >= 'Jan 1 2008'
    AND di.itemclass not in (1,9,20)
    AND      di.Chargetype = 'ITEM'
    and f.quantitysold > (0)
    and f.creditinvoiceflag <> 'RB'
0
 
LVL 1

Author Closing Comment

by:SasDev
ID: 31508417
How right you are! Thank you very much for helping me examine my logic.
Take Care,

Traci V.
0
 
LVL 3

Expert Comment

by:ScottyNewSuits
ID: 22769591
and di.itemclass NOT IN (1,9,20) should work...

you can try
and (di.itemclass <> 1 AND di.itemclass <> 9 AND di.itemclass <> 20).

Please note that OR syntax ignore indexes.
0
 
LVL 1

Author Comment

by:SasDev
ID: 22770518
Thank you ScottyNewSuits. I already awarded the points before your suggestion came in. Thanks anyhow!

Traci
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question