Jenna -
I think you're on the right track. Just union the two views, then do
another view with a group by on the item number, summing the quantity.
You could also combine the two views as one and save a step -
CREATE VIEW Availability (ItemID, SumQuan) AS SELECT ItemID,SUM(Quan) FROM
LineItems WHERE Direction = "Rcvd" +
UNION SELECT ItemID,SIGN(SUM(Quan),-1) FROM
LineItems WHERE Direction = "Ship"
Or, you MIGHT be able to do something like this, all in one statement:
SELECT ItemID,SUM(IFEQ(Direction,"Ship",(SUM(Quan)*-1),(SUM(Quan)))) FROM
LineItems GROUP BY ItemID
Sami Aaron
Hampshire Technology Group, LLC
6310 Lamar Ave, Suite 220
Overland Park, KS 66202
913-403-8100 Fax: 913-403-7689 Cell: 913-208-7205
http:\\www.hampshiretechnology.com
mailto:[EMAIL PROTECTED]
-----Original Message-----
From: Jenna Klein [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 30, 2001 4:28 AM
To: [EMAIL PROTECTED]
Subject: Shipping and Receiving
I've been tasked with creating an in-house shipping and receiving system. If
this is successful we may make a similar system available to our clients.
The part I am blocked on seems like it should be easy. I need to build a
view that gives me available (to ship) quantities. The reason I need a view
is that we are wanting to access the data from outside of R:BASE via ODBC.
This will also facilitate reports in R:BASE based on the view. I am thinking
that I could create two views, one that sums up received quantities and one
that sums up shipped quantities (grouping by the non-summed fields) If I
could find a way to change the shipped quantities to negative then I can
sum/group those two views together again creating an available view. I can
create a view like this...
CREATE VIEW Received (ItemID, Quan) AS SELECT ItemID,SUM(Quan) FROM
LineItems WHERE Direction = "Rcvd"
But can I create a view like this?
CREATE VIEW Shipped (ItemID, Quan) AS SELECT ItemID,SIGN(SUM(Quan),-1) FROM
LineItems WHERE Direction = "Ship"
But now what? If that works I've got something like this:
Rcvd Ship
1,10 1,-9
2,100 3,-390
3,400
But how do I take those views and get something like...
Available
1,1
2,100
3,10
Can I just union the two views together? Then re-group for a third view? Is
there a better way that doesn't involve temporary tables or projecting
tables? (Since I need this to be dynamic and always available? And since the
client may not be R:BASE or smart) I suspect that if the SIGN function will
work I can just union select the views together and then use the client (be
it R:BASE or something like Crystal) to SELECT the final summary grouping.
Thank you.
Jenna
