Past Performances - Display SQL UDMs Prob Expressions - JCapper Help Doc

Last Updated: July 08, 2017
 
Prob Expressions - Overview
In this help doc, I'm going to:
  1. Introduce a new concept in JCapper UPR Tools1 called a Prob Expression.

  2. Walk you through Prob Expression sql syntax.

  3. Walk you through using the Prob Expressions Tool in UPR Tools to manage Prob Expressions.

  4. Point out basic stuff you need to be aware of - such as optimizing query speed and avoiding after the fact results.

  5. Take you step by step - complete with screenshots - through the process of assigning a Prob Expression to your SQL Factor Setup. And from there, point out some of the things you need to be aware of when displaying Prob Expressions on your SQL HTML Report.

  6. Take you step by step through the process of adding a Prob Expression to a UserFactor or UPR GroupName.

  7. Present you with a list of valid behaviors and factor handles for JCapper factors that are supported in the Prob Expressions Tool in UPR Tools.

After reading this help doc you should be able to use the Prob Expressions Tool in UPR Tools to create, edit, clone, and delete Prob Expressions of your own.

1. UPR Tools and Prob Expressions are only available in JCapper Platinum.




What is a Prob Expression?
A Prob Expression is a user defined SQL Expression that is executed for each horse in the race - one horse at a time - when you click the Calc Races and/or Build Database buttons in JCapper Platinum. The query results returned by the Prob Expression for each horse are transformed by the interface into factor numeric value, rank, and gap.

Depending on your creativity and ability to apply critical thinking - Prob Expressions can deepen your insight into the game. Well designed Prob Expressions can help you understand why certain types of horses run better (or worse) than expectations. Prob Expressions can also help you better understand how the public bets the races.

However, Prob Expressions are not a magic bullet.

For example, you can create Prob Expressions based on early factors such as AvgE1, CPace, EarlyConsensus, etc. This type of Prob Expression can generate accurate statistics (WinPct, WinPlacePct, Roi, etc.) that relate to how speed favoring (or speed tiring) any given track-intsurface-dist has been playing - across any calendar time span - or any series of races.

But you need to keep in mind that stats generated by Prob Expressions merely reflect what has happened in the past - and that race outcomes going forward in time after a Prob Expression has been created can be shaped by changes in weather or track maintenance so as to play out in a completely different manner than what you might expect from the data.

Every Prob Expression has its own unique name and is stored in its own row in the ProbExpressions Table inside of the c:\2004\JCapper.mdb file.

Prob Expressions appear in the factors drop downs of the various screens throughout UPR Tools - and just like other JCapper factors - Prob Expressions can be used in the entries that make up your UserFactors and UPR.

Prob Expressions appear in the available factors drop down on Screen One of the SQL Factor Setup Wizard. Prob Expressions can be assigned to F-slot numbers in your SQL Factor Setup.

Prob Expressions can be displayed on your customizable SQL HTML Report Layout.

Prob Expressions can be used in SQL UDMs.

There are two types of Prob Expressions:
  1. Prob Expressions that are contained in a UserFactor or UPR GroupName.

  2. Prob Expressions that are not contained in a UserFactor or UPR GroupName - but that have been assigned their own F-Slot number in your SQL Factor Setup.

    Note: This type of Prob Expression is known as a "containerless" Prob Expression.

--Note: A table is presented later on in this Help Doc that contains a complete list of valid Behaviors for Prob Expressions. Hint: Scroll down and look for the headline labeled "SUPPORTED BEHAVIORS FOR PROB EXPRESSIONS" (without the quotes.)




Prob Expression Scoring illustration using Behavior 23
Assume that you have EarlyConsensus assigned to slot nummber F18 in your SQL Factor Setup.

Let's further assume that you have a Prob Expression based on "AND RANKF18 = {RANKEARLYCONSENSUS}" (without the quotes) assigned to a different slot number in your SQL Factor Setup and that you have applied Behavior 23 (which is WIN-PLACE PCT) to the same slot number in your SQL Factor Setup that the Prob Expression has been assigned to.

Given the above, this is what happens during a SQL Calc Races:
  • The Prob Expression is executed for each horse in the race - one horse at a time.

  • Let's assume for purposes of this example that the first horse in the race happens to be EarlyConsensus rank = 1 and that the returned query results have a Win-Place percentage of 0.4186. The Prob Expression factor score generated by the interface for the first horse in the race would be 0.4186.

  • Let's assume for purposes of this example that the second horse in the race happens to be EarlyConsensus rank = 2 and that the returned query results reveal a Win-Place percentage of 0.3103. The Prob Expression factor score generated by the interface for the second horse in the race would be 0.3103.

  • Let's assume for purposes of this example that the third horse in the race happens to be EarlyConsensus rank = 3 and that the returned query results reveal a Win-Place percentage of 0.2144. The Prob Expression factor score generated by the interface for the third horse in the race would be 0.2144.

  • The interface keeps executing the Prob Expression until every horse in the race has been scored. (And from there, program flow moves on to the next race where the process is repeated.)
If you give this some thought: Prob Expressions are actually handicapping factors - handicapping factors that you create!




Prob Expression Syntax - Explained

The following is valid Prob Expression syntax where the objective is to return the most recent 80 starters for each RailPosition at today's track code, distance, and surface:
	SELECT TOP 80 * FROM STARTERHISTORY 
       		WHERE TRACK = {TRACK}   
       		AND DIST = {DIST}   
       		AND INTSURFACE = {INTSURFACE}   
       		AND RAILPOSITION = {RAILPOSITION}   
       		AND [DATE] < {TODAY}   
       		ORDER BY [DATE] DESC


Let's walk through the above Prob Expression one line at a time. The first line of sql uses the TOP keyword to limit the query results to the first 80 records returned by the query:
	SELECT TOP 80 * FROM STARTERHISTORY 

Skipping ahead to the last line of the expression we have:
       	ORDER BY [DATE] DESC
