SQL query NEED CHECKING

[jonathan.cohen150887]

Ok I am using this query to delete the books from the book_stock table
that havent sold for the past 3 months.
To do it accoridng to the date I am using the order dates from the
customerorders table, and to know which ISBN_Ref
corresponds to which order i included the table
customer_orders_books.ISBN_Ref.

I have written up the following but it says I have a wrong number of
arguments.Could any of you have a look at it plz?

Thanks

Since some books may not have been ordered at all I also included the
line at the end:

DELETE Book_Stock.ISBN.Ref FROM Book_Stock
WHERE Book_Stock.ISBN.Ref =
(SELECT Book_Stock.ISBN.Ref, CustomerOrders.CO_ORDERDATE,
Customer_Books_Orders.ISBN_Ref
FROM Book_Stock, CustomerOrders, Customer_Orders_Books
WHERE Book_Stock.ISBN.Ref = Customer_Orders_Books.ISBN_Ref
HAVING CustomerOrders.CO_ORDERDATE < DateAdd("m",-3,Date(15/04/2008))
AND Book_Stock.ISBN.Ref <> Customer_Orders_Books.ISBN_Ref)

But still, I get an error saying I have the wrong number of arguments

 

[a a r o n . k e m p f]

Is this for a class?

Well I'm pretty sure-- this is 'air code'-- but here goes:

a) you got an extra parenthesis at the end.. Maybe even 2 extra parens
b) the HAVING clauses that you had-- it should have been on the
WHERE clause.. for performance reasons. (most access people around
here don't know anything about performance)
c) bottom line is that you're joining to a subquery.. and returning
_THREE_ fields. You just need to select ONE field.. not three.
d) you want to use an IN clause.. not a = clause.. for filtering
against the results of the subquery.
e) I don't think that you need the BOOK_STOCK table in the subquery.
It looks like you've got cartesianing there
f) you shouldn't allow a _PERIOD_ in a fieldname or an object name--
no special characters anywhere for any reason
g) you don't ever want to delete any data from any database.. for any
reason. you should put it in an archive table-- or set it 'IsActive =
False'. But deleting the data is kindof a harsh response.
h) you probably should use an NOT IN instead of an IN clause. I just
think that it would be easier.
But you can't use a NOT IN clause if you've got a million records
for example. That is when you need to shy away from the IN clause
altogether.
i) I just don't think that you're really grasping the concept of a
'correlated subquery'. Correlated subqueries are when are _BIND_ the
subquery to the parent query to get the results you want. That is
what the last piece is doing.

DELETE
FROM Book_Stock
WHERE Book_Stock.ISBN.Ref NOT IN
(
SELECT DISTINCT Customer_Orders_Books.ISBN.Ref
FROM Customer_Orders_Books INNER JOIN CustomerOrders
ON Customer_Order_Books.OrderID = CustomerOrders.OrderID
WHERE
CustomerOrders.CO_ORDERDATE < DateAdd("m",-3,Date(15/04/2008)
AND
CustomerOrders.ISBN.Ref = Book_Stock.ISBN.Ref
)

 

[jonathan.cohen150887]

Is this for a class?

Well I'm pretty sure-- this is 'air code'-- but here goes:

a) you got an extra parenthesis at the end.. Maybe even 2 extra parens
b) the HAVING clauses that you had-- it should have been on the
WHERE clause.. for performance reasons. (most access people around
here don't know anything about performance)
c) bottom line is that you're joining to a subquery.. and returning
_THREE_ fields. You just need to select ONE field.. not three.
d) you want to use an IN clause.. not a = clause.. for filtering
against the results of the subquery.
e) I don't think that you need the BOOK_STOCK table in the subquery.
It looks like you've got cartesianing there
f) you shouldn't allow a _PERIOD_ in a fieldname or an object name--
no special characters anywhere for any reason
g) you don't ever want to delete any data from any database.. for any
reason. you should put it in an archive table-- or set it 'IsActive =
False'. But deleting the data is kindof a harsh response.
h) you probably should use an NOT IN instead of an IN clause. I just
think that it would be easier.
But you can't use a NOT IN clause if you've got a million records
for example. That is when you need to shy away from the IN clause
altogether.
i) I just don't think that you're really grasping the concept of a
'correlated subquery'. Correlated subqueries are when are _BIND_ the
subquery to the parent query to get the results you want. That is
what the last piece is doing.

DELETE
FROM Book_Stock
WHERE Book_Stock.ISBN.Ref NOT IN
(
SELECT DISTINCT Customer_Orders_Books.ISBN.Ref
FROM Customer_Orders_Books INNER JOIN CustomerOrders
ON Customer_Order_Books.OrderID = CustomerOrders.OrderID
WHERE
CustomerOrders.CO_ORDERDATE < DateAdd("m",-3,Date(15/04/2008)
AND
CustomerOrders.ISBN.Ref = Book_Stock.ISBN.Ref
)

Thanks for the answer dude. But unfortunately I have very little idea
of what you are talking about in there as this is for a project, and
we are finance students so have nop clue about programming.

DELETE FROM Book_Stock
WHERE Book_Stock.ISBN.Ref =
(SELECT Book_Stock.ISBN.Ref, CustomerOrders.CO_ORDERDATE,
Customer_Books_Orders.ISBN_Ref
FROM Book_Stock, CustomerOrders, Customer_Orders_Books
WHERE Book_Stock.ISBN.Ref = Customer_Orders_Books.ISBN_Ref
HAVING CustomerOrders.CO_ORDERDATE < DateAdd (m,-3, Date (15/04/2008))
AND Book_Stock.ISBN.Ref <> Customer_Orders_Books.ISBN_Ref)

I have changed the beginning so it deletes the row all together.
However for the rest of the stuff you mentionned I have no idea how
you are supposed to implement that in the query .

I know sorry, I am a total NOOB when it comes to programming!

 

[Tom Wickerath]

Jonathan,

Thanks for the answer dude. But unfortunately I have very little idea
of what you are talking about in there as this is for a project, and
we are finance students so have no clue about programming.

May I suggest that you re-read Aaron's reply? He made no mention of any
programming. However, he did tell you that including the HAVING clause in
your SQL statement may not be the best idea.

I have changed the beginning so it deletes the row all together.

Why? Why not just set a flag for inactive record?
Hint: This requires the addition of a new field to your table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________