Exclusion Syntax Help

Question

Post reply

Exclusion Syntax Help

gatorfe

SSC-Addicted

Points: 416
More actions
March 18, 2014 at 11:11 am

#286826

We want to not show line 1,3,5 for a specific order number #23565. We still want to see those order line numbers for all other sales orders.
This is the syntax we have:
select
soi.slord as ORDER_NO,
soi.slline as ORDER_LINE_NO
from
erp.svkincd
where
(slord <> 249486 and slline not in (1,3,5))
When we run it it does not show us any line 1,3,5 at all. Is there a way to get around that? Thank you in advance for any suggestions!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

dbalmf SSCrazy Points: 2063 More actions · Answer 1

Someone will be along with a neater way to do this, but this would work

SELECT soi.slord as ORDER_NO,

soi.slline as ORDER_LINE_NO

FROM erp.svkincd

WHERE slord = '23565'

AND slline NOT IN (1,3,5)

UNION ALL

SELECT soi.slord as ORDER_NO,

soi.slline as ORDER_LINE_NO

FROM erp.svkincd

WHERE slord != '23565'

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I added some sample data to test the solution, but you should provide it for us on future occasions to get faster and better help.

WITH svkincd (slord, slline) AS(

SELECT 249486, 1 UNION ALL

SELECT 249486, 2 UNION ALL

SELECT 249486, 3 UNION ALL

SELECT 249486, 4 UNION ALL

SELECT 249487, 1 UNION ALL

SELECT 249487, 2 UNION ALL

SELECT 249487, 3 UNION ALL

SELECT 249487, 4

)

SELECT *

FROM svkincd

WHERE NOT(slord = 249486 and slline in (1,3,5))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

gatorfe SSC-Addicted Points: 416 More actions · Answer 3

gatorfe

SSC-Addicted

Points: 416

March 18, 2014 at 11:56 am

#1698148

Thak you for the info!

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

I think you were very close:

where

(slord <> 249486 or slline not in (1,3,5))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 5

Try this.

select

soi.slord as ORDER_NO,

soi.slline as ORDER_LINE_NO

from

erp.svkincd

where

slord <> 23565

OR (slord = 23565

and slline not in (1,3,5))