This line causes the query results to be sorted in reverse date order.

Sorting the query results in reverse date order is needed to accomplish the stated objective of returning the most recent 80 starters for each RailPosition at today's track code, distance, and surface.

If the final line of the expression is omitted: The query results won't be sorted in reverse date order - and the query will be prevented from returning the most recent 80 starters fitting the other factor constraints. Instead, the query results will be the first 80 starters fitting the factor constraints. Give this some thought before moving on.

--Hint: You might even want to experiment by pulling up a SQL UDM in the Data Window and opting to display individual plays.

Try changing the text in the SQL Expression Tool from "SELECT * FROM" to "SELECT TOP 5 * FROM" (each without the quotes) and then make "ORDER BY [DATE] DESC" (again without the quotes) the last line of the expression before clicking the Execute button.

Note the results set afterwards. Then remove "ORDER BY [DATE] DESC" (without the quotes) from the expression and re-execute. Note what happens to the results set this time.

Taking the time to actually do this will help you understand how query results are impacted by the way you use the TOP and ORDER BY keywords in conjunction with each other.

Shifting gears from the above Data Window tangent - and returning discussion once again to Prob Expression sql syntax - the remaining conditionals define the attributes of the 80 starters returned:
	WHERE TRACK = {TRACK}
The above line of sql adds a track code conditional to the most recent 80 rows returned by the results set.

Note in the above line how the TRACK field is contained inside of those two curly bracket characters.

When you wrap curly bracket characters around a valid table field name in a Prob Expression: You are telling the interface to replace the curly brackets and the table field name inside the brackets with the actual value of that field for the current horse being processed internally within the JCapper program.

So if the interface were executing the above line of the Prob Expression as part of a Calc Races for a race at BEL:

Upon encountering "WHERE TRACK = {TRACK}" the interface is going to replace {TRACK} with 'BEL' -- and from there correctly evaluate that part of the sql expression as WHERE TRACK = 'BEL'

Note: This behavior is the same no matter what the field name inside of the brackets. Whenever you wrap a (valid) supported table field name inside of curly brackets -- provided you use a valid supported table field name -- the interface will make the replacement before executing the Prob Expression.

Moving on to the next line we have:
	AND DIST = {DIST}
Here the interface is going to replace {DIST} with the actual numeric race distance in yards for the race of the current horse being processed. (If the actual race distance is 6f or 1320 yards: AND DIST = {DIST} will become AND DIST = 1320 prior to execution.)

--A table of supported factor handles is presented towards the bottom of this help doc. In addition to {DIST} note that {DIST_TYPE} is also a valid factor handle.

Suppose that instead of "AND DIST = {DIST}" (without the quotes) - the following line had been used in the Prob Expression:
	AND DIST = {DIST_TYPE}
Had that been the case the interface would replace {DIST_TYPE} with a numeric range that matches the distance type for the race of the current horse being processed.

Here's how that works:
  • If DIST for the current race is < 1760:
    Distance type for the current race is recognized as Sprint:
    AND DIST = {DIST_TYPE} becomes AND DIST < 1760 prior to execution.

  • If DIST for the current race is >= 1760:
    Distance type for the current race is recognized as Route:
    AND DIST = {DIST_TYPE} becomes AND DIST >= 1760 prior to execution.


Moving on to the next line we have:
	AND INTSURFACE = {INTSURFACE}
Here the interface is going to replace {INTSURFACE} with the actual numeric INTSURFACE for the race of the current horse being processed. (If the actual race surface is dirt or 1: AND INTSURFACE = {INTSURFACE} will become AND INTSURFACE = 1 prior to execution.)

--Referring once again to the table of supported factor handles presented towards the bottom of this help doc - note that in addition to {INTSURFACE} the table also lists {SURF_TYPE} as a valid factor handle.

Suppose that instead of "AND INTSURFACE = {INTSURFACE}" (without the quotes) - the following line had been used in the Prob Expression:
	AND INTSURFACE = {SURF_TYPE}
Had that been the case the interface would replace {SURF_TYPE} with a numeric range that matches the surface type for the race of the current horse being processed.

Here's how that works:
  • If INTSURFACE for the current race is <= 3:
    Surface type for the current race is recognized as Dirt:
    AND INTSURFACE = {SURF_TYPE} becomes AND INTSURFACE <= 3 prior to execution.

  • If INTSURFACE for the current race is >= 4:
    Surface type for the current race is recognized as Turf:
    AND INTSURFACE = {SURF_TYPE} becomes AND INTSURFACE >= 4 prior to execution.


Moving on, the next line is:
	AND RAILPOSITION = {RAILPOSITION}
Here the interface is going to replace {RAILPOSITION} with the actual RailPositon for the current horse being processed. (If the current horse being processed actually drew the 2 hole in the gate, AND RAILPOSITION = {RAILPOSITION} will become AND RAILPOSITION = 2 prior to execution.)

Before moving on to the next line - let's take a more in depth look into "RAILPOSITION = {RAILPOSITION}" (without the quotes.)

RAILPOSITION and {RAILPOSITION} from the above line each have two distinct and entirely different meanings.

The first mention of RAILPOSITION refers to the data field name (the RailPosition field) in the StarterHistory table.

The second mention of {RAILPOSITION} refers to the factor values read from the table for each horse while the Prob Expression is being executed. (In this case those factor values are RailPosition or position from that rail out in the starting gate.)

This has implications when you change your SQL Factor Setup.

Suppose for the sake of argument that the RailPosition based Prob Expression we are talking about here is assigned to Slot #F18 in your SQL Factor Setup.

Further suppose you wanted to redo your SQL Factor Setup and move the RailPosition based Prob Expression we are talking about here from Slot #F18 to a different Slot Number in your SQL Factor Setup.

In this case you would be able to do that - and you wouldn't have to edit the Prob Expression afterwards - because an exact match exists between each factor name wrapped inside of curly brackets in the Prob Expression and the correspoding data field names in the StarterHistory table - as listed in The StarterHistory Table Schema.

