Activating Calculation View Fails With general error: Undefined typeCode:-1

[Former Member]

Hello,

I've got a scripted Calculation View, which I am trying to activate, however, everytime that I attempt activation I get the error "Undefined typeCode: -1.

The code is as follows:

/********* Begin Procedure Script ************/

BEGIN

  vins = CE_OLAP_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/AN_VEHICLESALES",

  ["CarlineID", "ModelYearID", "ProfileAge", SUM("NoOfVINs") AS "NoOfVINs"]);

  vins_proj = CE_PROJECTION(:vins,

  ["CarlineID", "ModelYearID", "ProfileAge" AS "Day", "NoOfVINs"]);

  profiles = CE_CALC_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/CA_PROFILES_CARLINECATEGORY",

  ["CarlineID", "CategoryID", "Day", "SeverityPercent"]);

  vin_svy_pcs = CE_JOIN(:vins_proj, :profiles, ["CarlineID", "CategoryID", "Day"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs", "SeverityPercent"]);

  vin_earned_pos = CE_PROJECTION(:vin_svy_pcs,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs",

  "SeverityPercent" AS "Maturity",

  CE_CALC('("NoOfVINs" * "Maturity")', double) AS "EarnedPolicies"]);

  results = CE_AGGREGATION(:vin_earned_pos,

  [SUM("NoOfVINs") AS "NoOfVINs", SUM("Maturity") AS "Maturity", SUM("EarnedPolicies") AS "EarnedPolicies"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID"]);

  var_out = CE_PROJECTION(:results,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "NoOfVINs", "EarnedPolicies", "Maturity"]);

END /********* End Procedure Script ************/

The activation error is as follows:

ERROR      wepredict/martyn/indico/automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY.calculationview

           Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: Join attributes not found in variable: CategoryID: line 13 col 61 (at pos 763)

Set Schema DDL statement: set schema "SYSTEM"

Type DDL: create type "_SYS_BIC"."wepredict.martyn.indico.automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY/proc/tabletype/VAR_OUT" as table ("ManufacturerID" INTEGER, "CarlineID" INTEGER, "ModelYearID" INTEGER, "CategoryID" INTEGER, "NoOfVINs" INTEGER, "EarnedPolicies" DOUBLE, "Maturity" DOUBLE)

Procedure DDL: create procedure "_SYS_BIC"."wepredict.martyn.indico.automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY/proc" ( OUT var_out "_SYS_BIC"."wepredict.martyn.indico.automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as /********* Begin Procedure Script ************/

BEGIN

  vins = CE_OLAP_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/AN_VEHICLESALES",

  ["CarlineID", "ModelYearID", "ProfileAge", SUM("NoOfVINs") AS "NoOfVINs"]);

  vins_proj = CE_PROJECTION(:vins,

  ["CarlineID", "ModelYearID", "ProfileAge" AS "Day", "NoOfVINs"]);

  profiles = CE_CALC_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/CA_PROFILES_CARLINECATEGORY",

  ["CarlineID", "CategoryID", "Day", "SeverityPercent"]);

  vin_svy_pcs = CE_JOIN(:vins_proj, :profiles, ["CarlineID", "CategoryID", "Day"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs", "SeverityPercent"]);

  vin_earned_pos = CE_PROJECTION(:vin_svy_pcs,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs",

  "SeverityPercent" AS "Maturity",

  CE_CALC('("NoOfVINs" * "Maturity")', double) AS "EarnedPolicies"]);

  results = CE_AGGREGATION(:vin_earned_pos,

  [SUM("NoOfVINs") AS "NoOfVINs", SUM("Maturity") AS "Maturity", SUM("EarnedPolicies") AS "EarnedPolicies"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID"]);

  var_out = CE_PROJECTION(:results,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "NoOfVINs", "EarnedPolicies", "Maturity"]);

END /********* End Procedure Script ************/

Please can anyone help me with what might be the problem and how to fix it?

Thanks very much,

Martyn.

[patrickbachmann · Accepted solution]

Hi Martyn,

I'm wondering if it's because in your CE_JOIN you are joining :vins_proj with :profiles using keys "CarlineID", "CategoryID", "Day" yet I do not see CategoryID in the vins_proj projection variable.

-Patrick

[patrickbachmann · Accepted solution]

Actually the more I look at your error I think this is indeed the issue as it refers to CategoryID missing in the variable.  Did you mean to name "ModelYearID" as "CategoryID" instead?

-Patrick

[Former Member · Accepted solution]

Thank you Patrick. I now feel particularly silly as I hadn't read that particular occurrence of the error message.

I'd been trying for several hours to activate the view and kept getting the undefined type code error. I hadn't noticed the presence of the CategoryID in the error.

I'll fix the CategoryID and cross my fingers that it fixes it.

Thanks very much for taking the time to help me out.

Very much appreciated.

Martyn.

[Former Member · Accepted solution]

Hello,

I've fixed the issue pointed out by Patrick and now I'm back to square one.

The error message is now:

ERROR      wepredict/martyn/indico/automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY.calculationview

           Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: transaction rolled back by an internal error: general error: Undefined typeCode:-59885776

Set Schema DDL statement: set schema "SYSTEM"

Type DDL: create type "_SYS_BIC"."wepredict.martyn.indico.automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY/proc/tabletype/VAR_OUT" as table ("ManufacturerID" INTEGER, "CarlineID" INTEGER, "ModelYearID" INTEGER, "CategoryID" INTEGER, "NoOfVINs" INTEGER, "EarnedPolicies" DOUBLE, "Maturity" DOUBLE)

Procedure DDL: create procedure "_SYS_BIC"."wepredict.martyn.indico.automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY/proc" ( OUT var_out "_SYS_BIC"."wepredict.martyn.indico.automotive/CA_EARNINGS_CARLINEMODELYEARCATEGORY/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as /********* Begin Procedure Script ************/

BEGIN

  vins = CE_OLAP_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/AN_VEHICLESALES",

  ["CarlineID", "ModelYearID", "ProfileAge", SUM("NoOfVINs") AS "NoOfVINs"]);

  vins_proj = CE_PROJECTION(:vins,

  ["CarlineID", "ModelYearID", "ProfileAge" AS "Day", "NoOfVINs"]);

  profiles = CE_CALC_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/CA_PROFILES_CARLINECATEGORY",

  ["CarlineID", "CategoryID", "Day", "SeverityPercent"]);

  vin_svy_pcs = CE_JOIN(:vins_proj, :profiles, ["CarlineID", "Day"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs", "SeverityPercent"]);

  vin_earned_pos = CE_PROJECTION(:vin_svy_pcs,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs",

  "SeverityPercent" AS "Maturity",

  CE_CALC('("NoOfVINs" * "Maturity")', double) AS "EarnedPolicies"]);

  results = CE_AGGREGATION(:vin_earned_pos,

  [SUM("NoOfVINs") AS "NoOfVINs", SUM("Maturity") AS "Maturity", SUM("EarnedPolicies") AS "EarnedPolicies"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID"]);

  var_out = CE_PROJECTION(:results,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "NoOfVINs", "EarnedPolicies", "Maturity"]);

END /********* End Procedure Script ************/

The code is now:

/********* Begin Procedure Script ************/

BEGIN

  vins = CE_OLAP_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/AN_VEHICLESALES",

  ["CarlineID", "ModelYearID", "ProfileAge", SUM("NoOfVINs") AS "NoOfVINs"]);

  vins_proj = CE_PROJECTION(:vins,

  ["CarlineID", "ModelYearID", "ProfileAge" AS "Day", "NoOfVINs"]);

  profiles = CE_CALC_VIEW("_SYS_BIC"."wepredict.martyn.indico.automotive/CA_PROFILES_CARLINECATEGORY",

  ["CarlineID", "CategoryID", "Day", "SeverityPercent"]);

  vin_svy_pcs = CE_JOIN(:vins_proj, :profiles, ["CarlineID", "Day"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs", "SeverityPercent"]);

  vin_earned_pos = CE_PROJECTION(:vin_svy_pcs,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "Day", "NoOfVINs",

  "SeverityPercent" AS "Maturity",

  CE_CALC('("NoOfVINs" * "Maturity")', double) AS "EarnedPolicies"]);

  results = CE_AGGREGATION(:vin_earned_pos,

  [SUM("NoOfVINs") AS "NoOfVINs", SUM("Maturity") AS "Maturity", SUM("EarnedPolicies") AS "EarnedPolicies"],

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID"]);

  var_out = CE_PROJECTION(:results,

  ["ManufacturerID", "CarlineID", "ModelYearID", "CategoryID", "NoOfVINs", "EarnedPolicies", "Maturity"]);

END /********* End Procedure Script ************/

Thanks very much,

Martyn.

[patrickbachmann · Accepted solution]

Hi Martyn,

Now I think maybe the problem is with the first join.  I see a field called ManufacturerID yet I don't see this field in either of the table variables it's joining (vins_proj nor profiles).

That's my current guess at least. 

-Patrick

[Former Member · Accepted solution]

Hello Patrick,

I changed that and it still didn't work, so I gave up trying to fix it and implemented it as a Graphical Calculation View instead. That works fine.

Thanks very much for your help,

Martyn.

[patrickbachmann · Accepted solution]

Ok Martyn, glad to help and glad you have a workaround.

-Patrick