Job Runner <If> comparison sometime gives strange results.
Hint Ref: 021109230003
Hint Date: 23/09/2011
Hint Details:
ISSUE
An XLM job contained something similar to the following (using :maxid as a parameter in the comparison) :
<SetVariable name="MaxpID" sql="select max(SomeThingOrOther) from customer"/>
<If x1="(:maxpid)" x2="40" comparison=">">
<IfYesDialog text="Some Customers have more than 40 SomeThingOrOthers. Do you want to quit?">
<Finish text="User Quit"/>
</IfYesDialog>
</If>
The user found that the Message appeared even when MaxID was less than 40.
Instead they tried the following (using {maxid} as a substitution in the comparison) :
<SetVariable name="MaxpID" sql="select max(SomeThingOrOther) from customer"/>
<If x1="{maxpid}" x2="40" comparison=">">
<IfYesDialog text="Some Customers have more than 40 SomeThingOrOthers. Do you want to quit?">
<Finish text="User Quit"/>
</IfYesDialog>
</If>
However, this also did not work reliably.
SOLUTION
Using the local <If> comparison, rather than the Database <IfSQL> is much more efficient and does not increase network traffic, however you must exercise care when using <If> as it is much more strict on its use than <IfSQL>.
The first attempt will fail as you cannot use Parameters here. Whilst the second attempt does use macro substitution, there are other issues which will stop the code from functioning as the user would like:
1). The data type of the comparison.
MaxID is clearly a numeric as that is what will be returned by the query. However, by default the <If> comparison will be a string based comparison, rather than a numeric one because it has not been told differently.
2). The variable name is not being used in the same case as it was defined (MaxID vs maxid). It is very important to ensure that the upper and lower case letters are checked to ensure that they are the same in ALL cases, otherwise in some situations such as this the system will consider the variable maxid to be a different variable to MaxID.
Therefore the XML code should actually look like this;
<SetVariable name="MaxpID" sql="select max(SomeThingOrOther) from customer"/>
<If x1="{MaxpID}" x2="40" comparison=">" type="n">
<IfYesDialog text="Some Customers have more than 40 SomeThingOrOthers. Do you want to quit?">
<Finish text="User Quit"/>
</IfYesDialog>
</If>
NOTE: If the variable MaxID's data type had not been set by the query. it MUST be set as an attribute of <SetVariable> to match the numeric type of the <If>, to work correctly. E.g. <SetVariable name="MaxpID" value="8" type="n"/>
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure, XML Script or SQL command. Responsibility resides solely with the user.
IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)