JCapper Message Board

JCapper 101
-- Combining SQL UDMs

Home	Register Log In

Combining SQL UDMs

jeff
8/30/2014
3:11:55 PM

From the email inbox...

--quote:

"Jeff, I have been using Jcapper silver playlist mode for about two years and I'm just now making the switch to sql mode. I like that sql udm definitions seem cleaner/easier than playlist udms because with sql the entire udm definition exists on a single screen.

My question is this:

I have A LOT of individual udms. Is there a way to combine them so that I end up with fewer udms or even a single udm? I think I can do this using the OR keyword. Is that correct? If so, can you provide a basic template for doing this? -RC"

--end quote

Easy enough...

Let's start with a sql expression in its simplest form:

SELECT * FROM STARTERHISTORY WHERE (X)

Note that in the above template, even though the expression is as simple as it gets, I am using parenthesis characters.

It's also implortant to note that in sql expressions, everything inside a set of parenthesis characters, even if it is hundreds of lines long, gets evaluated as if it were a single parameter.

Therefore, placement of parenthesis characters in sql expressions is critical.

In the above sql expression 'X' can represent either a single sql command or even hundreds of lines of sql commands.

Because 'X' is wrapped in parenthesis characters 'X' is evaluated as if it were a single line of sql.

Let's expand the above sql expression a bit...

What happens if we replace 'X' with individual sql commands? (Substitution.)

Instead of SELECT * FROM STARTERHISTORY WHERE (X) if we replace 'X' with '(A) OR (B) OR (C) OR (D)' our sql expression now looks like this:

SELECT * FROM STARTERHISTORY WHERE ((A)

OR (B)

OR (C)

OR (D))

The above sql expression can be used as a basic template to combine 4 multiple sql UDMs into a single sql UDM.

In the above sql expression (A) is a single sql UDM.

In the above sql expression (B) is a second single sql UDM.

In the above sql expression (C) is a third single sql UDM.

In the above sql expression (D) is a fourth single sql UDM.

Suppose the full sql expression making up your 'A' udm looks like this:

SELECT * FROM STARTERHISTORY WHERE RANKUPR=1 AND RANKF19=1 AND RANKMLINE >= 3

To integrate the 'A' udm into the template let's first bold the part of the udm text we need like this:

SELECT * FROM STARTERHISTORY WHERE RANKUPR=1 AND RANKF19=1 AND RANKMLINE >= 3

From there I would substitute the bolded text from the 'A' udm into the 'A' part of the template like this:

SELECT * FROM STARTERHISTORY WHERE ((RANKUPR=1 AND RANKF19=1 AND RANKMLINE >= 3)

OR (B)

OR (C)

OR (D))

From there, I would repeat for udms 'B', 'C', 'D', etc. while being careful to keep the template structure intact.

Assume for the sake of argument that the udm definition of your 'B' udm looks like this:

SELECT * FROM STARTERHISTORY WHERE RIDER = 'CASTELLANO JAVIER' AND INTSURFACE >= 4 AND INSTR('AQU-BEL-SAR', TRACK) > 0

Bolding the part of the 'B' udm text we need the 'B' udm looks like this:

SELECT * FROM STARTERHISTORY WHERE RIDER = 'CASTELLANO JAVIER' AND INTSURFACE >= 4 AND INSTR('AQU-BEL-SAR', TRACK) > 0

After pasting the bolded part of the 'B' udm text into the 'B' part of the template, the template now looks like this:

SELECT * FROM STARTERHISTORY WHERE ((RANKUPR=1 AND RANKF19=1 AND RANKMLINE >= 3)

OR (RIDER = 'CASTELLANO JAVIER' AND INTSURFACE >= 4 AND INSTR('AQU-BEL-SAR', TRACK) > 0)

OR (C)

OR (D))

When I am done I will have created a single UDM that fires whenever the conditionals found in 'A' OR 'B' OR 'C' OR 'D' are met...

Which is another way of saying that I took udms 'A', 'B', 'C', and 'D' and combined them into a single udm.

-jp

.

~Edited by: jeff on: 8/30/2014 at: 3:11:55 PM~

JCapper Message Board

JCapper 101 -- Combining SQL UDMs

JCapper 101
-- Combining SQL UDMs