Troubleshooting
Problem
If a filter in Report Studio has multiple 'OR' and 'AND' operators with parenthesis, then the navtive SQL generated in Report Studio takes the paranthesis out of the where clause
Symptom
Parenthesis placed in filter does not show up in native SQL in report studio
Cause
Cognos creates where clause with minimal parenthesis keeping the logic of the filter same and takes out redundant parentheses
Diagnosing The Problem
If you have a filter defined like this
Example 1 :
( A = 1 AND B = 2) AND ( C = 10 AND D =5 )
- Cognos will create the 'where' clause as : 'where A = 1 AND B=2 AND C=10 AND D=5'
- All arguments use 'AND' and all arguments must be true to evaluate this filter to true. Thus, the parentheses
Example 2. ( A=1 AND B=2) OR (C =10 AND D=5)
- Again Cognos will create 'where' clause as: 'where A = 1 AND B=2 OR C=10 AND D=5'
- 'AND' is evaluated first and then 'OR'. In this example the logic does not change if parentheses
Example 3. (A = 1 OR B = 2) AND (C = 10 OR D = 5)
-Cognos will create the 'where' clause as: 'where (A = 1 OR B = 2) AND (C = 10 OR D = 5)'
-This time Cognos will keep the parentheses since removing parenthesis would change the logic of the filter. In this statement 'OR' statement has to be evaluated first . If parenthesis are removed 'AND' will be evaluated first and will change the logic of the filter.
Example 1 :
( A = 1 AND B = 2) AND ( C = 10 AND D =5 )
- Cognos will create the 'where' clause as : 'where A = 1 AND B=2 AND C=10 AND D=5'
- All arguments use 'AND' and all arguments must be true to evaluate this filter to true. Thus, the parentheses
Example 2. ( A=1 AND B=2) OR (C =10 AND D=5)
- Again Cognos will create 'where' clause as: 'where A = 1 AND B=2 OR C=10 AND D=5'
- 'AND' is evaluated first and then 'OR'. In this example the logic does not change if parentheses
Example 3. (A = 1 OR B = 2) AND (C = 10 OR D = 5)
-Cognos will create the 'where' clause as: 'where (A = 1 OR B = 2) AND (C = 10 OR D = 5)'
-This time Cognos will keep the parentheses since removing parenthesis would change the logic of the filter. In this statement 'OR' statement has to be evaluated first . If parenthesis are removed 'AND' will be evaluated first and will change the logic of the filter.
Resolving The Problem
Put the parenthesis in the filter if it does make difference in logic. If parenthesis are placed in the filter for readability only, Cognos will remove original parenthesis in the native SQL if doing so will not change the logic of the filter.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTSF6","label":"IBM Cognos Analytics"},"ARM Category":[{"code":"","label":""}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]
Was this topic helpful?
Document Information
Modified date:
17 November 2022
UID
swg21389789