HANA PAL Executing error

[Former Member]

Dear All,

We are  getting below while trying to run Decision Tree in PAL. Please guide.

Could not execute 'CALL _SYS_AFL.PAL_DT_IRIS (V_DT_IRIS_DATA, DT_IRIS_PARAMS, DT_IRIS_MODEL_JSON, DT_IRIS_MODEL_PMML) ...' in 334 ms 883 µs . SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] executor: plan operation failed;Can not insert data from temp table "SYSTEM:P3_5182B4BE6DC348EFE1000000C0A82A1Den" into table ""DAMUE_PAL_SCHEMA"."DT_IRIS_MODEL_JSON""

Best Regards,

Zaib

[Former Member]

Hi All,

I'm getting the same error :

"dberror(CallableStatement.execute): 2048 - column store error: search table error: [2620] executor: plan operation failed;Can not insert data from temp table into Result_Table"

I have verified i/p & o/p data types and the same code works fine on studio editor , but facing issue only executing the same from xsjs.

Please let me know any other suggestions ..

Thanks & Regards,

Anup Singh

[former_member223209]

Hi Anup,

Can you please share the syntax you are using to call the PAL procedure from XSJS service.

Thanks

Bhupender

[Former Member]

Hi Bhupender ,

Please find below syntax :

Executed from Studio successfully -

CREATE LOCAL TEMPORARY COLUMN TABLE #CONTROL ("NAME" VARCHAR(50), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR(100));

INSERT INTO #CONTROL VALUES ('THREAD_NUMBER', 50, null, null);

INSERT INTO #CONTROL VALUES ('MIN_SUPPORT', null, 0.001, null);

INSERT INTO #CONTROL VALUES ('MIN_CONFIDENCE', null, 0.001, null);

INSERT INTO #CONTROL VALUES ('MAX_ITEM_LENGTH', 100, null, null);

INSERT INTO #CONTROL VALUES ('MAX_CONSEQUENT', 100, null, null);

DROP TABLE "devUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_Result";

CREATE COLUMN TABLE "devUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_Result" ("PRERULE" VARCHAR(256), "POSTRULE" VARCHAR(256), "SUPPORT" DOUBLE, "CONFIDENCE" DOUBLE, "LIFT" DOUBLE);

DROP TABLE "devUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_PMMLResult";

CREATE COLUMN TABLE "devUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_PMMLResult" ("ID" INTEGER, "Descritption" VARCHAR(5000));