Let's take a closer look:
  • {TRACK}: The Table Schema shows an actual TRACK field.
  • {DIST}: The Table Schema shows an actual DIST field.
  • {INTSURFACE}: The Table Schema shows an actual INSTSURFACE field.
  • {RAILPOSITION}: The Table Schema shows an actual RAILPOSITION field.
  • {TODAY}: {TODAY} always refers to the [DATE] field and the Table Schema shows an actual [DATE] field.

But what if instead of RailPosition the Prob Expression was based on EarlyConsensus?

What if instead of "AND RAILPOSITION = {RAILPOSITION}" (without the quotes) the Prob Expression contained the following line?

"AND RANKF18 = {RANKEARLYCONSENSUS}" (again without the quotes.)

If that were the case - if you decided to change your SQL Factor Setup and move EarlyConsensus from Slot #F18 to Slot #F19:

You would be "breaking" the Prob Expression.

How and why would you be "breaking" the Prob Expression by moving EarlyConsensus from Slot #F18 to #F19?

• Before you move EarlyConsensus from Slot #F18 to #F19: Exact matches exist between all of the factor names in the expression text wrapped inside of curly brackets in the Prob Expression and the corresponding data field names in the StarterHistory table - as listed in The StarterHistory Table Schema.

• But as soon as you move EarlyConsensus from Slot #F18 to #F19 you create a conflict that "breaks" the Prob Expression.

• The conflict occurs because RANKF18 is still referenced to {RANKEARLYCONSENSUS} in the expression text - but EarlyConsensus is no longer assigned to Slot #F18.

So how do you fix this?

After moving EarlyConsensus from Slot #F18 to Slot #F19:

Edit the Prob Expression text afterwards. (You need to make sure no conflicts exist between the expression text in your Prob Expressions and your SQL Factor Setup.)

• In this case change "AND RANKF18 = {RANKEARLYCONSENSUS}" (without the quotes) to:

"AND RANKF19 = {RANKEARLYCONSENSUS}" (again without the quotes.)

That's it!

Important: Always review your Prob Expressions - and edit them accordingly - after changing your SQL Factor Setup.


Moving on to the next line we have:
	AND [DATE] < {TODAY}
