On instances before the Istanbul release, the 'Reference qual condition' field used for catalog variables was limited to a maximum length of 255 characters. If the translated query string exceeds this limit, a truncation occurs with no logged error or warning (KB0623315). This causes conditions to be stored corrupted and unusable in the system. If you are lucky enough to be at least be aware of this limitation, typically, you would know not not be able to add conditions over five or six rows in the wizard variable reference field in order to make it work safely.
You can reproduce this issue with the following steps:
- Navigate to Service Catalog > Catalog Variables > All Variables.
- Select a Reference type variable that uses a simple reference qualifier, i.e. the Question = "Select an Export Target".
- Within the variable form, click on the Type Specifications section tab.
- Add several conditions as shown below and save the record.
If you now use the variable and review the actual query being executed, you will notice that some bottom conditions have disappeared!
How irritating can that be?
The culprit table column on the dictionary is [question].[reference_qual_condition]. The Tables & Columns tool will help you identify it.
A limit of 255 characters is usually too short for this purpose. Other similar fields in the platform are usually 1000 or 4000 characters long. However, this field needs to be left OOB and the max length should not be altered, as it may cause issues in other areas of the platform.
Since Istanbul, the new reference qualifier condition field max length has been increased to 4000 characters.
Avoid query truncation on catalog variable reference qualifier conditions
If an upgrade to Istanbul is not yet possible, as a workaround you can set up an advanced reference qualifier instead of a simple one, and write a script to implement the query needed to return the wanted filtered values. The Reference qual field should contain the javascript call to your scripted function, ideally stored in a Script Include.
For further information on how to proceed with the implementation of your script, the available documentation pages below can be of help:
