I'm trying to figure out if this is even possible. I haven't had any luck on my own using the following routine and any similar. I'm trying to apply logic on a resident table and make field values based on the initial logic. I realize the following is probably not syntactically correct. Assume the variables are valued values.
Anything like this possible without loading the resident table 3x to get the desired result?
Thanks Sunny, that's kind of what we figured, but still wanted another set of eyes. Now 15 minutes per run or 45 total. I have seen longer as you've mentioned. It's still faster than having it in the users' front end.
I'm not certain of the number of rows in the source table (this is a contemporary's project) but I believe it is in the 10's of million of rows. The source table is created reading a database on a daily basis.
I (and my cohort) appreciate the thought provided.
You could start by trying to remove the IF functions from the Count() aggregation functions. Since you're reading everything from an internal table, it would be better to create field copies that have their value set to NULL if the condition xxx<=0 is fulfilled during the initial LOAD.
Another thought: set one of three flags to 0/1 if the condition is fulfilled, and use Sum() instead of Count() to create the counts. Sum() is way faster than Count(). However the DISTINCT keyword may force you to create a complex version of this simple expression.
