Converting Access qrys with logical expressions

[Terry Bell]

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

[Simon Hayes]

"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

[Terry Bell]

"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

[Simon Hayes]

<snip>

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?
Not quite - there is no Boolean data type in MSSQL, so how to evaluate
'true' or 'false' depends on the data type you've chosen. One common
solution is to use the bit data type, with 1 for true and 0 for false, in
which case your code above is correct (assuming true = 1).

Then I guess I need to think about NULL too ...
Yes - this is one reason why you often see requests for DDL (CREATE TABLE
etc.), as this makes it clear which columns allow NULL and which don't.
Something that seems to work fine may fail when NULLs are involved, so you
need to code for them if the data model allows them.

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?
I have no idea without seeing the full error, but perhaps this is error 8153
"Warning: Null value is eliminated by an aggregate or other SET operation."?
If so, it's just a warning that the column you SUMmed on contains NULL data.

Once again thanks a million this has saved me lots of time

Terry Bell

You're welcome.

Simon

[Adrian Edwards]

Generally just copy and paste from Access to Query Analyser. Check the
query runs correctly and then add CREATE PROCEDURE blah blah to the top
and run. This turns the script in to a stored procedure and loads it in
to the current database.

You might want to move all the restrictions to the WHERE clause other
wise you can get some interesting results if you are not very careful.

Sum((BackOrderQtyAvailable)* -1)

(BackOrderQtyAvailable > 0)
Adrian

Terry Bell wrote:

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

[Ross Presser]

On Thu, 16 Sep 2004 17:27:35 +0200, Simon Hayes wrote:

"Terry Bell" <dr**********@hotmail.com> wrote:

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?

I have no idea without seeing the full error, but perhaps this is error 8153
"Warning: Null value is eliminated by an aggregate or other SET operation."?
If so, it's just a warning that the column you SUMmed on contains NULL data.

I don't think it's a null error -- I think he was editing his query in MS
Access's query editor, in an ADP file, rather than using SQL Server's Query
Analyzer. I've gotten that error from MS Access myself.

As Terry said, Access goes ahead and executes it anyway. It just can't
parse it properly to represent it in the graphical query editor.