Question regarding functionality of nested CASE statement

[gutterline]

I need to know if a nested CASE statement would function as expected in Cognos 8.4.

Example:

CASE parameter
  WHEN 'A' THEN formula_1
  WHEN 'B' THEN formula_2
  WHEN 'C' THEN (CASE table_data
                        WHEN 'A' THEN formula_1
                        WHEN 'B' THEN formula_2
                        ELSE NULL
                       END)
ELSE NULL
END

When running this, option C produces no data, but there is data for all of the options.  If I remove either of the options from the nested CASE, it will work, but when both options are there it will not.

Any suggestions?

TIA,
Mark

[CognosPaul]

You should use a token prompt for this.

Replace the 'USE' values in the prompt with

Description   |     Use
---------------------------
Description 1 | formula_1
Description 2 | formula_2
Description 3 | CASE table_Data WHEN 'A' THEN formula_1 WHEN 'B' THEN formula_2 ELSE null end

In the data item in the query, replace your case statement with this: #prompt('parameter','token','default_formula,_if_there_is_one,_can_go_in_this_optional_prompt_parameter')#

When the user selects a value in your prompt, Cognos will run that code as if it were typed into the data item. From a performance perspective this is much cleaner and much more efficient than having it done in the SQL level.

[gutterline]

Being new to Cognos, I will say that I had to do some research on the 'token' prompt, but I think I understand it.

I do have another question on this subject.  In the original case statement, the A & B options are actually in the database, but the C option was added in at the prompt level.  Does your solution allow for this or are there other actions I would need to take?

Thanks again,
Mark

[CognosPaul]

If I understand correctly, your prompt is fed by a simple query that returns two values. On top of that you have static value C.

Does the query feeding the prompt ever return anything other than those two values? You can save valuable database time by adding the values to the static choice.

[gutterline]

No, the query only produces two values, the third was added into the static values.

That said, will your previous suggestion work?

[CognosPaul]

If that parameter is only being used by the case statement, it should work. Just remove the reference to the query and add items A and B to the static values with the use values formula_1 and formula_2.