CALL "devUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori"("devUser"."CNMR_APRIORI_TRANS_TBL1", #CONTROL, "devUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_Result", "devUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_PMMLResult") with overview;

__________________________________________________________________

I get the error when executing the same from XSJS -

var sqlstmt;

var method;

var conn;

var output = {};

output.msg = "";

$.response.contentType = "text/json";

$.response.status = $.net.http.OK;

method = parseInt($.request.parameters.get("method"),10);

if (method >= 1 && method <= 4) {

    conn = $.db.getConnection("CentralRepo.ABCRecommends1::DevUser");

    sqlstmt = 'CREATE LOCAL TEMPORARY COLUMN TABLE #CONTROL ("NAME" VARCHAR(50), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR(100))';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #CONTROL VALUES (\'THREAD_NUMBER\', 50, null, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #CONTROL VALUES (\'MIN_SUPPORT\', null, 0.0001, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #CONTROL VALUES (\'MIN_CONFIDENCE\', null, 0.0001, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #CONTROL VALUES (\'LIFT\', null, 0.001, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #CONTROL VALUES (\'MAX_ITEM_LENGTH\', 100, null, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #CONTROL VALUES (\'MAX_CONSEQUENT\', ?, null, null)';

    var pstmt = conn.prepareStatement(sqlstmt);

    pstmt.setInt(1,method);

    pstmt.execute();   

    sqlstmt = 'DROP TABLE "DevUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_Result"';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'CREATE COLUMN TABLE "DevUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_Result" ("PRERULE" VARCHAR(256), "POSTRULE" VARCHAR(256), "SUPPORT" DOUBLE, "CONFIDENCE" DOUBLE, "LIFT" DOUBLE)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'DROP TABLE "DevUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_PMMLResult"';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'CREATE COLUMN TABLE "DevUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_PMMLResult" ("ID" INTEGER, "Descritption" VARCHAR(5000))';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'CALL "DevUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori"("DevUser"."CNMR_APRIORI_TRANS_TBL1", #CONTROL, "DevUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_Result", "DevUser"."CentralRepo.ABCRecommends1.Algorithms::Apriori.Apriori_aprioriRule_PMMLResult") with overview';

    conn.prepareCall(sqlstmt).execute();

    conn.commit();

    output.msg = "Finished!";

} else {

    output.msg = "Value for 'method' parameter is not between 1 and 4.";

}

if (conn) {conn.close();}  

$.response.setBody(JSON.stringify(output));

Thanks & Regards,

Anup Singh

[former_member223209]

Hi Anup,

Below code worked for me, Only main change is use prepareStatement() in place of prepareCall().

try

{

var sqlstmt;

var method;

var conn;

var output = {};

output.msg = "";

$.response.contentType = "text/json";

$.response.status = $.net.http.OK;

method = parseInt($.request.parameters.get("method"),10);

var conn = $.db.getConnection();

    sqlstmt = 'CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_AD_CONTROL_TBL ("NAME" VARCHAR(50), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR(100))';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #PAL_AD_CONTROL_TBL VALUES (\'THREAD_NUMBER\', 50, null, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'truncate table  "PAL_00"."PAL_AD_RESULT_TBL"';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'CALL "_SYS_AFL".PAL_ANOMALY_DETECTION("PAL_00"."pal_input",#PAL_AD_CONTROL_TBL, "PAL_00"."PAL_AD_RESULT_TBL") with overview';

    conn.prepareStatement(sqlstmt).execute();

    conn.commit();

    output.msg = "Finished!";

if (conn) {conn.close();} 

$.response.setBody(JSON.stringify(output));

}catch (e) {

  $.response.setBody(e.message + "error is there");

}

And one suggestion is no need to drop and create output tables every time, Better use truncate command to just delete the DATA.

Hope this helps you,

Thanks You

Bhupender

[Former Member]

Much better is to use a SQLScript wrapper and use arrays to generate the tables you need. I should write a blog on this.

[former_member223209]

Hi John,

Waiting for your blog

Thanks

Bhupender

[Former Member]

Hi Bhupender ,

I just changed the prepareStatement() in place of prepareCall() in call procedure.

It throws out error  -

"InternalError: dberror(PreparedStatement.execute): 257 - sql syntax error: sql syntax error: incorrect syntax near "GENERATED": "

Please advise , do u see any other changes ??

Thanks & Regards,

Anup Singh

[Former Member]

Hi John,

Please advise , If i Should call "SYSTEM".afl_wrapper_eraser and generator procedure before running AFL procedure ?

Thanks & Regards,

Anup Singh

[former_member223209]

Hi Anup,

Please share  line code on which you are getting error.

Thanks

Bhupender

[Former Member]

Hi Bhupender,

Highlighted code for which I'm getting error :

var sqlstmt;

var method;

var conn;

var output = {};

output.msg = "";

$.response.contentType = "text/json";

$.response.status = $.net.http.OK;

method = parseInt($.request.parameters.get("method"),10);

if (method >= 1 && method <= 4) {

    conn = $.db.getConnection();

    sqlstmt = 'TRUNCATE TABLE "DEVUSER"."APRIORICNTRL" ';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO "DEVUSER"."APRIORICNTRL" VALUES (\'THREAD_NUMBER\', 50, null, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO "DEVUSER"."APRIORICNTRL" VALUES (\'MIN_SUPPORT\', null, 0.0001, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO "DEVUSER"."APRIORICNTRL" VALUES (\'MIN_CONFIDENCE\', null, 0.0001, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO "DEVUSER"."APRIORICNTRL" VALUES (\'LIFT\', null, 0.001, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO "DEVUSER"."APRIORICNTRL" VALUES (\'MAX_ITEM_LENGTH\', 100, null, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO "DEVUSER"."APRIORICNTRL" VALUES (\'MAX_CONSEQUENT\', ?, null, null)';

    var pstmt = conn.prepareStatement(sqlstmt);

    pstmt.setInt(1,method);

    pstmt.execute();  

    sqlstmt = ' TRUNCATE TABLE "DEVUSER"."CNMR_APRIORI_RESULT_TBL1"';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'TRUNCATE TABLE "DEVUSER"."CNMR_APRIORI_PMMLMODEL_TBL1"';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'CALL "_SYS_AFL".CNMR_APRIORI_RULE_PROC("DEVUSER"."CNMR_APRIORI_TRANS_TBL1", "DEVUSER"."APRIORICNTRL", "DEVUSER"."CNMR_APRIORI_RESULT_TBL1", "DEVUSER"."CNMR_APRIORI_PMMLMODEL_TBL1") WITH overview';

     conn.prepareStatement(sqlstmt).execute();

    conn.commit();

    output.msg = "Finished!";

} else {

    output.msg = "Value for 'method' parameter is not between 1 and 4.";

}

if (conn) {conn.close();} 

$.response.setBody(JSON.stringify(output));

Thanks & Regards,

Anup Singh

[former_member223209]

Hi Anup,

I think there is some issue with your call statement, Try executing this same call statement in SQL console and make it proper. Error is because temporary tables are not stored in any schema, where as you have  given schema name.

Thanks

Bhupender

[Former Member]

Hi Bhupender,

I had used both temporary table and standard table for control table , but facing the same issue.

I get the same error.

Thanks & Regards,

Anup Singh

[former_member223209]

Hi Anup,

Want to know whether you are able to execute this algorithm in SQL console?

Thanks

Bhupender

[Former Member]

Hi Bhupender,

Yes i'm able to execute the same codes from SQL editor , only when i execute from xsjs then i get the issue as "cannot insert from temp_table to result_table.

Thanks & Regards,

Anup Singh

[former_member223209]

Hi Anup,

You may be doing some small issue, may be of data type miss match, which code i sent you worked fine for me. You try with the simplest code which i sent you.

Thanks

Bhupender

[Former Member]

Hi Bhupender,

I took the same code you gave me ,

__________________________________________________________________________________________________________

Found the following errors:

===========================

InternalError: dberror(CallableStatement.execute): 2048 - column store error: search table error: [2620] executor: plan operation failed;Can not insert data from temp table "DEVUSER:P3_546B7DEE54F319B6E10000007F000002 (-1)" into table ""DEVUSER"."MRepo::apr.AnomalyDetection_anomalyDetection_Result"" at ptime/session/eapi/jdbc/ExternalStatement.cc:927 (line 29 position 0 in /CentralRepo/HMKRecommends1/Anomalydetection.xsjs)

___________________________________________________________________________________________________________

Code :

var sqlstmt;

var method;

var conn;

var output = {};

output.msg = "";

$.response.contentType = "text/json";

$.response.status = $.net.http.OK;

method = parseInt($.request.parameters.get("method"),10);

var conn = $.db.getConnection();

    sqlstmt = 'CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_AD_CONTROL_TBL ("NAME" VARCHAR(50), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR(100))';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'INSERT INTO #PAL_AD_CONTROL_TBL VALUES (\'THREAD_NUMBER\', 50, null, null)';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'truncate table  "DEVUSER"."MRepo::apr.AnomalyDetection_anomalyDetection_Result"';

    conn.prepareStatement(sqlstmt).execute();

    sqlstmt = 'call "DEVUSER"."MRepo::apr.AnomalyDetection"("DEVUSER"."PAL_AD_DATA_TBL",#PAL_AD_CONTROL_TBL, "DEVUSER"."MRepo::apr.AnomalyDetection_anomalyDetection_Result") with overview';

    conn.prepareCall(sqlstmt).execute();   

    conn.commit();

    output.msg = "Finished!";

if (conn) {conn.close();}

$.response.setBody(JSON.stringify(output));

___________________________________________________________

When i use conn.prepareStatement(sqlstmt).execute(); instead of conn.prepareCall(sqlstmt).execute();

i get below SQL error :

Found the following errors:

===========================

InternalError: dberror(PreparedStatement.execute): 257 - sql syntax error: sql syntax error: incorrect syntax near "GENERATED": line 1 col 2 (at pos 2) at ptime/session/dist/RemoteQueryExecution.cc:1354 (line 27 position 0 in /CentralRepo/ABCRecommends1/Anomalydetection.xsjs)

Thanks & Regards,

Anup Singh

[Former Member]

Most of times this error is occurred because of following line ,

INSERT INTO DT_PARAMS_Z VALUES ('IS_SPLIT_MODEL', 0, null, null);

So change to

INSERT INTO DT_PARAMS_Z VALUES ('IS_SPLIT_MODEL', 1, null, null);

[rindia]

Dear Zaib,

Have you checked the table used for storing tree model is column table. Also the input data should not contain null value and column order should match with tree model.

Hope the user has assigned the role AFL__SYS_AFL_AFLPAL_EXECUTE.

If you have already taken care of all of the above, then could you please send the complete SQLScript including the procedure, input table, parameter table, result output table including table types.

Regards

Raj

[Former Member]

Hi Raj,

Thank you. I already solved this issue. Actually there were some duplication of types.

Surely i will send you.

What i understood is that its better to drop all tables and types to avoid any duplication.

Best Regards,

Zaib

[Former Member]

Hi Raj,

See below code.

                                --CREATION OF A DT--

SET SCHEMA PAL_ZAZ;

-- PAL setup

/*

DROP TYPE PAL_T_DT_DATA_Z;

DROP TYPE PAL_T_DT_PARAMS_Z;

DROP TYPE PAL_T_DT_MODEL_JSON_Z;

DROP TYPE PAL_T_DT_MODEL_PMML_Z;

DROP TABLE PAL_DT_SIGNATURE_Z;

DROP PROCEDURE _SYS_AFL.PAL_DT_Z;

*/

CREATE TYPE PAL_T_DT_DATA_Z AS TABLE (POLICY VARCHAR(10), AGE INTEGER, AMOUNT INTEGER, OCCUPATION VARCHAR(10), FRAUD VARCHAR(10));

CREATE TYPE PAL_T_DT_PARAMS_Z AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR (100));

CREATE TYPE PAL_T_DT_MODEL_JSON_Z AS TABLE (ID INTEGER, JSONMODEL VARCHAR(5000));

CREATE TYPE PAL_T_DT_MODEL_PMML_Z AS TABLE (ID INTEGER, PMMLMODEL VARCHAR(5000));

CREATE COLUMN TABLE PAL_DT_SIGNATURE_Z (ID INTEGER, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100));

INSERT INTO PAL_DT_SIGNATURE_Z VALUES (1, 'PAL_T_DT_DATA_Z', 'in');

INSERT INTO PAL_DT_SIGNATURE_Z VALUES (2, 'PAL_T_DT_PARAMS_Z', 'in');

INSERT INTO PAL_DT_SIGNATURE_Z VALUES (3, 'PAL_T_DT_MODEL_JSON_Z', 'out');

INSERT INTO PAL_DT_SIGNATURE_Z VALUES (4, 'PAL_T_DT_MODEL_PMML_Z', 'out');

CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_DT_Z', 'AFLPAL', 'CREATEDT', PAL_DT_SIGNATURE_Z);

-- app setup

/*

DROP VIEW V_DT_DATA_Z;

DROP TABLE DT_PARAMS_Z;

DROP TABLE DT_MODEL_PMML_Z;

*/

CREATE VIEW V_DT_DATA_Z AS

SELECT POLICY, AGE, AMOUNT, OCCUPATION, FRAUD

  FROM CLAIMS

  ;

CREATE COLUMN TABLE DT_PARAMS_Z LIKE PAL_T_DT_PARAMS_Z;

CREATE COLUMN TABLE DT_MODEL_JSON_Z LIKE PAL_T_DT_MODEL_JSON_Z;

CREATE COLUMN TABLE DT_MODEL_PMML_Z LIKE PAL_T_DT_MODEL_PMML_Z;

/*

INSERT INTO DT_PARAMS_Z VALUES ('THREAD_NUMBER', 2, null, null);

INSERT INTO DT_PARAMS_Z VALUES ('PERCENTAGE', null, 1.0, null);

INSERT INTO DT_PARAMS_Z VALUES ('MIN_NUMS_RECORDS', 1, null, null);

INSERT INTO DT_PARAMS_Z VALUES ('IS_SPLIT_MODEL', 0, null, null);

INSERT INTO DT_PARAMS_Z VALUES ('PMML_EXPORT', 1, null, null);

INSERT INTO DT_PARAMS_Z VALUES ('CONTINUOUS_COL', 1, 30, null);

INSERT INTO DT_PARAMS_Z VALUES ('CONTINUOUS_COL', 1, 50, null);

INSERT INTO DT_PARAMS_Z VALUES ('CONTINUOUS_COL', 2, 500, null);

INSERT INTO DT_PARAMS_Z VALUES ('CONTINUOUS_COL', 2, 1000, null);

INSERT INTO DT_PARAMS_Z VALUES ('CONTINUOUS_COL', 2, 2000, null);

*/

-- app runtime

TRUNCATE TABLE DT_MODEL_JSON_Z;

TRUNCATE TABLE DT_MODEL_PMML_Z;

CALL _SYS_AFL.PAL_DT_Z (V_DT_DATA_Z, DT_PARAMS_Z, DT_MODEL_JSON_Z, DT_MODEL_PMML_Z) WITH OVERVIEW;

bEST rEGARDS,

zAIB

[rindia]

Dear Zaib,

Good to know that problem is solved.

Thanks for sending code.

Regards

Raj

[former_member223209]

Hi Raj,

I am getting the below error while executing the PAL Procedure.

Could not execute 'CALL _SYS_AFL.PAL_ANOMALY_DETECTION("PAL"."pal_input", #PAL_AD_CONTROL_TBL, ...' in 283 ms 98 µs .

SAP DBTech JDBC: [2048]: column store error: search table error:  [2620] executor: plan operation failed;Can not insert data from temp table "SYSTEM:P3_538DCC8E4D82D7BCE10000000A3071A8 (-1)" into table ""PAL"."PAL_AD_RESULT_TBL""

And  above solution are not working for me.

The code which i am executing is below

SET SCHEMA PAL;

DROP TYPE PAL_AD_RESULT_T;

CREATE TYPE PAL_AD_RESULT_T AS TABLE( "ProductId" NVARCHAR, "Quantity" Double, "GrossAmount" Double);

DROP TYPE PAL_AD_DATA_T;

CREATE TYPE PAL_AD_DATA_T AS TABLE( "ProductId" NVARCHAR, "Quantity" Double, "GrossAmount" Double);

DROP TYPE PAL_AD_CONTROL_T;

CREATE TYPE PAL_AD_CONTROL_T AS TABLE( "NAME" VARCHAR (50), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR (100));

DROP TABLE PAL_AD_PDATA_TBL;

CREATE COLUMN TABLE PAL_AD_PDATA_TBL( "ID" INT, "TYPENAME" VARCHAR(100), "DIRECTION" VARCHAR(100) );

INSERT INTO PAL_AD_PDATA_TBL VALUES (1, 'PAL.PAL_AD_DATA_T', 'in');

INSERT INTO PAL_AD_PDATA_TBL VALUES (2, 'PAL.PAL_AD_CONTROL_T', 'in');

INSERT INTO PAL_AD_PDATA_TBL VALUES (3, 'PAL.PAL_AD_RESULT_T', 'out');

--GRANT SELECT ON SFP.PAL_AD_PDATA_TBL to SYSTEM;

call SYSTEM.afl_wrapper_eraser('PAL_ANOMALY_DETECTION');

call SYSTEM.afl_wrapper_generator('PAL_ANOMALY_DETECTION', 'AFLPAL', 'ANOMALYDETECTION', PAL_AD_PDATA_TBL);

SET SCHEMA PAL;

DROP TABLE #PAL_AD_CONTROL_TBL;

CREATE LOCAL TEMPORARY COLUMN TABLE #PAL_AD_CONTROL_TBL ( "NAME" VARCHAR (50), "INTARGS" INTEGER, "DOUBLEARGS" DOUBLE, "STRINGARGS" VARCHAR (100));

INSERT INTO #PAL_AD_CONTROL_TBL VALUES ('THREAD_NUMBER',2,null,null);

INSERT INTO #PAL_AD_CONTROL_TBL VALUES ('GROUP_NUMBER',3,null,null);

--INSERT INTO #PAL_AD_CONTROL_TBL VALUES ('INIT_TYPE',4,null,null);

--INSERT INTO #PAL_AD_CONTROL_TBL VALUES ('DISTANCE_LEVEL',2,null,null);

--INSERT INTO #PAL_AD_CONTROL_TBL VALUES ('MAX_ITERATION',1000,null,null);

DROP TABLE PAL_AD_RESULT_TBL;

CREATE COLUMN TABLE PAL_AD_RESULT_TBL LIKE PAL_AD_RESULT_T;

DROP view PAL."pal_input";

create view PAL."pal_input"(

"ProductId",

"Quantity",

"GrossAmount") AS

select "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products"."ProductId",sum(CAST("Quantity" AS DOUBLE )),sum(CAST("GrossAmount" AS DOUBLE ))

from "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::purchaseOrderItem","SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products"

where "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::purchaseOrderItem"."ProductId" = "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products"."ProductId"

group by "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::products"."ProductId";

CALL _SYS_AFL.PAL_ANOMALY_DETECTION("PAL"."pal_input", #PAL_AD_CONTROL_TBL, "PAL"."PAL_AD_RESULT_TBL") with overview;

There is no issue in creating the view.

Thanks

Bhupender

[former_member223209]

Hi All,

I got solution for above problem.

Issue was coming because i was using NVARCHAR data type ( "ProductId" NVARCHAR), where as algorithm need the VARCHAR data type.

So I suggest to Match every tables's Data type in such case.

thanks

bhupender