Hello everyone. Pretty new to cognos and neck deep in work. I was wondering if someone could help me figure out an issue I am having. I am trying to turn transactions codes into a spot for a word to generate. Example a Transaction code of 227 should read as 'ATM'. Here is the code I worked out and here is the error.
If there is a better way to ask this question or a spot please let me know and I can fill in more data or move my question. Thanks for reading.
case
when ( [DDHist].[Transaction Code (DDHIST)] = 227 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 228 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 237 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 238 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 243 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 244 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 292 ) then 'POS'
when ( [DDHist].[Transaction Code (DDHIST)] = 293 ) then 'POS'
when ( [DDHist].([Transaction Code (DDHIST)] = 427 ) then 'POS'
when ( [DDHist].([Transaction Code (DDHIST)] = 437 ) then 'POS'
when ( [DDHist].([Transaction Code (DDHIST)] = 166 ) then 'ACH Credit'
when ( [DDHist].([Transaction Code (DDHIST)] = 866 ) then 'ACH Credit'
when ( [DDHist].([Transaction Code (DDHIST)] = 182 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 183 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 186 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 196 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 886 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 1 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 5 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 10 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 21 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 22 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 24 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 33 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 34 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 35 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 38 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 39 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 40 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 43 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 72 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 90 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 113 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 127 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 128 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 130 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 140 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 141 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 142 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 161 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 222 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 223 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 233 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 242 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 245 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 303 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 305 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 317 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 361 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 401 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 403 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 422 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 432 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 451 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 641 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 642 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 689 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 920 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 937 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 938 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 7 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 9 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 25 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 30 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 31 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 32 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 51 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 54 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 62 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 66 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 69 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 70 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 71 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 73 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 74 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 75 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 76 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 77 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 81 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 88 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 89 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 111 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 114 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 131 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 137 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 144 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 145 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 147 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 148 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 181 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 302 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 304 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 316 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 360 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 400 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 402 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 450 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 644 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 645 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 647 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 648 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 747 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 748 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 980 ) then 'Check'
else 'Other'
end
The error:
<queryProblems><message code="901" location="./queries/query[10]/selection/dataItem[8]/expression" severity="error" title="QE-DEF-0459 CCLException" type="expression">QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
</message><message code="901" location="./queries/query[10]/selection/dataItem[8]/expression" severity="error" title="QE-DEF-0459 CCLException" type="expression">QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
</message><message code="901" severity="error" title="QE-DEF-0459 CCLException" type="general">QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
</message></queryProblems>
Quote from: stevedave on 17 Aug 2021 09:58:24 AM
Hello everyone. Pretty new to cognos and neck deep in work. I was wondering if someone could help me figure out an issue I am having. I am trying to turn transactions codes into a spot for a word to generate. Example a Transaction code of 227 should read as 'ATM'. Here is the code I worked out and here is the error.
If there is a better way to ask this question or a spot please let me know and I can fill in more data or move my question. Thanks for reading.
case
when ( [DDHist].[Transaction Code (DDHIST)] = 227 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 228 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 237 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 238 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 243 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 244 ) then 'ATM'
when ( [DDHist].[Transaction Code (DDHIST)] = 292 ) then 'POS'
when ( [DDHist].[Transaction Code (DDHIST)] = 293 ) then 'POS'
when ( [DDHist].([Transaction Code (DDHIST)] = 427 ) then 'POS'
when ( [DDHist].([Transaction Code (DDHIST)] = 437 ) then 'POS'
when ( [DDHist].([Transaction Code (DDHIST)] = 166 ) then 'ACH Credit'
when ( [DDHist].([Transaction Code (DDHIST)] = 866 ) then 'ACH Credit'
when ( [DDHist].([Transaction Code (DDHIST)] = 182 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 183 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 186 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 196 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 886 ) then 'ACH Debit'
when ( [DDHist].([Transaction Code (DDHIST)] = 1 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 5 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 10 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 21 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 22 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 24 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 33 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 34 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 35 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 38 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 39 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 40 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 43 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 72 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 90 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 113 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 127 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 128 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 130 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 140 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 141 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 142 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 161 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 222 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 223 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 233 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 242 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 245 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 303 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 305 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 317 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 361 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 401 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 403 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 422 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 432 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 451 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 641 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 642 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 689 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 920 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 937 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 938 ) then 'Deposit'
when ( [DDHist].([Transaction Code (DDHIST)] = 7 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 9 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 25 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 30 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 31 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 32 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 51 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 54 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 62 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 66 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 69 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 70 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 71 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 73 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 74 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 75 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 76 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 77 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 81 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 88 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 89 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 111 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 114 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 131 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 137 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 144 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 145 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 147 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 148 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 181 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 302 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 304 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 316 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 360 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 400 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 402 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 450 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 644 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 645 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 647 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 648 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 747 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 748 ) then 'Check'
when ( [DDHist].([Transaction Code (DDHIST)] = 980 ) then 'Check'
else 'Other'
end
The error:
<queryProblems><message code="901" location="./queries/query[10]/selection/dataItem[8]/expression" severity="error" title="QE-DEF-0459 CCLException" type="expression">QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
</message><message code="901" location="./queries/query[10]/selection/dataItem[8]/expression" severity="error" title="QE-DEF-0459 CCLException" type="expression">QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
</message><message code="901" severity="error" title="QE-DEF-0459 CCLException" type="general">QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
QE-DEF-0260 Parsing error before or near position: 535 of: "case
QE-DEF-0261 QFWP - Parsing text: case
</message></queryProblems>
Hi,
First comment is that the item name [DDHist].[Transaction Code (DDHIST)] looks wrong to me. I would expect to see either a single-part item name such as [Transaction Code (DDHIST)] or else a 3-part name comprising the package, query subject and query item such as [Your package].[DDHist].[Transaction Code (DDHIST)]
In the code you posted above, there is also a spurious open bracket embedded in the middle of the item name [DDHist].([Transaction Code (DDHIST)] from the ninth when statement onwards, which will cause the expression to fail.
One other comment here is that you are using Search Case syntax, eg
case
when [item]=value then expression
when [item]=value then expression
else expression
end
A cleaner alternative might be to use simple case syntax, eg
case [item]
when value then expression
when value then expression
else expression
end
case [Transaction Code (DDHIST)]
when 227 then 'ATM'
when 228 then 'ATM'
...
else 'Other'
end
One last observation is that you don't need the brackets ( and ) in your case expression, so they can be removed.
Cheers!
MF.
This makes sense. I will use what you have given me and change some things around in a test report.
I really appreciate it MFGF. Thanks for taking the time to respond to me. I am just starting but I am obsessive with my learning.
If you have any thought on trainings, educations, or videos to watch please let me know. I am taking multiple courses now and have a few work will provide me as well.
Thanks
Quote from: stevedave on 18 Aug 2021 11:06:41 AM
This makes sense. I will use what you have given me and change some things around in a test report.
I really appreciate it MFGF. Thanks for taking the time to respond to me. I am just starting but I am obsessive with my learning.
If you have any thought on trainings, educations, or videos to watch please let me know. I am taking multiple courses now and have a few work will provide me as well.
Thanks
Hi,
You're most welcome! :-)
There are some good learning resources on the IBM Demos site - great for getting an initial overview:
https://www.ibm.com/demos/?filter=ibm%20cognos%20analytics
Good luck!
MF.