Case Statement

[Bensta]

Can I use a case statement in Impromptu???
Thank you

 

[kenndot]

maybe in a macro? since essentially it uses vbscript, rigth? Not positive, just a guess.

 

[griffindm]

For non-Oracle databases, Impromptu will translate an If-Then-Else statement into a SQL Case statement automatically for databases that support it. Not sure when it will do this for Oracle. Oracle supports the Case statement since 9i (with limited support in 8i). I'm sure it will get there eventually.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DoubleD]

Cognos will convert IF statements into either Decode, which will then be processed in Oracle, or CASE statements, which will be processed locally prior to 8i, depending on your Cogdmor.ini settings. There is a Case_To_Decode line in the ini file which decides whether Impromptu will attempt to convert to decode statements.

Pain is stress leaving the body

DoubleD

 

[Bensta]

Thank you guys for your help. I appreciate it a lot.
I am quering against an Oracle database. I don't think there is any equivalent of a Case Statement in Impromptu. I am not sure if there are any???
It would be nice though. Especially the ability to write expression like it is possible with Crystal Reports.
Bensta

 

[griffindm]

You can write Case statements in Oracle 8i using interactive SQL. You can't use it in procedures. Oracle 9i allows it in both. Not sure if Cognos will support it in newer releases of Impromptu, but it could certainly boost performance against larger databases. I miss the ability to do non-discrete comparisons (such as if amt > 1 then x else y ) and have the database handle it. Decode is not much use for those types of comparisons.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[DoubleD]

Griffindm,
I have to disagree that Decode can't handle the expression:
(if amt > 1 then x else y). Decode is extremely powerful when used with other database functions. To write your expression with a decode, use the SIGN function. The expression is as follows:
decode(sign(Amt - 1),1,x,y)
The SIGN function evaluates an integer expression as being positive (1), 0 (0), or negative (-1). So the following examples would provide the listed results:

Amt Calculation Result
5.00 sign(5.00 - 1) = 1
1 sign(1 - 1) = 0
0.05 sign(0.05 - 1) = -1
-3 sign(-3 - 1) = -1


Pain is stress leaving the body

DoubleD

 

[griffindm]

Nice example for this case (I have to try it sometime). However, the Decode statement (which I use EXTENSIVELY in stored procedures) does not have the general flexibility of the Case statement.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Chargrams]

Hello,

I ran into a problem with the case_to_decode in the ini file (cogdmor.ini). What does this configuration actually do? Can someone explain it to me.

For Impromptu 7.0 and Oracle 8.1, I set case_to_decode=F in the [Exceptions General Oracle 8.1], but now I moved to Oracle 9.2 and there is no setting for case_to_decode for Oracle 9.2 in the configuration file.

Can someone help.

...Chargrams

 

[DoubleD]

There is a Default Oracle Case_To_Decode statement in your ini file. If you have not updated your Oracle drivers for Oracle 9.2, I would assume it will be using the default.

The Case_To_Decode line tells Impromptu whether you want it to try to convert If Then Else statements into Decode statements instead of Case statements. This was a bigger issue in old versions of Oracle where Case statements were inefficient. It's not as big of a deal anymore, but can still have an performance impact sometimes.

If you have a limited users, you can set all of your Case_To_Decode statements to False, then use the Decode function in Impromptu when possible, and let Impromptu convert any If Then Else statements to Case statements.

Pain is stress leaving the body

DoubleD

 

[Chargrams]

Thanks for the quick response,

I've sort of ran into this problem in the past. Here is the scenario.

Scenario#1
Microsoft ODBC driver (2.573.6200)
Cognos 7.0
***Oracle 8.1
I was receiving division problems, in other words, 5/10 was giving me zero (0)???
-I change the Case_to_decode=F for oracle 8.1 and everything worked fine after that.

Scenario#2
Microsoft ODBC driver (2.573.6200)
Cognos 7.0
***Oracle 9.2
I get the same problem above, divisions = 0.
-since there isn't a section for oracle 9.2 in the cogdmor.ini file, I've entered it in manually, but it doesn't seem to fix the problem.

Here is the entry into the cogdmor.ini file:
[Exceptions General Oracle9.2]
Case_To_Decode=F


Additional information: As a test I switched to the Oracle driver from the Microsoft ODBC for Oracle, and I did not have to make the modification in the cogdmor.ini file, and the division works, however I get other errors in the catalog, where it cannot find certain objects in the dictionary.

Thanks in advance for the help,


...Chargrams