I have the below requirement.
If an account number entered does not fit the format required by the company
Bank Account Number
A- 9 Alphanumeric Digits (No Symbols)
B- 9 Numeric Digits (No Symbols)
C- 9 Numeric Digits (No Symbols)
D- 9 Numeric Digits (No Symbols)
E- 8 Alphanumeric Digits (No Symbols)
F- 9 Alphanumeric Digits (No Symbols)
G- 9 Alphanumeric Digits (No Symbols, Starts with "P")
H- 8 Numeric Digits (No Symbols)
I- 8 Numeric Digits (No Symbols)
J- 8 Numeric Digits (No Symbols)
K- 8 Numeric Digits (No Symbols)
L- 2 Digit Alphanumeric Plus Space Plus 5 Digit Numeric
M- 9 Numeric Digits (No Symbols)
N- Starts with "H" Followed by 5 or 6 Numeric Digits
O- 9 Numeric Digits (Hyphen Between First 4 and Last 4)
P- 8 Numeric Digits (No Symbols)
I need to create this report in Cognos Report studio, can somebody give some idea on the filter condition. Thanks
Which version of Cognos? What RDBMS and where do you need to do the validation? Is this supposed to return a list of values that are already in the database, or pop up a warning as the values are being entered into a prompt?
How much control do you have over the database? Can you create or modify a table if needed?
Which version of Cognos? Cognos 8.4
What RDBMS? DB2 UDB
where do you need to do the validation? Cognos
Is this supposed to return a list of values that are already in the database? Yes
Can you create or modify a table if needed? Yes
Thanks for the reply.
So you need to render a list of account numbers that don't match the associated company format.
DB2 is a bit difficult, as it doesn't have native regex support. You need to set up the regex udfs. See here: http://www.ibm.com/developerworks/data/library/techarticle/0301stolze/0301stolze.html
Once you have that set up, modify your banks table to add a new regex field that contains the search string. So bank a would be ^[0-9aA-zZ]{9}$ and bank G is ^[P][0-9aA-zZ]{8}$