Tip 1: Always provide a default value to a Boolean field (DTYPE_BOOL) in a business component,especially if it will be used in search spec to filter records.
If you don’t provide a default value (either by pre-default or post-default) to a boolean field then everywhere you have to take care of NULL and blank values in addition to ‘Y’ and ‘N’.
Let’s take an example: You want to retrieve all the records where the value of a particular flag is is ‘N’ then usually you provide following search spec
[field] = ‘N’
The search spec mentioned above will only retrieve records that have value as ‘N’ it will miss the records with value as NULL or blank. To retrieve all the records you have change your search spec to
[Field] = ‘N’ OR [Field] IS NULL OR [Field] = ‘’
A boolean fields until and unless it is specifically set to ‘N’ has value as NULL in DB. Although on UI you will see N and NULL in same way (an unchecked record in an applet). From UI if you set a flag as ‘Y’ and then uncheck it only then Siebel sets it value as ‘N’ otherwise it is NULL.
What is the problem in this? It is only increasing the search spec length!
Unfortunately the side effect is not just the increase in the search spec length it impacts database performance. As soon as you introduce “IS NULL” or “IS NOT NULL” clause on a column the query plan changes and even if you have index defined on the field it might not be used. So your query performance can degrade.
In addition if you have multiple flags in your search spec then you might end up reaching the search spec length limit (believe me I have personally experienced that).
Tip 2: Take care of history data.
Providing Pre-Default and Post-Default value will take care of new records but you will have to update the old records either through EIM or SQL query. You will have to set the value as N where ever it NULL. Below is an example of SQL Query that you can use:
SET NEW_FLAG_COLUMN = ‘N’
WHERE NEW_FLAG_COLUMN IS NULL
The above query will update all the records to N where ever it was null.
Tip 3: Provide default value at a Table Level
If you load data from EIM into this particular table then either provide a static value of ‘N’ in IFB file for this column or provide default value of either ‘Y’ or ‘N’ depending on your requirement at the Table itself
Providing a value at Table itself will take care of all the scenarios.
Please feel free to add to this list and share more tips if you think I have missed something.