Help with some SQL

[swisstoni]

Hey guys,

I keep getting a "A.order_date" unknown error with the following query:

SELECT tblOrder_Item.orderitem_id, tblOrder_Item.item_id, tblOrder_Item.order_id, qryAllocatedStock.allocated_stock, tblOrder_Item.item_qty, tblItem.item_shelfStock, tblOrder_Item.item_name, tblItem.item_partno, tblOrder_Item.item_price, A.order_date
FROM 
tblOrder A, 
((tblOrder_Item INNER JOIN tblItem ON tblOrder_Item.item_id=tblItem.item_id) INNER JOIN (
SELECT SUM(tblOrder_Item.item_qty) AS allocated_stock, tblOrder_Item.item_id FROM tblOrder_Item, tblOrder WHERE (((tblOrder.order_id)=tblOrder_Item.order_id) And ((tblOrder.order_status)<>'dispatched')) Or (((tblOrder.order_status)<>'processing')) Or (((tblOrder.order_status)<>'printed')) And (tblOrder.order_date < A.order_date) GROUP BY tblOrder_Item.item_id)
 
 AS qryAllocatedStock ON tblItem.item_id=qryAllocatedStock.item_id)
WHERE A.order_id = tblOrder_Item.order_id;

I'll try as best I can to describe what I believe's going on here!

I've got tblOrder - with order info

tblOrder_Item - with item_id, order_id, aswell as item_name and quantity

tblItem - with item information



This query is used to pull information mainly from the tblOrder_Item table, but it also pulls the part number from tblItem.

The subquery is used to display how many of this item are already allocated to orders - so it sums item_qty.

But I want to change it so that instead of reading "allocated_stock", it will be like it's selecting "pre-allocated_stock". By that, I mean that stock should be allocated to orders on a first come first served basis. So that if an order contains an item which we only have 1 of in stock, the next order that comes in will see that one has been pre allocated. But the first order would show zero as pre-allocated because it's the first order of this type.

My SQL knowledge said to use the tblOrder.order_date < A.order_date, but that seems to be throwing this error!

Any assistance, as always, greatly appreciated.

 

[ajetrumpet]

Maybe the query is too complex? Although I would think that the error message would tell you this if it really was.

What does the error message say? "Unknown Error?"

 

[swisstoni]

It's not that.

Basically, I'm not sure if I can actually do the following

SELECT ...... FROM tblA A INNER JOIN (ANOTHER SELECT QUERY.... WHERE A.row = newTbl.row)


Because the error says a.row is not found....

Hope that clears it up a bit.

 

[jal]

It's not that.

Basically, I'm not sure if I can actually do the following

SELECT ...... FROM tblA A INNER JOIN (ANOTHER SELECT QUERY.... WHERE A.row = newTbl.row)


Because the error says a.row is not found....

Hope that clears it up a bit.

Your query is confusing, because there are two ways to do a join and your query almost seems to be doing both in the same query (which is not standard for most users).

The first way is to name both tables in the FROM clause
SELECT A.*, B.* FROM tblA as A, tblB as B
The second way is to use the JOIN keyword. When you use this keyword, use the following rule in sql view. End every JOIN line with a closing parentheses, and put all the opening parantheses after the FROM keyword, like this (here we join 10 tables).


SELECT t1.* FROM (((((((((t1
INNER JOIN t2 on t1.ID = t2.ID)
INNER JOIN t3 on t3.id = t2.ID)
INNER JOIN t4 on t4.id = t3.id)
INNER JOIN t5 on t5.id = t4.id)
INNER JOIN t6 on t6.id = t5.id)
INNER JOIN t7 on t7.id = t6.id)
INNER JOIN t8 on t8.id = t7.id)
INNER JOIN t9 on t9.id = t8.id)
INNER JOIN t10 on t10.id = t9.id)

Personally I prefer the INNER JOIN syntax. I find it very readable. Unfortunately, if you use the query grid or wizard, it reorganizes the INNER JOIN clauses in a very unreadable way (which is one reason I only use sql view).

AnyWHO, try rewriting it using one of these two methods instead of doing both methods at once. This will make it more readable, and then the rest of us might find we are able to help you once we can read it.

 

[swisstoni]

Thanks for the info.

The simple version of what I need is as follows:

SELECT Sum(A.item_qty) AS pre_allocated_stock, A.item_id
FROM tblOrder_Item AS A INNER JOIN tblOrder AS B ON A.order_id = B.order_id
WHERE (((A.item_id)=[itemid]) AND ((B.order_date)<[orderdate]))
GROUP BY A.item_id;

But I would have to call that from each record, filling in values for [orderdate] and [itemid]

I hope that's a bit clearer - I want to find out how many items have been ordered before this order.


But now I don't know how to integrate the above query with:

SELECT * FROM tblOrder_Item

Filling in the appropriate values of orderdate and item_id as it goes through each record...

That's what I was trying to do with the two joins earlier.

 

[swisstoni]

Okay, so I think I've simplified it a bit here:

SELECT A.orderitem_id, A.item_id, A.order_id, A.item_qty, tblOrder.order_date, Q.pre_allocated_stock
 
FROM 
 
(
(tblOrder_Item AS A INNER JOIN tblOrder AS B ON A.order_id = B.order_id)
 
INNER JOIN 
 
(SELECT Sum(X.item_qty) AS pre_allocated_stock, X.orderitem_id
FROM tblOrder_Item AS X INNER JOIN tblOrder AS Y ON X.order_id = Y.order_id
WHERE X.item_id=A.item_id AND Y.order_date< B.order_id
GROUP BY X.orderitem_id)
 
AS Q ON Q.orderitem_id = A.orderitem_id);

But I'm back to square 1 in that I keep getting the "Unknown column A.item_id" error. I don't know why it can't reference A.item_id from the outer query....

 

[jal]

