Substitute in SQL - SubstituteFor attribute

This attribute defines a piece of text which is placed in the SQL and which will be replaced by text entered by the user when the report is executed. Any text can be used, but using something like {0} will be unique.

Consider the following SQL

Select {0} from sample

{0} is the text which will be substituted so with the Condition as follows:-

 <Condition field =""  operator="equal" type="Text" SubstituteFor="{0}" name="Field to be returned"  / >

The user will be requested to enter the field name (say sample_Key) and this will be substituted for the {0}.

The actual query executed would be

Select sample_key from sample

This is a useful way of passing a parameter to a User Defined Function.  

A restriction in the use of this is that it only works with the final SELECT statement in the report so it can not be used to populate temporary tables. This is explained in more detail in Using User Defined functions. Note that if you wish to use this within several <Where> tags then you must use a different piece of text to be replaced (eg. {0}, {1} in each one.


Created by Atop CHM to web converter,© 2009 all right reserved.