Solved

T-SQL Simple Not Equal Question

Posted on 2008-10-21
4
356 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now