cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation Scenario Filter does not work as expected

alimiri71
Associate
Associate
0 Kudos
103

Running this query, we expect the calculation scenario return some rows; however, it does not.

If it runs on Hana Cloud, it works as expected, but not in Hana2.

There is a filter in the second stage from the bottom of the calc scenario like: "expression": "not in (\"VALUE\", '''') or isNull(\"VALUE\")"

If we change it we get different results:

"not in (\"VALUE\", ''1'') or isNull(\"VALUE\")" returns some rows (expected) "in (\"VALUE\", ''1'') or isNull(\"VALUE\")" returns no rows (not expected)

 
SET SCHEMA TENANT_TEST;
 
DROP TABLE "src";
CREATE COLUMN TABLE "src"(
"Plant" NVARCHAR(5000),
"Account" NVARCHAR(5000),
"Col_202501" NVARCHAR(5000),
"Col_202502" NVARCHAR(5000),
"Col_202503" NVARCHAR(5000),
"Col_202504" NVARCHAR(5000),
"Col_202505" NVARCHAR(5000),
"Col_202506" NVARCHAR(5000),
"Col_202507" NVARCHAR(5000),
"Col_202508" NVARCHAR(5000),
"Col_202509" NVARCHAR(5000),
"Col_202510" NVARCHAR(5000),
"Col_202511" NVARCHAR(5000),
"Col_202512" NVARCHAR(5000)
) UNLOAD PRIORITY 5 AUTO MERGE;
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3014, 'VV311', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3012, 'VV121', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3014, 'VV300', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3012, 'VV083', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3014, 'VV093', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3012, 'VV120', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3014, 'VV122', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3012, 'VV317', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3014, 'VV082', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3012, 'EXP_FV', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3014, 'EXP_NS_PPV', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3012, 'EXP_TPC', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
INSERT INTO "src" ("Plant", "Account", "Col_202501", "Col_202502", "Col_202503", "Col_202504", "Col_202505", "Col_202506", "Col_202507", "Col_202508", "Col_202509", "Col_202510", "Col_202511", "Col_202512") 
VALUES (3012, 'EXP_BA', '', '', '', '', '', 0.6, 0.7, 0.8, 0.9, 1, 1.1, 1.2);
 
 
DROP VIEW "TENANT_TEST"."myView";
DROP CALCULATION SCENARIO "TENANT_TEST"."myCalc";
CREATE CALCULATION SCENARIO "TENANT_TEST"."myCalc" USING '[{"__CalculationNode__": true,"name": "DataSource_1","operation": {"__TableDSNodeData__": true,"source": {"__IndexName__": true,"schema": "TENANT_TEST","name": "src"},"dataSourceFlags": 0},"attributeVec": [{"__Attribute__": true,"name": "Plant","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Account","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202501","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202502","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202503","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202504","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202505","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202506","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202507","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202508","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202509","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202510","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202511","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202512","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0}]},{"__CalculationNode__": true,"name": "ROOT_1","isDefaultNode": true,"inputVec": [{"__Input__": true,"name": "DataSource_1"}],"operation": {"__ProjectionOpNodeData__": true},"attributeVec": [{"__Attribute__": true,"name": "Plant","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Account","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202501","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202502","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202503","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202504","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202505","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202506","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202507","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202508","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202509","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202510","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202511","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202512","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0}]},{"__CalculationNode__": true,"name": "PROJECTION_2","inputVec": [{"__Input__": true,"name": "ROOT_1"}],"operation": {"__ProjectionOpNodeData__": true},"attributeVec": [{"__Attribute__": true,"name": "Plant","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Account","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202501","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202502","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202503","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202504","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202505","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202506","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202507","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202508","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202509","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202510","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202511","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202512","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0}]},{"__CalculationNode__": true,"name": "UNPIVOT_3_1","inputVec": [{"__Input__": true,"name": "PROJECTION_2"}],"operation": {"__ProjectionOpNodeData__": true},"attributeVec": [{"__Attribute__": true,"name": "Col_202501_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202501\")"},{"__Attribute__": true,"name": "Col_202502_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202502\")"},{"__Attribute__": true,"name": "Col_202503_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202503\")"},{"__Attribute__": true,"name": "Col_202504_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202504\")"},{"__Attribute__": true,"name": "Col_202505_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202505\")"},{"__Attribute__": true,"name": "Col_202506_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202506\")"},{"__Attribute__": true,"name": "Col_202507_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202507\")"},{"__Attribute__": true,"name": "Col_202508_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202508\")"},{"__Attribute__": true,"name": "Col_202509_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202509\")"},{"__Attribute__": true,"name": "Col_202510_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202510\")"},{"__Attribute__": true,"name": "Col_202511_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202511\")"},{"__Attribute__": true,"name": "Col_202512_1","role": 1,"datatype": {"__DataType__": true,"type": 83,"length": 5000,"sqlType": 11,"sqlLength": 5000},"attributeType": 1,"expression": "string(\"Col_202512\")"},{"__Attribute__": true,"name": "Plant","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Account","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202501","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202502","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202503","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202504","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202505","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202506","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202507","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202508","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202509","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202510","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202511","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Col_202512","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0}]},{"__CalculationNode__": true,"name": "UNPIVOT_3_2","inputVec": [{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202501_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202501","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202502_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202502","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202503_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202503","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202504_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202504","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202505_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202505","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202506_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202506","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202507_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202507","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202508_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202508","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202509_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202509","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202510_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202510","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202511_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202511","length": 0}]},{"__Input__": true,"name": "UNPIVOT_3_1","mappingVec": [{"__Mapping__": true,"type": 1,"target": "Plant","source": "Plant","length": 0},{"__Mapping__": true,"type": 1,"target": "Account","source": "Account","length": 0},{"__Mapping__": true,"type": 1,"target": "VALUE","source": "Col_202512_1","length": 0},{"__Mapping__": true,"type": 0,"target": "KEY","value": "202512","length": 0}]}],"operation": {"__UnionOpNodeData__": true},"attributeVec": [{"__Attribute__": true,"name": "KEY","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 11,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "VALUE","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 11,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Plant","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Account","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0}]},{"__CalculationNode__": true,"name": "FILTER_4","inputVec": [{"__Input__": true,"name": "UNPIVOT_3_2"}],"operation": {"__ProjectionOpNodeData__": true},"attributeVec": [{"__Attribute__": true,"name": "KEY","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 11,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "VALUE","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 11,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Plant","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Account","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0}],"filterObject": {"__Filter__": true,"type": 0,"expression": "not in (\"VALUE\", '''') or isNull(\"VALUE\")"}},{"__CalculationNode__": true,"name": "FILTER_5","inputVec": [{"__Input__": true,"name": "FILTER_4"}],"operation": {"__ProjectionOpNodeData__": true},"attributeVec": [{"__Attribute__": true,"name": "KEY","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 11,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "VALUE","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 11,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Plant","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0},{"__Attribute__": true,"name": "Account","role": 1,"datatype": {"__DataType__": true,"type": 83,"sqlType": 37,"sqlLength": 5000},"attributeType": 0}],"filterObject": {"__Filter__": true,"type": 0,"expression": "in (\"Account\", ''VV311'', ''VV121'', ''VV300'', ''VV083'', ''VV093'', ''VV120'', ''VV122'', ''VV317'', ''VV082'', ''EXP_FV'', ''EXP_NS_PPV'', ''EXP_TPC'', ''EXP_BA'')"}}]' WITH PARAMETERS ( 'FORCE_CREATE'='1' );
 
CREATE COLUMN VIEW "TENANT_TEST"."myView" WITH PARAMETERS (indexType=11,
'PARENTCALCINDEXSCHEMA'='TENANT_TEST','PARENTCALCINDEX'='myCalc','PARENTCALCNODE'='FILTER_5');
 
select * from "myView";

 

Accepted Solutions (0)

Answers (1)

Answers (1)

jan_zwickel
Product and Topic Expert
Product and Topic Expert
0 Kudos

 

I don't understand on which assumptions you expectation is based on. Perhaps you can elaborate a bit. Looking at your insert statements column Col_202501 is filled with empty strings.

If you run a SELECT statement directly on the table like:

select * from "src" where not "Col_202501" in ('1') or "Col_202501" is Null

the not=1 returns records

If you run

select * from "src" where "Col_202501" in ('1') or "Col_202501" is Null

Col_202501 is neither 1 nor null so no records are returned.

This column is mapped to VALUE in your calculation view and therefore I would not expect 

in (\"VALUE\", ''1'') or isNull(\"VALUE\")"

to return records.

alimiri71
Associate
Associate
0 Kudos
Thanks for the reply. If we create another view based on 'FILTER_4' like "CREATE COLUMN VIEW "TENANT_TEST"."myView4" WITH PARAMETERS (indexType=11, 'PARENTCALCINDEXSCHEMA'='TENANT_TEST','PARENTCALCINDEX'='myCalc','PARENTCALCNODE'='FILTER_4');" we have the records from the previous stage which many of them match with the stage 5 criteria. Also, running the script in Hana Cloud returns the expected rows, different from Hana2.
jan_zwickel
Product and Topic Expert
Product and Topic Expert
0 Kudos
If I understand you correctly, filter 4 ("not in (\"VALUE\", '''') or isNull(\"VALUE\")") is returning records. Assuming a pretty direct mapping in your scenario this would mean that a statement like select * from "src" where ("Col_202501" not in ('') or "Col_202501" is null) would need to return records which looks wrong to me with the data that are provided by you. To me it looks surprising that you exclude '' but include nulls. I would rather expect "in (\"VALUE\", '''') or isNull(\"VALUE\")" instead of "not in (\"VALUE\", '''') or isNull(\"VALUE\")" Anyway, if you have a behavior difference between HANA Cloud and HANA 2, please open an incident.