cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Help with Ce_aggregation function

Former Member

on ‎11-15-2011 8:47 PM

0 Kudos

Hi All ,

I was trying to use CE functions & was getting a syntax error . Any help is grealty appreciated . I have put in the sql which works & the CE funstion syntax

Sql : This works

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

BEGIN

var_out = select customer_key, mpg_id, sales_structure_key,primary_rep_flag, max(end_date) as end_date, COUNT(*)AS CNT

from "_SYS_BIC"."zgsr/SALES_ACCOUNT_AT"

where end_date >= begin_date

group by customer_key, mpg_id, sales_structure_key,primary_rep_flag, end_date;

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

CE function :

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

BEGIN

var_out = CE_AGGREGATION ( "_SYS_BIC"."zgsr/SALES_ACCOUNT_AT",

["customer_key",

"mpg_id",

"sales_structure_key",

"primary_rep_flag",

MAX ("end_date") AS "end_date",

count ("customer_key") AS "CNT",

["customer_key","mpg_id","sales_structure_key",

"primary_rep_flag","end_date"],

'"end_date" >= "begin_date"');

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

Error :

[2011-11-11 13:17:14 725]

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

System Name:HD1:MSPLPS101.CORP.MEDTRONIC.COM

User Name:SYSTEM

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

[2011-11-11 13:17:14 725]Activation Job started

[2011-11-11 13:17:14 725]Inside Deployment Job for Activation process................

[2011-11-11 13:17:14 953]

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

[2011-11-11 13:17:14 953]Activation started for model: zgsr.TEST

[2011-11-11 13:17:15 433]Start loading dependent objects

[2011-11-11 13:17:15 433]Loading dependent objects finished

[2011-11-11 13:17:15 729]Validation of Calculation View TEST started

[2011-11-11 13:17:15 730]Validation of Calculation View TEST successfully finished

[2011-11-11 13:17:15 730]Activate Calculation View :TEST(zgsr)

[2011-11-11 13:17:15 746]Prepare and execute DDL ...

[2011-11-11 13:17:15 746] successfully created

[2011-11-11 13:17:15 746]Server activation started...

[2011-11-11 13:17:16 473]Activation ID: 648

[2011-11-11 13:17:16 473]Repository: Encountered an error in repository runtime extension

[2011-11-11 13:17:16 473]Model deployment started: zgsr/TEST timestamp: 2011-11-11,19:17:16.351

[2011-11-11 13:17:16 473]Method deployCalcView::startDocument(ModelCache *, string *) start, timestamp: 2011-11-11,19:17:16.352

[2011-11-11 13:17:16 473]Version: 21

[2011-11-11 13:17:16 473]Method deployCalcView::startDocument(ModelCache *, string *) end, timestamp: 2011-11-11,19:17:16.352

[2011-11-11 13:17:16 474]Method deployCalcView::endDocument() start, timestamp: 2011-11-11,19:17:16.353

[2011-11-11 13:17:16 474]Method deployCalcView::deploy() start, timestamp: 2011-11-11,19:17:16.353

[2011-11-11 13:17:16 474]Method deployCalcView::createConsumptionXML(XMLInfo *) start, timestamp: 2011-11-11,19:17:16.353

[2011-11-11 13:17:16 474]Method deployCalcView::createConsumptionXML(XMLInfo *) end, timestamp: 2011-11-11,19:17:16.353

[2011-11-11 13:17:16 474]Calculation View: SYSBIC/zgsr/TEST

[2011-11-11 13:17:16 474]Consumption XML: <?xml version="1.0" encoding="UTF-8" standalone="no"?><cubeSchema defaultLanguage="EN" defaultSchema="_SYS_BIC" operation="createHanaCube" version="3"><sqlScriptView name="zgsr/TEST" schema="_SYS_BIC" sqlScriptFunctionSchema="_SYS_BIC" sqlScriptFunctionName="zgsr/TEST/proc" registerViewForApsFlag="true"><dimensionAttributes><dimensionAttribute name="CUSTOMER_KEY$attr"><columnMappings><columnMapping column="CUSTOMER_KEY" name="CUSTOMER_KEY" type="key"/></columnMappings></dimensionAttribute><dimensionAttribute name="MPG_ID$attr"><columnMappings><columnMapping column="MPG_ID" name="MPG_ID" type="key"/></columnMappings></dimensionAttribute><dimensionAttribute name="SALES_STRUCTURE_KEY$attr"><columnMappings><columnMapping column="SALES_STRUCTURE_KEY" name="SALES_STRUCTURE_KEY" type="key"/></columnMappings></dimensionAttribute><dimensionAttribute name="PRIMARY_REP_FLAG$attr"><columnMappings><columnMapping column="PRIMARY_REP_FLAG" name="PRIMARY_REP_FLAG" type="key"/></columnMappings></dimensionAttribute></dimensionAttributes><measures><measure column="CNT" name="CNT" aggregationType="sum"/></measures></sqlScriptView></cubeSchema>

[2011-11-11 13:17:16 475]Method deployCalcView::deployScriptCV() start, timestamp: 2011-11-11,19:17:16.353

[2011-11-11 13:17:16 475]Alter Session DDL: alter session set current_schema = "SYSTEM"

[2011-11-11 13:17:16 475]Drop View DDL: drop view "_SYS_BIC"."zgsr/TEST"

[2011-11-11 13:17:16 475]Drop Procedure DDL: drop procedure "_SYS_BIC"."zgsr/TEST/proc"

[2011-11-11 13:17:16 475]Drop Type DDL: drop type "_SYS_BIC"."zgsr/TEST/proc/tabletype/VAR_OUT"

[2011-11-11 13:17:16 475]Create Table Type DDL: create type "_SYS_BIC"."zgsr/TEST/proc/tabletype/VAR_OUT" as table ("CUSTOMER_KEY" INTEGER, "MPG_ID" NVARCHAR(10), "SALES_STRUCTURE_KEY" INTEGER, "PRIMARY_REP_FLAG" NVARCHAR(1), "END_DATE" DATE, "CNT" INTEGER)

[2011-11-11 13:17:16 475]Create Procedure DDL: create procedure "_SYS_BIC"."zgsr/TEST/proc" ( OUT var_out "_SYS_BIC"."zgsr/TEST/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as n /********* Begin Procedure Script ***********/ n BEGIN n t ttvar_out = CE_AGGREGATION ( "_SYS_BIC"."zgsr/SALES_ACCOUNT_AT",nttttt ["customer_key",nttttt "mpg_id",nttttt "sales_structure_key",nttttt "primary_rep_flag",nttttt MAX ("end_date") AS "end_date",nttttt count ("customer_key") AS "CNT",nttttt ["customer_key","mpg_id","sales_structure_key",nttttt "primary_rep_flag","end_date"],nttttt '"end_date" >= "begin_date"');nEND /******** End Procedure Script ************/

[2011-11-11 13:17:16 475]Create Procedure failed: SQL: sql syntax error: incorrect syntax near "_SYS_BIC": line 4 col 33 (at pos 266)2011-11-11,19:17:16.360

[2011-11-11 13:17:16 476]Method deployCalcView::deploy() end, timestamp: 2011-11-11,19:17:16.360

[2011-11-11 13:17:16 476]Method deployCalcView::endDocument() end, timestamp: 2011-11-11,19:17:16.360

[2011-11-11 13:17:16 476]Post deploy processing started: zgsr/TEST timestamp: 2011-11-11,19:17:16.361

[2011-11-11 13:17:16 476]Model deployment ended: zgsr/TEST timestamp: 2011-11-11,19:17:16.361

[2011-11-11 13:17:16 476]MDXRuntime: Internal deployment of object failed: Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "_SYS_BIC": line 4 col 33 (at pos 266)nAlter Session DDL statement: alter session set current_schema = "SYSTEM"nTable Type DDL: create type "_SYS_BIC"."zgsr/TEST/proc/tabletype/VAR_OUT" as table ("CUSTOMER_KEY" INTEGER, "MPG_ID" NVARCHAR(10), "SALES_STRUCTURE_KEY" INTEGER, "PRIMARY_REP_FLAG" NVARCHAR(1), "END_DATE" DATE, "CNT" INTEGER)nProcedure DDL: create procedure "_SYS_BIC"."zgsr/TEST/proc" ( OUT var_out "_SYS_BIC"."zgsr/TEST/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as n /********* Begin Procedure Script ***********/ n BEGIN n t ttvar_out = CE_AGGREGATION ( "_SYS_BIC"."zgsr/SALES_ACCOUNT_AT",nttttt ["customer_key",nttttt "mpg_id",nttttt "sales_structure_key",nttttt "primary_rep_flag",nttttt MAX ("end_date") AS "end_date",nttttt count ("customer_key") AS "CNT",nttttt ["customer_key","mpg_id","sales_structure_key",nttttt "primary_rep_flag","end_date"],nttttt '"end_date" >= "begin_date"');nEND /******** End Procedure Script ************/nVersion: 21n

[2011-11-11 13:17:16 477]Error in server side activation : Server activation failed

[2011-11-11 13:17:16 779]Activation ended for model: zgsr.TEST

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
‎11-16-2011 3:51 AM
0 Kudos

Looks Syntax error, should CE functions on Variable data type depend on use.

Tomas is give nice document for SQL scripts

Thanks

Rao

Show replies
Show replies
Former Member
‎11-15-2011 8:50 PM
0 Kudos

Sorry , the Formating is bad . i will try to put in a document .

Show replies
Show replies
tomas-krojzl
Active Contributor
‎11-15-2011 9:31 PM
0 Kudos

Hello,

please read SQL Script Guide...

https://service.sap.com/~sapidb/011000358700000604932011

In your query you are directly referencing table - this is not allowed.

You are having following operators to access data objects:

CE_COLUMN_TABLE - classical table

CE_JOIN_VIEW - attribute view

CE_OLAP_VIEW - analytic view

CE_CALC_VIEW - calculation view

You must always access tables and view using these operators.

In case you are reading data from analytic view - you always provide dimensions and required key figures. Result of such query are dimensions and aggregated key figures.

See example in guide:

out = CE_OLAP_VIEW("OLAP_view", ["DIM1", "KF"]);
Is equivalent to the following SQL:
out = select dim1, SUM(kf) FROM OLAP_view GROUP BY dim1;

Tomas

tomas-krojzl
Active Contributor
‎11-15-2011 9:41 PM
0 Kudos

Hello,

now if you wish to suppress this aggregation you need to provide all dimensions..

Result of such operator is variable of type table. Then you can use this variable in all other operators - including CE_AGGREGATION.

For rewriting your example it is very important to understand definition of SALES_ACCOUNT_AT - is it attribute view or analytic view. What exactly is definition (all fields and their type), etc..

Tomas

Ask a Question