Using OpenEdge 11.2 on Linux, is there a way when doing a "FOR EACH" with a "BREAK BY" to skip a block of records?
I have the following:
inv-blk: FOR EACH InvHeader_DT NO-LOCK WHERE InvHeader_DT.Store_ID = 1 AND InvHeader_DT.Invoice_Date >= 11/01/2017 AND InvHeader_DT.Invoice_Date <= 11/30/2017 BREAK BY InvHeader_DT.Customer_ID: FIND Customer NO-LOCK WHERE Customer.Customer_ID = InvHeader_DT.Customer_ID NO-ERROR. IF Customer.Hide_From_GST_Report THEN DO: MESSAGE "Hide Customer " Customer.Customer_ID. NEXT inv-blk. END. DISPLAY InvHeader.Customer_ID. END.
The problem is that I need it to just skip over every single invoice for that customer who has that flag set.
I tried by putting the flag check in with the 'FOR EACH' by having 'FOR EACH ..., FIRST CUSTOMER' but that just made it take a little bit longer.
The current method takes about 2secs, having a 'first' in with the 'for each' made it take about 4secs.
Only read the customer on first-of(). Set a flag if need to skip. After the first-of block, do a next if the flag is set. Don’t forget to reset the flag when required.
Re-structure the query as follows, probably faster in running as well. Hopefully you'll have an index on the Hide_From_GST_Report field?
cust-blk:
FOR EACH Customer NO-LOCK WHERE NOT Customer.Hide_From_GST_Report:
DISPLAY Customer.Customer_ID.
/* do any first-of customer logic here */
inv-blk:
FOR EACH InvHeader_DT NO-LOCK WHERE
InvHeader_DT.Customer = Customer.Customer_ID AND
InvHeader_DT.Store_ID = 1 AND
InvHeader_DT.Invoice_Date >= 11/01/2017 AND InvHeader_DT.Invoice_Date <= 11/30/2017:
END.
END.
There is no index on 'Hide_From_GST_Report' at all, Although I said 'Customer', the table that the flag is in is not the same, I was just trying to be generic with my question.
The name 'Customer' is a bad name too, I should have said 'Account', since the 'Account' table has accounts for our stores, customers, suppliers, delivery companies, etc... and then hanging off of that master table is sub-tables depending on the type (Store, Customer, Supplier, Delivery, etc...).
The flag is stored in a 'AccountFlag' table which has an index on the 'Customer_ID' (Don't ask, I didn't design it this way and it won't be changing any time soon).
Also, although I used 'Store_ID = 1' in my example, there are actually 17 stores in total (4 of which aren't customer facing ones).
The full example would be:
FOR EACH Store NO-LOCK WHERE Store.Store_Deleted = FALSE AND Store.Requires_GST_Totals = TRUE:
inv-blk:
FOR EACH InvHeader_DT NO-LOCK
WHERE InvHeader_DT.Store_ID = Store.Store_ID
AND InvHeader_DT.Invoice_Date >= 11/01/2017
AND InvHeader_DT.Invoice_Date <= 11/30/2017 BREAK BY InvHeader_DT.Customer_ID:
FIND AccountFlag NO-LOCK WHERE AccountFlag.Customer_ID = InvHeader_DT.Customer_ID NO-ERROR.
IF AccountFlag.Hide_From_GST_Report THEN
DO:
MESSAGE "Hide Customer " InvHeader.Customer_ID.
NEXT inv-blk.
END.
DISPLAY InvHeader.Customer_ID.
END.
END.
At first I tried doing the whole flag thing in a 'FIRST-OF', but it didn't seem to work at all, I could have of course done something very wrong when I tried it.
My current attempt is:
FOR EACH Store NO-LOCK WHERE Store.Store_Deleted = FALSE AND Store.Requires_GST_Totals = TRUE:
inv-blk:
FOR EACH InvHeader_DT NO-LOCK
WHERE InvHeader_DT.Store_ID = Store.Store_ID
AND InvHeader_DT.Invoice_Date >= 11/01/2017
AND InvHeader_DT.Invoice_Date <= 11/30/2017,
FIRST AccountFlag NO-LOCK
WHERE AccountFlag.Customer_ID = InvHeader.Customer_ID
AND AccountFlag.Hide_From_GST_Report NE FALSE
BREAK BY InvHeader_DT.Customer_ID:
DISPLAY InvHeader.Customer_ID.
END.
END.
But that makes it take an extra second or 2 in total.
In all timing tests I have found that the 'FOR EACH' on 'Store' is extremely fast, less than 0.02s in total, but that's because we only have like 17 stores where the 'Store_Deleted' flag is FALSE and a couple where the 'Store_Deleted' flag is TRUE.
There is an index on 'InvHeader' which fits what I have opted for:
Store_ID
Invoice_Date
Customer_ID (Obviously I don't use Customer_ID in my 'FOR EACH' on 'InvHeader' but the index is still the chosen one when I do an xref in mpro).
The only index on 'AccountFlag' is for 'Customer_ID'.
Obviously the problem here is that it still has to investigate every single InvHeader row before determining if it needs to skip it or not which adds time (for 1 store it took 0.2s, most stores seem to take about 0.15s).
There are 84,000 InvHeader records that fall between 11/01/2017 and 11/30/2017, customers have a default store they purchase from but there is nothing stopping them from purchasing from other stores.
Looking at AccountFlag, there are 2 accounts that have the flag set to 'N', one is our main warehouse (which feeds each individual store when they get low on stock), the other is our stock transfer account, neither of which are involved in GST since it is all inter-company.
What if you start with account-flag, and sort it by customer-ID. From there get the invoices within the date-range, and then get the store of the invoice header. If a customer is buying only at one store, that should work fine and might be faster (as results are in customer-ID order already and don't need to be sorted after being retrieved). Second requirement for this to be fast is that you do have an index on InvHeader with the customer_ID as first component and the the date as second.
Another alternative might be: Since you don't have many stores, you could run through account-flag, then all relevant stores, and then invoices with given store_id and and customer_ID. Again, works only if index in invHeader has customer_ID and store_id as first and second component (in either order). Second assumption: you don't have a ton of customers with no invoices... (And you could try to with stores and account-flag in the query, but leave invHeader as third table)
Well, just a thought...
Good luck