Query Help

  • Thread starter Thread starter gaetan.metra
  • Start date Start date
G

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
 
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
Click to expand...

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
 
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
Click to expand...

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
Code: 
 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
 
John said:
I see Bob has a SQL/Server syntax solution;
Click to expand...

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
 
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.
Click to expand...
Order 123 contains 3 items
Order #     Product   Cannot Ship    Code
123 Product A         Y
123 Product B                                999
123 Product C         N                      OKS
Click to expand...
Order
245 Product B                                457
245 Product C         N                      OKS
Click to expand...
Order
356 Product C        N                      OKS
356 Product D        N
Click to expand...

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
Code: 
 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!!!
Click to expand...
 
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
Click to expand...

No probs. thanks a lot for the help
 
Back
Top