If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Expression parsing error

Started by stevedave, 17 Aug 2021 09:58:24 AM

Previous topic - Next topic
Print
Go Down Pages1
User actions

stevedave

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>

MFGF

#1
17 Aug 2021 12:31:06 PM
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.
Meep!

stevedave

#2
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

MFGF

#3
18 Aug 2021 01:35:00 PM
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.
Meep!
Print
Go Up Pages1
User actions
BSP Software Documentation
MetaManager Documentation and Knowledgebase
Version Control Documentation and Knowledgebase
BSP Software Resources About Us
MetaManager BSP Software Training BSP Software
Integrated Control Suite YouTube Channel Micro Strategies Inc
Security Migration IBM Cognos
Integrated Management Suite