Note that TODAY is not a valid table field name. (You won't find TODAY listed as a field name in the table schema for the StarterHistory table.)

The word TODAY wrapped inside of curly brackets is actually a special command that allows you to tell the interface to replace {TODAY} with the correct sql syntax for today's race date for the current horse being processed.

If the actual race date is 05-12-2016, the interface will transform AND [DATE] < {TODAY} into AND [DATE] < #05-12-2016# prior to execution.

FYI, using AND [DATE] < {TODAY} in this manner insures that scoring of Prob Expression query results executed during a Calc Races on the original race date will be exactly the same as those generated when executing that same Prob Expression in the Data Window at some later date when your StarterHistory table contains starters from race dates added after the initial CalC Races.

Important: Using AND [DATE] < {TODAY} in this manner (with a less than sign) insures that Prob Expression query results are always BEFORE the original race date. (Make sure you read the section beneath the "Avoiding After the Fact Query Results" headline presented later on in this help doc.)




The UPR Tools Prob Expressions Tool
The Prob Expressions Tool is an interface in UPR Tools that will enable you to create, save, and edit Prob Expressions.

To launch the Prob Expression Tool:
  1. On the Main Module click the System Settings button.

  2. On the System Settings Interface click the UPR Tools button.

  3. On the UPR Tools Menu click the Prob Expressions button.


Screenshot:

Prob Expressions Tool

Note: The above screenshot shows the UPR Tools Prob Expressions Tool after a Prob Expression named _EARLY-TK-SURF-DIST has been selected from the available Prob Expressions Drop Down.



BASIC OPERATING INSTRUCTIONS for creating a new Prob Expression:
  1. Key valid sql for your Prob Expression into the SQL (or main) textbox.
    Hint: Use the Assist button to get started.

  2. NAME your Prob Expression by keying a unique name into the Name textbox.
    Hint: See the section about Naming your Prob Expressions (below.)

  3. SAVE your work by clicking the Save button.

That's it!



BASIC OPERATING INSTRUCTIONS for editing an existing Prob Expression:
  1. Select the name of the Prob Expression you want to edit from the Available Prob Expressions drop down.

  2. Key any edits you want to make to the Prob Expression's sql into the SQL (or main) textbox.
    Hint: Read the explanation for valid Prob Expression syntax (above) in this help doc.

  3. SAVE your work by clicking the Save button.

That's it!



BASIC OPERATING INSTRUCTIONS for cloning a Prob Expression:
  1. Open the Available Prob Expressions drop down and select the name of the Prob Expression you want to clone.

  2. Key any edits you want to make to the Prob Expression's sql into the SQL (or main) textbox.
    Hint: Read the explanation for valid Prob Expression syntax (above) in this help doc.

  3. NAME your (new) Prob Expression by keying a (new) unique name into the Name textbox.
    Hint: See the section about Naming your Prob Expressions (below.)

  4. SAVE your work by clicking the Save button.

That's it!



BASIC OPERATING INSTRUCTIONS for deleting a Prob Expression:
  1. Open the Available Prob Expressions drop down and select the name of the Prob Expression you want to delete.

  2. Double check the NAME of the Prob Expression you are about to delete by looking at the unique name into the Name textbox.

  3. Click the Delete button and answer Yes at the prompt.

That's it!



Naming your Prob Expressions
Once created - Prob Expressions behave just like any other JCapper factor. The names of your Prob Expressions will display in the factors drop downs of the various screens in UPR Tools. Prob Expression names are also displayed on the available factors list on Screen One of the SQL Factor Setup Wizard.

I spent some time working with Prob Expressions before publishing them as part of a JCapper program update. During that time I came up with a simple naming convention that resulted in my Prob Expression names displaying grouped together at the very top of the various screens throughout JCapper wherever factor names are displayed.

REQUIREMENT: The very first character of each Prob Expression name must be an underscore character.

(That's underscore as in "_" without the quotes.)

This is not optional. This is a requirement that must be met before the number crunching algorithms in JCapper can handle Prob Expressions properly.

Example: Suppose you've created a Prob Expression (using Behavior 23) based on DRIVETYPE and that you want to name it DType23.

You can't. If you try to save it as DType23: Logic behind the Save button in the Prob Expressions Tool will prevent you from doing that.

But if you instead name it _DType23: When you click the the Save button in the Prob Expressions Tool you will find that it saves just fine.

--Note: If you are using third party database tools to manage your own outside of JCapper databases and/or spreadsheets:

Be aware that Prob Expression names are stored in the ExpressionName field of the ProbExpressions table in the c:\2004\JCapper.mdb file. Also be aware that containerless Prob Expression names are stored in the FactorName field of the Factors table in the c:\JCapper\Exe\JCapper2.mdb file - and that if you use an outside of JCapper tool to edit those fields and remove the leading underscore character from a Prob Expression name: You will break JCapper's ability to use that Prob Expression during a Calc Races and/or Build Database routine. (If you break a Prob Expression in that manner you will cause it to return 0's for every horse.)


Testing your Prob Expressions
I strongly recommend you get in the habit of testing your Prob Expressions immediately after creating them.

One mistake you definitely want to avoid is creating a brand new Prob Expression - adding it to your SQL Factor Setup - and rebuilding databases from scratch immediately afterwards - only to discover hours later - that your Prob Expression is generating zeroes for every horse.

I've found that a relatively easy way to test a Prob Expression after creating it is to set it up to display on your SQL HTML Report - and then run a SQL Calc Races for a single race.

Another relatively easy way to test a Prob Expression after creating it is to run a SQL Calc Races for a single race - and then use the JCX File Exports Module to export that race out of the StartersToday table to a .csv file on your hard drive - and from there double click the .csv file to open it in Excel.

From there, take a few minutes to compose your new Prob Expression into an equivalent (standard) SQL Expression - and then run the equivalent (standard) SQL Expression through the Data Window - and compare the numbers that you see in the Data Window against the numbers for that race on your SQL HTML Report - or alternately - against the numbers from a .csv file while you have it open in Excel.

This is relatively easy to do with Behaviors 18, 19, 20 -- and Behaviors 23, 24, 25.

This is harder to do with the PwrWt and Log(n) Behaviors.

In general: If you are seeing numbers generated by a Prob Expression on your SQL HTML Report: You should expect to see the same numbers written to the data fields in your StarterHistory table for the slot number where the Prob Expression is assigned to. (The most likely cause of any differences should be scratches and changes on race day that you are not aware of when you click the Calc Races button.)

If you are not seeing numbers generated by a Prob Expression on your SQL HTML Report: You should NOT expect to see numbers written to the data fields in your StarterHistory table for the slot number where the Prob Expression is assigned to.

Important: Consider it a Best Practice to test (and resolve) Prob Expressions BEFORE you click the Build Database button.



Prob Expressions Tool - Elements found on the User Interface
This section contains a brief discussion for the elements found on the Prob Expression Tool's user interface.

The Available Prob Expressions Drop Down
This drop down is located in the upper left hand area of the screen. When the Prob Expressions Tool is first launched this drop down auto populates to display a list (sorted by name) of the existing (available) Prob Expressions. Selecting a Prob Expression from this drop down causes: 1. The sql for that Prob Expression to display in the SQL or main textbox. 2. The name of the selected Prob Expression to display in the Name textbox. 3. If the Auto Format Off checkbox was checked when the selected Prob Expression was last saved, the interface will auto ckeck the Auto Format Off checkbox when the selected Prob Expression is pulled up and displayed. (Otherwise, the Auto Format Off checkbox will be unchecked when the selected Prob Expression is pulled up and displayed.) Each time a Prob Expression is created or deleted the Available Prob Expressions Drop Down is repopulated.

The Name Textbox This textbox is located to the immediate right of the The Available Prob Expressions Drop Down. The Name textbox is where you key a (new) unique name when either creating a new Prob Expression or renaming/cloning an existing Prob Expression.

The SAVE button
Click this button to Save your work. Clicking the Save button causes the interface to test the sql expression displayed in the SQL or main textbox for valid syntax. If the sql displayed in the SQL or main textbox passes validation the interface will save it to the ProbExpression table. If the sql displayed in the SQL or main textbox fails validation testing the interface will display a message with a brief explanation why the sql is invalid and could not be saved to the table.

Hint: If you key changes into the SQL or main Textbox and exit the module without clicking the Save button those changes will be lost. (Always click the Save button before exiting the module whenever you want to keep your changes.)

The Delete button
Click this button to Delete the currently displayed Prob Expression. When this button is clicked after a Prob Expression has been selected from the Available Prob Expressions drop down: The interface will prompt the user (Yes or No) whether or not to continue with the Delete operation. Clicking Yes at the prompt results in the current Prob Expression being deleted. Clicking No at the prompt aborts the Delete operation.

The Assist button
Click this button to paste a sql template (that can be edited) into the SQL or main textbox when creating a new Prob Expression.

The SQL or main Textbox This is the large textbox located in the center area of the screen. This textbox is where you key (valid) sql for your Prob Expression. It is also where valid sql is displayed when an existing Prob Expression is selected from the Available Prob Expressions drop down.

The Clear button
Click this button to clear the SQL and Name textboxes. Hint: Don't panic if you accidentally click the Clear button while a Prob Expression is displayed. To redisplay the Prob Expression you were working on (in its last saved state) simply reselect it from the Available Prob Expressions drop down.

The Auto Format Off checkbox
The Auto Format Off checkbox in the Prob Expressions Tool behaves in the exact same manner as the Auto Format Off checkboxes found in both the Data Window and the UDM Wizard. If the box is checked when the Save button is clicked: The sql expression is saved with Auto Formatting turned Off. If the box is not checked when the Save button is clicked: The sql expression is saved with Auto Formatting turned on.

What is Auto Formatting?

Auto Formatting is a feature (in JCapper) designed to make sql expressions easier to read to the human eye.

When Auto Formatting is left On: a line break is inserted into the sql expression just prior to each instance of the word AND. The result is that a sql expression having many factor constraints is displayed in a manner where each factor constraint is given its own line. While this does make sql expressions easier to read - there are times when this "feature" interferes with query results.

Consider the case where Auto Formatting is left On for a sql expression referencing names for riders, trainers, sires, or damssires. If a name referenced by the expression contains the characters "AND" -- the interface will insert a line break into the name itself just prior to "AND" -- making it impossible to get accurate query results for that name.

I can illustrate this using a mythical trainer name in a sql expression. This is what such a sql expression looks like with Auto Formatting turned Off:
AND INSTR('MORELAND THOMAS W', TRAINER) > 0

This is what the same sql expression looks like with Auto Formatting left on:
AND INSTR('INSTR('MOREL AND THOMAS W', TRAINER) > 0', TRAINER) > 0

As you can see, insertion of the line break into the sql expression makes it impossible for the interface to get accurate query results for MORELAND THOMAS W (or any other name containg the characters "AND.")

Follow these three rules for Auto Formatting:
  1. If a sql expression is not referencing names and it contains many factor constraints - and you want to make it easier to read - feel free to leave auto formatting on (uncheck the box) before saving it.

  2. If a sql expression references names - ALWAYS turn Auto Formatting Off (check the box) before saving it.

  3. If you don't care what your sql expressions look like in the Data Window - but you DO care that they return accurate results! -- ALWAYS turn Auto Formatting Off (check the box) before saving a sql expression. (That way you never have to worry about whether or not the interface is going to insert line breaks into your sql expressions.)

Note: These rules apply equally to both Prob Expressions and SQL UDMs.




Other Considerations
Prob Expressions are sql expressions that get executed during SQL Calc Races and SQL Build Database routines.

They can be a great way to create really useful user defined factors.

But you need to use them judisciously.

Create too many of them - and they can slow execution speed of SQL Calc Races and SQL Build Database routines to a crawl. (Especially on older/slower machines.)

Create Prob Expressions that are too wide or poorly formed (I'll touch on that in a minute) and you WILL slow execution speed of SQL Calc Races and SQL Build Database routines to a crawl. (No matter how fast your machine happens to be.)

Consider the following sql expression:
SELECT * FROM STARTERHISTORY
This is perfectly valid sql syntax for a Prob Expression.

So yes, you COULD use it in a Prob Expression. But why would you want to? I wouldn't. First of all it doesn't do anything other than tell you the win or win-place percentages for all starters in your table. That's not particulary useful info to have. (So why waste time and resources generating it during a Calc Races?)

Suppose for the sake of argument - you did use the above sql expression (as is) to drive a Prob Expression. It does not contain any factor constraints designed to narrow down the number of starters returned. Therefore it takes a long time to execute - especially if your table contains a sizable number of starters.

Not only that - because it's a Prob Expression - it's going to execute once for every starter in a Calc Races routine. Consider a busy Saturday in the middle of July where you have 30 or so track codes loaded into the program. I'm guessing the last thing you want when you click the Calc Races button is an expression like that executing for every single horse. (You'd literally be there all day waiting for your Calc Races to finish.)

Let's move on. Consider the following sql expression:
SELECT * FROM STARTERHISTORY WHERE STARTSLIFETIME > 0 AND RANKF08 <= 10 AND GAPF15 >= -150 AND RANKF19 <= 3 AND RANKJPR = 1

Of course, this too is perfectly valid sql syntax for a Prob Expression.

But it is hardly optimal.

First, it will take (a lot) longer to execute than what you would (normally) want in a Prob Expression - because there is only minimal filtering of results. The constraint "AND RANKJPR = 1" (without the quotes) will make the expression return JPR rank = 1 horses only. But the expression is completely lacking when it comes to the type of constraints I normally include in a Prob Expression.

For example, the expression doesn't contain any constraints for track, surface, distance, etc. - nor does it make use of the TOP command in conjunction with an ORDER BY [DATE] DESC clause - which I normally would include to limit the query results to the most recent X number of starters. Because of this, the query (as is) is telling the database driver to parse the entire starterhistory table to get its JPR rank = 1 results. That's a lengthy process time-wise - and something best avoided in a Prob Expression.

Second, the expression (as is) will take much longer to execute than necessary - because it is too WIDE at the top.

Most database drivers execute sql expressions one line at a time, from top to bottom, each line in succession. The very first line in the above expression gets executed first and casts a very wide net with "WHERE STARTSLIFETIME > 0" which returns every horse in the table that is not a first time starter.

The second line in the expression gets executed next - and also casts a fairly wide net with "AND RANKF08 <= 10" (as does the third line with gap for HDW pedigree rating within 150 points of the top horse in each race.)

Query execution time for the above expression can be improved (dramatically) by restructuring it.

Best practice (in my opinion) involves always putting the part of the expression that returns the fewest number of starters at the very top of a Prob Expression or SQL UDM.

To make the above SQL expression as narrow at the top as possible (to optimize query speed) I would rewrite it as follows:
SELECT * FROM STARTERHISTORY WHERE RANKJPR = 1 AND RANKF19 <= 3 AND GAPF15 >= -150 AND RANKF08 <= 10 STARTSLIFETIME > 0

Point made. (Hopefully.)



The SQL CHRON BUILD Setting on the User Sys Defs Screen
Skip this section (for now) if ALL of your Prob Expressions are track code specific.

READ this section (and use the SQL Chron Build Setting) if ANY of your Prob Expressions are designed to span multiple track codes.

The User Sys Defs Screen in Build 198 ver 2.0 (and later) includes a SQL Chron Build checkbox.

When this setting is checked or ON: SQL Build Database routines are run in date-track-race order.

When this setting is unchecked or OFF: SQL Build Database routines will continue to be run in data file name order (or track-date-race order.)

This can have implications for Prob Expressions that span multiple track codes.

The order in which databases are built (date-track-race vs. track-date-race) has zero impact on numbers generated by (properly formed) Prob Expressions spanning multiple track codes when you click the Calc Races button on race day.

However, the order in which databases are built (date-track-race vs. track-date-race) MATTERS when numbers generated by Prob Expressions spanning multiple track codes are written to your StarterHistory table.

Put another way: The order in which databases are built (date-track-race vs. track-date-race) affects the numbers generated by Prob Expressions spanning multiple track codes that you see in the Data Window.

RECOMMENDED BEST PRACTICE for Chron Builds:
  1. If you have Prob Expressions spanning multiple track codes:

    • Check the SQL Chron Build box on the User Sys Defs Screen for Mode3 and Mode 4 Build Database Routines.

    • You can leave the SQL Chron Build box on the User Sys Defs Screen unchecked for daily Mode5 Build Database Routines.

  2. If you don't have Prob Expressions spanning multiple track codes:

    • You can leave the SQL Chron Build box on the User Sys Defs Screen unchecked for all Build Database Routines.




POPULATING THE STARTERHISTORY TABLE

IMPORTANT: AVOID using Data Window Exports to populate the StarterHistory Table after assigning Containerless Prob Expressions to Slot Numbers in your SQL Factor Setup.

As of this writing (October 2016) the Data Window is not programmed to execute Prob Expressions during Data Window Exports - and therefore cannot, will not, and DOES NOT populate data fields in the StarterHistory table data where Containerless Prob Expressions have been assigned.

Currently -- There are two ways to populate data fields in the StarterHistory table data where Containerless Prob Expressions have been assigned:

  1. Running Build Database routines in Mode 3, Mode4, or Mode5.


  2. Running JCapper2 Import routines to import StarterHistory Table, SQL Mode F-Factor Setup, and SQL Mode Customizable HTML Report Layout data out of a source JCapper2.mdb file into a target JCapper2.mdb file.

    Note: If the StarterHistory table in your source JCapper2.mdb contains data fields where Containerless Prob Expressions have been assigned - those data fields must have been populated by running Build Database routines in Mode 3, Mode4, or Mode5.

I hope I have managed to explain that in a way that makes sense,

-jp

.



AVOIDING AFTER THE FACT QUERY RESULTS
One thing I need to make clear in this help doc: You need to be careful with the way you use {TODAY} and/or {RACEDATE} in your Prob Expressions.

Be 1000% sure that you use a less than sign... example: AND [DATE] < {TODAY}

NEVER (repeat never) use an equal to sign or a greater than sign with {TODAY} and/or {RACEDATE} in your Prob Expressions.

If you (accidentally) do use an equal to sign or a greater than sign with {TODAY} and/or {RACEDATE} in a Prob Expression:

The query results returned by a Prob Expression where you've (accidentally) done this are going to be after results are known.

The result can be amazingly high win percentages and roi in the Data Window.... which of course will not reflect reality of what you can expect during live play.


Example of correct Prob Expression syntax:
		AND [DATE] < {TODAY}

Examples of bad/incorrect Prob Expression syntax:
		AND [DATE] <= {TODAY}

		AND [DATE] = {TODAY}

		AND [DATE] >= {TODAY}

		AND [DATE] > {TODAY}


I actually made this mistake myself very early on... ughh.

I'm pointing it out here because I don't want you to make the same mistake.




SUPPORTED BEHAVIORS FOR PROB EXPRESSIONS
The mechanisim in JCapper that controls what the interface does with Prob Expression query results is called a Behavior.

Current list of supported Behaviors for Prob Expressions:
  • 18 WinPct
  • 19 WinRoi
  • 20 WinRoiOddsCapped
  • 21 WinPwrWt
  • 22 WinPwrWtOddsCapped

  • 23 WinPlacePct
  • 24 WinPlaceRoi
  • 25 WinPlaceRoiOddsCapped
  • 26 WinPlacePwrWt
  • 27 WinPlacePwrWtOddsCapped

  • 30 Log(WinPct
  • 31 Log(WinRoi)
  • 32 Log(WinRoiOddsCapped)
  • 33 Log(WinPwrWt)
  • 34 Log(WinPwrWtOddsCapped)

  • 35 Log(WinPlacePct)
  • 36 Log(WinPlaceRoi)
  • 37 Log(WinPlaceRoiOddsCapped)
  • 38 Log(WinPlacePwrWt)
  • 39 Log(WinPlacePwrWtOddsCapped)


Entering Behaviors for Prob Expressions
Behaviors for Prob Expressions are not entered through the Prob Expressions Tool.

The two areas in JCapper where you enter Behaviors for Prob Expressions are:
  1. On Screen One of the SQL Factor Setup Wizard - when assigning a Prob Expression its own F-Slot number in your SQL Factor Setup. (A containerless Prob Expression.)

  2. In UPR Tools - when adding entries for a Prob Expression to a UserFactor or UPR GroupName. (A Prob Expression contained in a UserFactor or UPR GroupName.)


This info model gives you some flexibility.

Suppose you use the Prob Expressions Tool to create a single Prob Expression designed to score query results for the most recent 80 starters for the rider-trainer combo of every horse in a Calc Races and/or Build Database routine (no matter what that combo happens to be) at today's track code.

Such a Prob Expression might look like this:
SELECT TOP 80 * FROM STARTERHISTORY WHERE TRACK = {TRACK} AND RIDER = {RIDER} AND TRAINER = {TRAINER} AND [DATE] < {TODAY} ORDER BY [DATE] DESC


After the (above or any) Prob Expression has been created in the Prob Expressions Tool - it can be used multiple times - and each separate instance of that Prob Expression can have its own (unique) Behavior.

Examples:
  1. The above Prob Expression could be assigned to slot number F01 on Screen One of the SQL Factor Setup Wizard using Behavior 23 - causing the interface to score the returned query results for each horse as WIN-PLACE PCT.

    You could also add slot number F01 to your customizable SQL HTML Report Layout - with the end result being that WIN-PLACE PCT for the RIDER-TRAINER of each horse now displays on your SQL HTML Report.

  2. You could select the above Prob Expression in the Prob Expressions Tool and (using it as a template) save it a second time - using a different (but similar) name.

    From there you could assign this second (cloned) instance of the same Prob Expression to slot number F02 on Screen One of the SQL Factor Setup Wizard using Behavior 19 - causing the interface to score the returned query results for this instance of the Prob Expression as WIN ROI.

    You could also add slot number F02 to your customizable SQL HTML Report Layout - with the end result being that WIN ROI for the RIDER-TRAINER of each horse now displays on your SQL HTML Report.

  3. You could bring up UPR Tools and from there - add either instance of the above Prob Expression to a UserFactor or UPR GroupName. You can do this for as many ImpactValues table entries as you see fit. And so long as you use a valid Prob Expression Behavior - each ImpactValues table entry can have a different Behavior.

As you can see, the info model of not entering Behaviors for Prob Expressions in the Prob Expressions Tool gives you some flexibility.




Implementing a Containerless Prob Expression
In this section, I'm going to walk you through the steps to implement the example rider-trainer Prob Expression presented in the previous section (above) as a containerless Prob Expression.

BASIC OPERATING INSTRUCTIONS:
  1. Launch the Prob Expressions Tool, key the expression, give it a name, click the Save button, and X-out of the Prob Expressions Tool.

    Screenshot:

    Prob Expressions Tool

    Note: The above screenshot shows the UPR Tools Prob Expressions Tool after a Prob Expression named _RDR-TNR-80 has been keyed and saved.

  2. Launch the SQL Factor Setup Wizard. On Screen One of the SQL Factor Setup Wizard: Add the desired Prob Expression to an open F-Slot Number.

  3. Then, single click the (just assigned) Prob Expression to highlight it, and with the Prob Expression highlighted, click the Behavior button to launch the Prob Expressions Behavior Tool. From there, select a valid Behavior (18-39) and click the Apply button to apply the selected behavior and save your work. Then X-out of the Prob Expressions Behavior Tool.

    Screenshot:

    Prob Expression Assigned to F-Slot Number F04

    Note: The above screenshot shows Screen One of the SQL Factor Setup Wizard after a Prob Expression named _RDR-TNR-80 (highlighted in blue) was assigned to Slot Number F04 with Behavior 23 (WIN-PLACE PCT) applied.

That's it!

At this point, the Prob Expression has been assigned to an F-Slot Number in the SQL Factor Setup. It is now a containerless Prob Expression - and you can now use it in your SQL UDMs. (Although you'll first need to rebuild databases to poulate your StarterHistory table before you'll be able to see data for the newly assigned Prob Expression in the SQL Data Window.)

Optionally, from here, you could also click the Next button and use screens two, three, and four of the SQL Factor Setup Wizard to add the F-Slot Number where your Prob Expression was (just) assigned to your customizable SQL HTML Report Layout.

Important Note: Whenever you display Prob Expressions on your customizable SQL HTML Report - keep in mind that numeric value for Prob Expressions is often a number between 0 and 1.

Because of this, on Screen Four of the SQL Factor Setup Wizard: You'll likely want to set the display to at least 2 decimal places for any slot number where you are displaying a Prob Expression on your SQL HTML Report Layout.

Screenshot:
Displaying a Prob Expression on the SQL HTML Report

Note: The above screenshot shows Screen Four of the SQL Factor Setup Wizard after the F-Slot number for a Prob Expression has been selected. Note the center area of the screen (circled ever so crudely using a red mouse pen) where I have set the display to 2 decimal places.




Implementing a Prob Expression in a UserFactor or UPR GroupName
In this section, I'm going to walk you through the steps to implement a Prob Expression in a UserFactor or UPR GroupName.

BASIC OPERATING INSTRUCTIONS - UPR TOOLS CLASSIC VIEW:
  1. Launch the UPR Tools Classic View and click the New button to initiate the sequence for a new entry.

  2. Key a valid GroupName into the GroupName field (or alternately select an existing GroupName from the GroupName drop down.)

  3. Check the Active box.

  4. Select an existing Prob Expression name from the factors drop down.

    Important: See the Notes and Comments section (below) for info on how to avoid 'aliasing' between Prob Expressions you are using in a UPR GroupName and Containerless Prob Expressions you are using in your SQL Factor Setup.

  5. Click inside the Behavior field. This will cause the Behavior Tool to appear. Open up the Behaviors drop down and select a valid Prob Expression Behavior (18-39) and then click the Apply button.

    --Notes: If you want the interface to transform the Prob Expression query results into simple WIN PCT use Behavior 18. If you want WIN ROI use Behavior 19. A complete list of current supported Behaviors for Prob Expressions is presented above. (Use the Behavior for the query result transformation that you want.)

    --If you open up the drop down in the Behaviors Tool and scroll through the behaviors themselves - you will notice they are broken out into sections - one section for each type of model supported in UPR Tools. You will also notice that Prob Expressions has its own section beginning with Behavior 18 and ending with Behavior 39. Behaviors 18-39 are the only Behaviors you should be using for Prob Expressions in UPR Tools Classic View.

    --Important: You should avoid using Behaviors 18-39 with standard (non Prob Expression) JCapper factors in UPR Tools Classic View (as the interface is not programmed to handle that.)

  6. Key a value into the Weight field. (Use 1.00 if the Prob Expression is the only factor in the GroupName. Otherwise, scale your weights to refelct relative importance among the factors you are using in the GroupName.)

  7. Select a valid Surf-Dist option from the drop down.

  8. Key entries for min and max Field Size or Dist (optional.)

  9. Key any entries for Track, AvoidTrack, or ClassDescriptor (optional.)

  10. Key entries for High Score and Low Score/Default Impact. (Using 9999 for High Score and -9999 for Low Score/Default Impact works about 99.9% of the time.)

  11. Click the Save button.

That's it!

Screenshot:

A Prob Expression saved in UPR Tools Classic View

Note: The above screenshot shows UPR Tools Classic View after saving the entries to add a Prob Expression named "_DAMSSIRE100_b23" (without the quotes) to a UPR GroupName named "_BX7" (again without the quotes.)

Note that I am using a weight of 1.00 because the Prob Expression is the only factor in the GroupName.

Also note that I am using Behavior 23 (simple WIN-PLACE PCT.)


At this point, the Prob Expression has now been added to the GroupName. (Although I'd first need to set "_BX7" as my UPR GroupName in the User System Definitions screen - and from there rebuild databases to poulate my StarterHistory table before being able to see data for my new GroupName in the SQL Data Window.)



--Notes and Comments:

--How to avoid 'aliasing' between Prob Expressions:

It is possible to use a Prob Expression in more than one place in JCapper.

For example, you can use a Prob Expression (as a containerless Prob Expression) in your SQL Factor Setup.

And you can use the same Prob Expression (multiple times if you want) in you UserFactor and/or UPR GroupNames.

But you need to be aware of a few things before you do that:

• If you use the same Behavior for each instance of the same Prob Expression: You don't have to worry about 'aliasing.'

• But, if you use one Behavior for a Prob Expression in your SQL Factor Setup -- and use a different Behavior for the same Prob Expression in a UserFactor or UPR GroupName:

Then you DO have to worry about 'aliasing.'

Q. What is 'aliasing?'

A. For Prob Expressions: 'Aliasing' occurs when the same Prob Expression has different numeric values in multiple places where you are using it in JCapper.

Let's illustrate this using an example.

Suppose you are using the Prob Expression named "_DAMSSIRE100_b23" (without the quotes) from the above screenshot.

Let's further suppose you are using this Prob Expression in your SQL Factor Setup where it is assigned Behavior 23 (Win-Place Pct.) When you click the Calc Races button, the interface executes the Prob Expression and scores the query results for each horse using win-place pct. At this point in the Calc Races event chain the factor name "_DAMSSIRE100_b23" (without the quotes) will have a numeric value equal to the Win-Place Pct for each horse. (So far so good.)

But what if you are using the same Prob Expression a second time with Behavior 19 (Win Roi) in a UPR GroupName?

Later on in the same Calc Races routine the interface is going to execute the same Prob Expression - but this time it is going to score the query results for each horse using Win Roi.

At this point in the Calc Races event chain the factor name "_DAMSSIRE100_b23" (without the quotes) now has a numeric value equal to the Win Roi for each horse.

Not so good because now you've intoduced 'aliasing' -- and the numeric value for the factor named "_DAMSSIRE100_b23" (without the quotes) is different from what you were trying to achieve when you created you SQL Factor Setup.

Q. So how do you avoid 'aliasing?'

A. Easy. There are two ways. You can either:

1. Use the same Behavior throughout each time you use the same Prob Expression in JCapper.

--or--

2. Clone the Prob Expression (see the BASIC OPERATING INSTRUCTIONS for cloning a Prob Expression above) and use the Clone in your UserFactor or UPR Groupnames instead of the original.

--Hint: This works because the clone has a different factor name than the original (which is in your sql factor setup) and the clone isn't in your SQL Factor Setup.





SUPPORTED FACTOR HANDLES FOR PROB EXPRESSIONS:
    --Race level factors:
  • {TODAY}
  • {RACEDATE}
  • {TRACK}
  • {CLASSDESCRIPTOR}
  • {INTSURFACE}
  • {SURF_TYPE}
  • {DIST}
  • {DIST_TYPE}

  • {DISTANCE}
  • {FIELDSIZE}
  • {FTSCOUNT}
  • {PACEINDEX}
  • {PACEPRESSURE}
  • {ESPRACESHAPE}
  • {RACEVOLATILITY}

  • --Horse level factors:
  • {POS2CALL}
  • {POSFINCALL}
  • {DRIVETYPE}
  • {CANTRUNXFORMATTRIB}
  • {STAGEREADINESS}

  • {RAILPOSITION}
  • {RUNSTYLE}
  • {QSPEEDPOINTS}

  • {RANKEARLYCONSENSUS}
  • {RANKLATECONSENSUS}
  • {RANKCLASSCONSENSUS}
  • {RANKFIGCONSENSUS}
  • {RANKFORMCONSENSUS}

  • {RANKJPR}
  • {RANKJPRCLASS}
  • {RANKQRATING}
  • {RANKALCHEMY}
  • {RANKPRIME}
  • {RANKPSR}
  • {RANKMLINE}
  • {RANKPEDRATING}

  • {RANKCPACE}
  • {RANKV1}
  • {RANKCOMPOUNDAP}
  • {RANKCOMPOUNDSP}
  • {RANKCOMPOUNDLATE}
  • {RANKCOMPOUNDPACEFIT}

  • {RANKCFA}
  • {RANKOPTIMIZATION}

  • {RANKUSERFACTOR1}
  • {RANKUSERFACTOR2}
  • {RANKUSERFACTOR3}
  • {RANKUSERFACTOR4}
  • {RANKUSERFACTOR5}
  • {RANKUSERFACTOR6}
  • {RANKUSERFACTOR7}
  • {RANKUSERFACTOR8}

  • --Name based factors:
  • {RIDER}
  • {TRAINER}
  • {OWNER}

  • {SIRE}
  • {SIRESSIRE}
  • {DAM}
  • {DAMSSIRE}







-jp

.

Copyright 2008 JCapper Software              http://www.JCapper.com