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
