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((BackOrderQ tyAvailable>0)*-1) AS ReadyBackOrderI tems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.C lientId = Items.ClientId)
AND (OrderDetails.I temId = Items.ItemId)
WHERE (NOT (SitesCustomerT ypeId = 2
AND ExpressBackorde r =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBac kOrderItems that I can use in further joins?

Thanks in anticipation
Terry Bell

[Simon Hayes]

"Terry Bell" <dr**********@h otmail.com> wrote in message
news:92******** *************** **@posting.goog le.com...

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((BackOrderQ tyAvailable>0)*-1) AS ReadyBackOrderI tems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.C lientId = Items.ClientId)
AND (OrderDetails.I temId = Items.ItemId)
WHERE (NOT (SitesCustomerT ypeId = 2
AND ExpressBackorde r =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBac kOrderItems 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 BackOrderQtyAva ilable > 0 then BackOrderQtyAva ilable 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.bluewi n.ch>...

"Terry Bell" <dr**********@h otmail.com> wrote in message
news:92******** *************** **@posting.goog le.com...

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((BackOrderQ tyAvailable>0)*-1) AS ReadyBackOrderI tems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.C lientId = Items.ClientId)
AND (OrderDetails.I temId = Items.ItemId)
WHERE (NOT (SitesCustomerT ypeId = 2
AND ExpressBackorde r =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBac kOrderItems 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 BackOrderQtyAva ilable > 0 then BackOrderQtyAva ilable 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 Q845Undelivered OrderDetails.Or derId, SUM(CASE WHEN
BackOrderQtyAva ilable > 0 THEN 1 ELSE 0 END) AS ReadyBackOrderI tems
FROM Q845Undelivered OrderDetails INNER JOIN
Items ON (Q845Undelivere dOrderDetails.C lientId =
Items.ClientId) AND (Q845Undelivere dOrderDetails.I temId =
Items.ItemId)
WHERE (NOT (SitesCustomerT ypeId = 2 AND ExpressBackorde r = 1 AND
OrderUrgency = 1))
GROUP BY Q845Undelivered OrderDetails.Or derId;
So:
Sum((BackOrderQ tyAvailable>0)*-1) AS ReadyBackOrderI tems ... in Access
SQL
becomes
SUM(CASE WHEN BackOrderQtyAva ilable > 0 THEN 1 ELSE 0 END) AS
ReadyBackOrderI tems ... 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((BackOrderQ tyAvailable)* -1)

(BackOrderQtyAv ailable > 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((BackOrderQ tyAvailable>0)*-1) AS ReadyBackOrderI tems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.C lientId = Items.ClientId)
AND (OrderDetails.I temId = Items.ItemId)
WHERE (NOT (SitesCustomerT ypeId = 2
AND ExpressBackorde r =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBac kOrderItems 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**********@h otmail.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.