Some of our reports stopped working when I installed impromptu 7.1 MR2 in place of the 7.1 MR1.
The reports that do not work anymore show the number of visits done by our sales reps per month. They always contain the same 3 fields for each month.
The first field calculates the number of submitted visits for a particular month.
The second field calculates the number of planned visits for a particular month.
The third field contains the number of submitted visits if the first field is not equal to 0 else it contains the letter P if the second field is not equal to 0 or is NULL if none of the previous clauses are fulfilled.
The reports are quite complex and contain a lot more than these fields but I think I isolated the problem with this 'simple' example:
Using a single table containing 4 columns:
MONTH_NUMBER; VISIT_ID; VISIT_STATUS; CUSTOMRE_ID
02; 7; S; 1
01; 6; P; 3
01; 5; S; 2
01; 4; S; 2
01; 1; S; 1
01; 2; P; 1
01; 3; S; 1
In impromptu, you can now perform the test for January.
You select the CUSTOMER_ID and group it.
You create a "Jan S" field that contains this formula (calculated for CUSTOMER_ID):
count ( distinct ( if ( MONTH_NUMBER = '01' and VISIT_STATUS = 'S' ) then ( VISIT_ID ) else NULL ) )
You create a "Jan P" field that contains this formula (calculated for CUSTOMER_ID):
count ( distinct ( if ( MONTH_NUMBER = '01' and VISIT_STATUS = 'P' ) then ( VISIT_ID ) else NULL ) )
If you run the report, everything goes well.
But if you create the last field which contains this formula:
If ( Jan S > 0 ) then ( number-to-string ( Jan S ) ) else if ( Jan P > 0 ) then ( 'P' ) else NULL
The 7.1 MR 2 crashes but the 7.1 MR 1 retrieves the correct values.
If this work on MR 1 should it not work on MR 2?
I read here that there were problems with the 'if…then'/decode clause in Oracle
As I am working with Oracle 8.1.7;
I tried to put this line in impromptu.ini: Use automatic Oracle Decode=0.
No luck there.
I tried to put this line in cogdmor.ini:
[Exceptions General ORACLE8.1.7]
Case_To_Decode=F
That did not work either.
There may be another way to calculate these fields and maybe that would do the trick in this little report but the real reports are very complex and I don't know if that would also work with them.
Thanks for your time,
The reports that do not work anymore show the number of visits done by our sales reps per month. They always contain the same 3 fields for each month.
The first field calculates the number of submitted visits for a particular month.
The second field calculates the number of planned visits for a particular month.
The third field contains the number of submitted visits if the first field is not equal to 0 else it contains the letter P if the second field is not equal to 0 or is NULL if none of the previous clauses are fulfilled.
The reports are quite complex and contain a lot more than these fields but I think I isolated the problem with this 'simple' example:
Using a single table containing 4 columns:
MONTH_NUMBER; VISIT_ID; VISIT_STATUS; CUSTOMRE_ID
02; 7; S; 1
01; 6; P; 3
01; 5; S; 2
01; 4; S; 2
01; 1; S; 1
01; 2; P; 1
01; 3; S; 1
In impromptu, you can now perform the test for January.
You select the CUSTOMER_ID and group it.
You create a "Jan S" field that contains this formula (calculated for CUSTOMER_ID):
count ( distinct ( if ( MONTH_NUMBER = '01' and VISIT_STATUS = 'S' ) then ( VISIT_ID ) else NULL ) )
You create a "Jan P" field that contains this formula (calculated for CUSTOMER_ID):
count ( distinct ( if ( MONTH_NUMBER = '01' and VISIT_STATUS = 'P' ) then ( VISIT_ID ) else NULL ) )
If you run the report, everything goes well.
But if you create the last field which contains this formula:
If ( Jan S > 0 ) then ( number-to-string ( Jan S ) ) else if ( Jan P > 0 ) then ( 'P' ) else NULL
The 7.1 MR 2 crashes but the 7.1 MR 1 retrieves the correct values.
If this work on MR 1 should it not work on MR 2?
I read here that there were problems with the 'if…then'/decode clause in Oracle
As I am working with Oracle 8.1.7;
I tried to put this line in impromptu.ini: Use automatic Oracle Decode=0.
No luck there.
I tried to put this line in cogdmor.ini:
[Exceptions General ORACLE8.1.7]
Case_To_Decode=F
That did not work either.
There may be another way to calculate these fields and maybe that would do the trick in this little report but the real reports are very complex and I don't know if that would also work with them.
Thanks for your time,