Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
3 replies Latest Post - ‏2013-02-28T16:13:05Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts
ACCEPTED ANSWER

Pinned topic Question - Cognos Rookie here

‏2013-02-27T19:28:26Z |
Hello, I was tasked to lead the business intelligence rollout for my company. We just installed Cognos 10.2

I am having quite a bit of trouble getting the hang of the basics. I come from a MS SQL background in database development and business Intelligence.

So I just created a test project and package. I am now trying to create a test report and ran across some issues.

First, I need to perform a custom sort on the results of one of the fields. In Visual Studio you are able to use an expression for custom sorting. This is not the case for Cognos. I read that I am suppose to create a new calculation definition in Framework Manager.
I just wanted to validate, is this true?

Now I go to create calculation and in the expression try to use a very simple Case statement.

What is wrong with my syntax it gives me this error
QE-DEF-0260 Parsing error before or near position: 14 of: "select
case"
here is the code used:
select
case when test.type_category.type_cat_desc = 'International
then 1
else when test.type_category.type_cat_desc = 'Emerging Markets
then 2
else when test.type_category.type_cat_desc = 'US Equity'
then 3
else when test.type_category.type_cat_desc = 'Currency'
then 4
else when test.type_category.type_cat_desc = 'International Developed Equity'
then 5
else when test.type_category.type_cat_desc = 'Alternative Strategy'
then 6
END

Basically I am assigning a value to try to create the desired sort

What is wrong with this syntax and am I even doing this in the right place? Are there better alternatives.

Thanks Cheers
Updated on 2013-02-28T16:13:05Z at 2013-02-28T16:13:05Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Question - Cognos Rookie here

    ‏2013-02-27T19:58:16Z  in response to SystemAdmin
    I tried using one END for the above syntax it didnt work

    I tried using 6 ENDs and it did not work

    The error message generated QE-DEF-0260 Parsing error before or near position: says that I am using a Cognos or SQL keyword that it does not like
    but I keep looking at the code and I do not see what keyword I am using

    Please this is really urgent
  • DanielWagemann
    DanielWagemann
    411 Posts
    ACCEPTED ANSWER

    Re: Question - Cognos Rookie here

    ‏2013-02-27T22:06:53Z  in response to SystemAdmin
    Where are you trying to do this in FM. Are you manually editing the SQL window?

    Double click on the Query Subject and find the add calcuatlion button, then within the constructs you should see the template for the case when...

    You original syntax is correct minus the select statement. I just think you maybe doing this in the wrong place.
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Question - Cognos Rookie here

      ‏2013-02-28T16:13:05Z  in response to DanielWagemann
      Thanks for taking time to answer my question!!!!
      I am sure there will a be a million more

      cheers!