I have a situation where I'm dealing with a primary number which consists of 5 digits and is unique identifier. There are child numbers also associated with this number which appear at the end of the primary number separated with an underscore (00000_0000). I know how to get the information I need, but I'm curious as to which option is more efficient, and I'm hoping some of the folks in the forum have experience to weigh in on the situation.
Solution 1: Filter for the account number I want by using 'LIKE 0000%'
Solution 2: Create a data item in framework manager by using 'LEFT(NUMBER, 5)' and filter on this data item in the report.
Is my question trivial, or is there actually a correct answer in order to have the most efficient outcome?
Quote from: robblob on 02 Aug 2017 07:47:59 AM
I have a situation where I'm dealing with a primary number which consists of 5 digits and is unique identifier. There are child numbers also associated with this number which appear at the end of the primary number separated with an underscore (00000_0000). I know how to get the information I need, but I'm curious as to which option is more efficient, and I'm hoping some of the folks in the forum have experience to weigh in on the situation.
Solution 1: Filter for the account number I want by using 'LIKE 0000%'
Solution 2: Create a data item in framework manager by using 'LEFT(NUMBER, 5)' and filter on this data item in the report.
Is my question trivial, or is there actually a correct answer in order to have the most efficient outcome?
I think this is a database question more than a Cognos question. I'm not a DBA but I suspect it will depend on indexing that may or may not be available. If you are working with a data warehouse then the ideal approach would be to have a separate column containing just the primary portion which could then be indexed and allow for a simple filter expression.