Let me offer my opinions re: using SPs to retrieve data. (I will talk about using SPs to create and update BOs in a different post. )
SPs integrate very well with Aware and there are many times they are the ONLY viable solution.
While Aware can store "totals" in a parent BO. (i.e. store the total owed in a customer BO that is related to an order BO with a process that looks like "IF Customers.omOrders WAS CHANGED THEN Customers.totalOwed = SUM (Orders.balance WHERE Orders in Customers.omOrders)" and this DOES work well, it fails when you need a selective range of dates or other criteria. If you wanted to find:
- Total Sold in the last "X" months
- Customers who places more than "X" orders that totals more than "Y" dollars
You would have to create a process to run through all of the customer records and run a SUM operation. This is VERY costly. Remember that SQL is a set based system, so to compute the 1st option mentioned above for 10 customers or 10,000 customers will only take a marginally different amount of time.
It does not matter if you are querying SQL tables or views or calling an existing SP to retrieve the data.
Views and Tables and existing SPs that return a result set are all treated the same.
Here is what I learned and how I use SP's for data retrieval
1st, figure out if you have an existing BO that you want to return the results to or will you need to create a new one.
This is interesting, Lets use the above example, I have a customer BO with an attribute called TotalOwed that has the current customer balance. Our requirement is to display customers and show the total amount sold (not owed) in the last 3 months. You can have a query with the SQL statement looking like "EXEC_SP Compute3MonthSales RETURN Customers" In the query, the column that says TotalOwed, change the heading to 3 Month Sales and viola, when you run this SP, it will return a "virtual" dataset, that looks just like your customer BO showing the 3 months sales total. Assuming you are returning the correct ID for the customer, you can have related Queries, to link to the order table, etc. etc.
However, some times, what you are returning does not look like any existing table. No problem. Design a BO that is not persistent and have your query return data to that BO. Again, if you return a "good" ID field, it can still link to other tables.
2nd, there are 2 "hidden" fields in every BO, called BASVERSION and BASTIMESTAMP. if you do not return these, then Aware will not be happy. Also all foreign keys in child tables have 2 "hidden" fields also, that have constants. If you are returning child records, you have to populate these fields also.
3rd, after you run your SP Query, click the view button next to the Aware IM Server line on the control panel. You will see something similar to below:
The column name maxStops is not valid.
The column name fax is not valid.
The column name whseTerms_REN is not valid.
The column name whseTerms_RID is not valid.
The column name whseTerms_RMA is not valid.
The column name unshippedCost is not valid.
The column name lastOrder is not valid.
The column name lastYearSales is not valid.
The column name active is not valid.
This is fine, These are all the columns that you did NOT return in your SP. If you see any error msgs for columns (or relationships) that you require, then go debug your SP.
Good Luck
Bruce
