Query Help

[gaetan.metra]

Suppose I have a set of orders I want to filter
I want to remove any order for which a product says Y. Then if blank
or N ok to ship. But if code is listed as 3 digits numbers it cannot
ship.
So using the example below only order 356 should be shipped. Any help
is appreciated.

Order 123 contains 3 items
Order # Product Cannot Ship Code
123 Product A Y
123 Product B 999
123 Product C N OKS


Order
245 Product B 457
245 Product C N OKS

Order
356 Product C N OKS
356 Product D N

 

[Bob Barrows]

Suppose I have a set of orders I want to filter
I want to remove any order for which a product says Y. Then if blank
or N ok to ship. But if code is listed as 3 digits numbers it cannot
ship.
So using the example below only order 356 should be shipped. Any help
is appreciated.

Order 123 contains 3 items
Order # Product Cannot Ship Code
123 Product A Y
123 Product B 999
123 Product C N OKS


Order
245 Product B 457
245 Product C N OKS

Order
356 Product C N OKS
356 Product D N

Assuming SQL 2005 or higher:
WITH q AS (
SELECT Order,
MAX([Cannot Ship]) AS NoShip
,MAX(CASE WHEN Code LIKE '[0-9][0-9][0-9]' THEN 1 ELSE 0 END) NoShip2
FROM Orders)
SELECT Order FROM q
WHERE NoShip<>'Y' AND NoShip2=0

 

[John W. Vinson]

Suppose I have a set of orders I want to filter
I want to remove any order for which a product says Y. Then if blank
or N ok to ship. But if code is listed as 3 digits numbers it cannot
ship.
So using the example below only order 356 should be shipped. Any help
is appreciated.

Order 123 contains 3 items
Order # Product Cannot Ship Code
123 Product A Y
123 Product B 999
123 Product C N OKS


Order
245 Product B 457
245 Product C N OKS

Order
356 Product C N OKS
356 Product D N

I see Bob has a SQL/Server syntax solution; if you're using vanilla Access,
the syntax may be rather different. Assuming that you want to exclude an order
if ANY item in it contains a Y or a three-digit number, you'll need a couple
of EXISTS clauses:

SELECT <whatever you want to see>
FROM Orders
WHERE NOT EXISTS(SELECT [Order #] FROM Orders WHERE [Cannot Ship] = "Y")
AND NOT EXISTS(SELECT [Order #] FROM Orders] WHERE

 LIKE "###");

NOT EXISTS clauses can be very slow; an alternative would be a NOT IN query:

SELECT <whatever> FROM Orders
WHERE [Order #] NOT IN (SELECT X.[Order #] FROM Orders AS X WHERE [Code] LIKE
"###" OR [Cannot Ship] = "Y");

These can be poky too, you might want to try both to see which works better.

Of course you may have other criteria you don't mention, they'd need to be
included.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Bob Barrows]

I see Bob has a SQL/Server syntax solution;

Oops! My excuse is that there was a very similar question I had just
finished answering on a SQL Server list a few minutes prior to this answer
and my head was still in that mindset.
Sorry gaetan

 

[gaetan.metra]

Suppose I have a set of orders I want to filter
I want to remove any order for which a product says Y. Then if blank
or N ok to ship. But if code is listed as 3 digits numbers it cannot
ship.
So using the example below only order 356 should be shipped. Any help
is appreciated.

Order 123 contains 3 items
Order #     Product   Cannot Ship    Code
123 Product A         Y
123 Product B                                999
123 Product C         N                      OKS

Order
245 Product B                                457
245 Product C         N                      OKS

Order
356 Product C        N                      OKS
356 Product D        N

I see Bob has a SQL/Server syntax solution; if you're using vanilla Access,
the syntax may be rather different. Assuming that you want to exclude an order
if ANY item in it contains a Y or a three-digit number, you'll need a couple
of EXISTS clauses:

SELECT <whatever you want to see>
FROM Orders
WHERE NOT EXISTS(SELECT [Order #] FROM Orders WHERE [Cannot Ship] = "Y")
AND NOT EXISTS(SELECT [Order #] FROM Orders] WHERE

 LIKE "###");

NOT EXISTS clauses can be very slow; an alternative would be a NOT IN query:

SELECT <whatever> FROM Orders
WHERE [Order #] NOT IN (SELECT X.[Order #] FROM Orders AS X WHERE [Code] LIKE
"###" OR [Cannot Ship] = "Y");

These can be poky too, you might want to try both to see which works better.

Of course you may have other criteria you don't mention, they'd need to be
included.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -[/QUOTE]

Thanks a lot!!!

 

[gaetan.metra]

Oops! My excuse is that there was a very similar question I had just
finished answering on a SQL Server list a few minutes prior to this answer
and my head was still in that mindset.
Sorry gaetan

No probs. thanks a lot for the help