JCapper Message Board

JCapper 101
-- SQL UDMs - a Primer

Home	Register Log In

SQL UDMs - a Primer

jeff
12/2/2023
10:24:46 AM

From the email inbox:

--quote:

"Good morning Jeff. I am looking for info on writing SQL UDMS. Writing days back, gap rankings, certain dates, etc, etc. I have looked and came up empty. I thought I had some guide before. Thank you"

--end quote

From the JCapper.com site, click the JCapper 101 Audio/Video link.

From there, click the Operating JCapper in SQL Mode link.

• The FIRST THING you need to know are the Column Names and Data Types in the tables where the data is stored.

• Start with the Table Schema Doc which lists the column names and data types of a JCapper StarterHistory Table.

Link here:
Table Schema Doc

• FYI, you can also generate reports on the fly showing column names and data types for any of the tables in any JCapper .mdb file using the SQL Expression Tool in the JCX File Exports Module.

Link to the JCX File Exports Module Help Doc here:
http://www.jcapper.com/helpDocs/JCXFileExportModule.html

Direct Link to that functionality inside of the Help Doc 4. BASIC OPERATING INSTRUCTIONS for using the SQL Expression Tool in the JCX File Exports Module to expose tables, data field names, and data field attributes in a connected to .mdb File.

• The SECOND THING you need to know is how to compose a SQL Expression.

• Start with the JCapper SQL Cheat Sheet.

Link, here:
The JCapper SQL Cheat Sheet

• Example SQL Expression for a simple UDM formatted for storage in the UDM Wizard:

SELECT * FROM STARTERHISTORY
WHERE RANKUPR=1
AND RANKF22=1
AND RANKF09 <= 2
AND RANKF24 <= 5
WHERE TRACK = 'AQU'
AND INTSURFACE = 1

--Hint: Always check the Auto Format Off box in the UDM Wizard SQL Expression Tool before saving a SQL Expression in the UDM Wizard.

• The same UDM with date text and an order by clause added for execution in the Data Window:

SELECT * FROM STARTERHISTORY
WHERE RANKUPR=1
AND RANKF22=1
AND RANKF09 <= 2
AND RANKF24 <= 5
WHERE TRACK = 'AQU'
AND INTSURFACE = 1

AND [DATE] >= #11-14-2023#
AND [DATE] <= #11-30-2023#
ORDER BY [DATE], TRACK, RACE

--Hint: Date text and order by are useful in the Data Window. (But are best avoided when storing SQL UDMs in the UDM Wizard.)

That should get you started.

Let me know as you have questions.

-jp
.

JCapper Message Board

JCapper 101 -- SQL UDMs - a Primer

JCapper 101
-- SQL UDMs - a Primer