"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...
"Terry Bell" <dr**********@hotmail.com> wrote in message
news:92*************************@posting.google.co m... I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;
Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?
Thanks in anticipation
Terry Bell
Are you asking how to rewrite the SUM expression? I don't know exactly what
the syntax above means, so this is a guess:
sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
end * -1)
If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
to create your tables and populate some sample data, along with the result
you expect to see from your query.
Simon
Thanks very much Simon you have given me the direction I needed.
For the record, here's my full converted code - with some side errors
fixed
SELECT Q845UndeliveredOrderDetails.OrderId, SUM(CASE WHEN
BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS ReadyBackOrderItems
FROM Q845UndeliveredOrderDetails INNER JOIN
Items ON (Q845UndeliveredOrderDetails.ClientId =
Items.ClientId) AND (Q845UndeliveredOrderDetails.ItemId =
Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2 AND ExpressBackorder = 1 AND
OrderUrgency = 1))
GROUP BY Q845UndeliveredOrderDetails.OrderId;
So:
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems ... in Access
SQL
becomes
SUM(CASE WHEN BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS
ReadyBackOrderItems ... in SQL
I also note that in Access you can say something like
WHERE IsBackOrder
and it evaluates IsBackOrder as a logical expression
whereas in sql server we need to say
WHERE IsBackorder = 1
Is that right?
Then I guess I need to think about NULL too ...
Also I notice in the query analyser it comes up with a message saying
it can't understand the CASE statement, but I can ignore that, can I,
as it seems to go ahead and execute the query anyway?
Once again thanks a million this has saved me lots of time
Terry Bell