I added some carriage returns to your last version of it to make it more readable. I have at most an inkling of what you are trying to do - this looks like a Running_Total query and, if so, perhaps I can help you. (I have a wokring sample one in my notes which I got from a book, and recently I was able to use it to help someone on this forum. Here is what your wrote.

SELECT A.orderitem_id, A.item_id, A.order_id, A.item_qty, tblOrder.order_date, Q.pre_allocated_stock
FROM ( (tblOrder_Item AS A
INNER JOIN tblOrder AS B ON A.order_id = B.order_id)
INNER JOIN
(
SELECT Sum(X.item_qty) AS pre_allocated_stock, X.orderitem_id
FROM tblOrder_Item AS X
INNER JOIN tblOrder AS Y ON X.order_id = Y.order_id
WHERE X.item_id=A.item_id AND Y.order_date< B.order_id
GROUP BY X.orderitem_id
) AS Q ON Q.orderitem_id = A.orderitem_id
);

It seems to me that the big subquery section which is used to produce the Q table is for the purpose of a running total. If so, you may be able to do the following (PERHAPS). (I'm doing this in WordPad so expect tons of errors)First, let's put an outer SELECT around that first inner join (and I'm removing the preallocated column for the moment.
SELECT * FROM
(
SELECT A.orderitem_id, A.item_id, A.order_id, A.item_qty, tblOrder.order_date
FROM ( (tblOrder_Item AS A
INNER JOIN tblOrder AS B ON A.order_id = B.order_id)
) as BaseTable

And we can rewrite the above by expanding the asterisk like this:

SELECT orderitem_id, item_id, order_id, item_qty, order_date
FROM
(
SELECT A.orderitem_id, A.item_id, A.order_id, A.item_qty, tblOrder.order_date
FROM ( (tblOrder_Item AS A
INNER JOIN tblOrder AS B ON A.order_id = B.order_id)
) as BaseTable

And now we probably add a subquery to that outer SELECT to give you the desired running total (although it is likely I've totally misunderstood you).

SELECT orderitem_id, item_id, order_id, item_qty, order_date, (SELECT Sum(item_qty) FROM BaseTable as BaseTable2 WHERE BaseTable2.OrderDate < BaseTable.OrderDate) as PreAllocatedStock
FROM
(
SELECT A.orderitem_id, A.item_id, A.order_id, A.item_qty, tblOrder.order_date
FROM ( (tblOrder_Item AS A
INNER JOIN tblOrder AS B ON A.order_id = B.order_id)
) as BaseTable

This is just a sketch. Further it is likely that I don't have enough joins in there to give you what you want. And the problem with adding another level of nesting into my running total subquery is that if you go two levels deep, the engine will complain. So if this method isn't working, maybe we can find a method similar to what you are already doing. I am at work, so I won't be able to look into this until I get home.

 

[jal]

Well, my idea probably won't work. I just pasted into sql view and it doesn't like the fact that BaseTable is the result of a subquery - gives me a syntax error. Whereas if BaseTable is a real table, i don't get a syntax error. Sorry.

 

[jal]

Ok, the syntax error was a fluke - I had too many parentheses after the
last FROM clause in the query. I removed one.

 

[swisstoni]

Hmmm, it's now giving the error about "BaseTable does not exist..."

I agree I might not have made it particularly clear what I need, so I'll try a bit more:

I have tblOrder, tblOrder_Item and tblItem, each with their own ID column. tblOrder and tblItem are fairly self explanitory. tblOrder_Item ties up item_id's to an order_id.

I'd like a query to give me the following columns:


OrderItem_ID | Order_id | Item_id | Item_qty | PreAllocated
3 | 3 | 1 | 1 | 5
2 | 2 | 1 | 2 | 3
1 | 1 | 1 | 3 | 0


Where the order id's are based on orders where order # 1's date is before order # 2's and 2's is before 3...

 

[swisstoni]

I do believe I've done it!

SQL as follows if anyone was interested..

SELECT  B.orderitem_id, B.item_id, B.order_id, B.item_qty, order_date, 
(SELECT sum(item_qty) FROM tblOrder_Item INNER JOIN tblOrder ON tblOrder.order_id = tblOrder_Item.order_id WHERE order_date < A.order_date AND tblOrder_Item.item_id = B.item_id) AS pre_allocated_stock
FROM tblOrder_Item AS B INNER JOIN tblOrder AS A ON A.order_id = B.order_id;

Thanks for all your help people.

 

[jal]

Brilliant! At first I thought I was getting close, but now I see I probably would not have been able to figure it out. Great job.

 

[swisstoni]

Hmmmm okay, so now I've got a different problem...


I want to SELECT all orders where ALL items are in stock.

I've written the following SQL:

SELECT orderitem_id
 
FROM (qryOrder_Item INNER JOIN tblItem ON tblItem.item_id=qryOrder_Item.item_id)
 
WHERE (tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock-qryOrder_Item.item_qty)>=0

That will return me all the orderitem_id's that are in stock... But I'm having trouble matching that up to select ALL items on an order.


For example, I did the following but this crashes Access!

SELECT order_id

FROM tblOrder_Item AS A

WHERE 
 
(SELECT COUNT(orderitem_id) FROM tblOrder_Item WHERE order_id = A.order_id) 
 
= 
 
(SELECT COUNT(orderitem_id)
FROM (qryOrder_Item INNER JOIN tblItem ON tblItem.item_id=qryOrder_Item.item_id)
WHERE (tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock-qryOrder_Item.item_qty)>=0
AND order_id = A.order_id
);

This gives me the error I was getting in the first place ("it doesn't recognise A.order_id"). And I'm not sure if you can call "=" between queries to check that they return the same result - is there a keyword which will do this?

 

[jal]

You seem to want to exclude items of zero-stock. Maybe do it this way:

SELECT order_id
FROM tblOrder_Item
WHERE order_Id
NOT IN
(
SELECT order_ID
FROM (qryOrder_Item INNER JOIN tblItem ON tblItem.item_id=qryOrder_Item.item_id)
WHERE (tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock-qryOrder_Item.item_qty) = 0
);



Perhaps ???

 

[swisstoni]

Okay, I made a few alterations:

SELECT order_id FROM tblOrder_Item
 
WHERE orderitem_Id

NOT IN 
 
(SELECT orderitem_id FROM
(qryOrder_Item INNER JOIN tblItem ON tblItem.item_id=qryOrder_Item.item_id)
 
WHERE
 
 (tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock-qryOrder_Item.item_qty) < 0)

the WHERE NOT IN, I change that to orderitem_id, and changed the = 0 to < 0.

But that appears to do pretty much the same as my query - it will return the order_id's of any orders that have at least one item in stock. I need to somehow check ALL items on an order are in stock...

 

[jal]

Well, maybe we can go at it the opposite way. Let's find order_IDs with at least one item that is unstocked (as the inner result set), and then the desired result set (the outer set) will those orderIds NOT IN the inner result set.

SELECT OrderID FROM tblOrder_item WHERE
OrderID NOT IN

(


SELECT order_id FROM tblOrder_Item
 
WHERE orderitem_Id
IN 
 
(SELECT orderitem_id FROM
(qryOrder_Item INNER JOIN tblItem ON tblItem.item_id=qryOrder_Item.item_id)
 
WHERE
 
 (tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock-qryOrder_Item.item_qty) < 0)


)

 

[swisstoni]

Brilliant - works a charm :-D

Once again, many thanks for your help!

 

[swisstoni]

Well, almost perfect....!

The final code is shown here, it sets orders to "processing" if all items are in stock:

UPDATE tblOrder
 
SET order_status = 'processing' WHERE
 
order_id IN (
 
SELECT DISTINCT tblOrder_item.Order_ID
FROM tblOrder_item
WHERE (((tblOrder_item.Order_ID) Not In (SELECT order_id FROM tblOrder_Item
 
WHERE orderitem_Id
IN 
 
(SELECT orderitem_id FROM
(qryOrder_Item INNER JOIN tblItem ON tblItem.item_id=qryOrder_Item.item_id)
 
WHERE
 
 (tblItem.item_shelfstock-qryOrder_Item.pre_allocated_stock-qryOrder_Item.item_qty) < 0)

))));

But it's hellishly slow..... I've only got around 3 orders in there and about 10 rows in order_item during testing, and it takes around 15 seconds to execute the above...!

This is all on ODBC link tables, so I tried making this a passthrough query, but it fails because qryPreAllocatedStock is stored in Access...

 

[jal]

Thanks for reporting the slowness because your experience might prove helpful to me. What I mean is, Microsoft says that the IN keyword is slow and therefore should be rephrased as INNER JOIN wherever possible.

But the other day I ran a quick test and had good performance with the IN keyword, and then was left uncertain as to whether my test was conclusive. So if we can find a way to rewrite this query as a JOIN, and then if we get better performance, this will help me draw a final conclusion on this issue.

Queries with the IN keyword are sometimes more readable than JOIN queries. I don't mind losing a little performance if it makes my code more readable. But I sure don't want to lose ALL performance.

Gotta go - maybe I'll have time later to reexamine the query.

 

[jal]

Forgot to mention something - this was a personal discovery of mine that seemed to shock a few veterans both on this forum and others. (I know it's odd that a beginner like me would make a discovery of any kind, but it's true).

I found out that a JOIN that uses the OR keyword can kill performance because neither sql Server nor Access optimizes such a query.
I had a JOIN query that was taking 2 minutes every time (and about 1 minute in sql Server). When I eliminated the OR keyword, brought it down to 2 seconds !!! I uploaded the data on this forum and others, challenging people to prove me wrong. Those who tried failed.

Here's what I mean.

SELECT C.* FROM Customers as C
INNER JOIN ZipCodes as Z
ON Z.Zip = C.Zipcode1
OR Z.Zip = C.ZipCode2


Slow !!!! And this is true even if you write it like this;

SELECT C.* FROM Customers as C, ZipCodes as Z
WHERE Z.Zip = C.Zipcode1
OR Z.Zip = C.ZipCode2

However, you can avoid the OR-keyword by rewriting this as a UNION of two queries:


SELECT C.* FROM Customers as C
INNER JOIN ZipCodes as Z
ON Z.Zip = C.Zipcode1

UNION ALL

SELECT C.* FROM Customers as C
INNER JOIN ZipCodes as Z
ON Z.Zip = C.Zipcode2


FAST !!!!

So please check ALL your queries to determine if any of your JOINs are relying on the OR keyword. If so, that might be your bottleneck.