Writing Alias column using select query in Calc View using CE functions

[Former Member]

Hi All

  I am trying to derive a new column out of exisiting columns by using select query on CE function CE_CALC_VIEW as mentioned below

BEGIN

var_out =CE_CALC_VIEW("_SYS_BIC"."zhanajlr/CRM_JLR_CE_VISIT_CALCVIEW",["NAME_FIRST",

"PARTNER","TELNR_LONG","NAME_LAST","SMTP_ADDR","CITY1","COUNTRY",

"DESIGNATIONTEXT","INDUSTRYTEXT","AGE_GROUP","VISITID","VISITDATE",

"VISITPRODUCT","PRODUCTTYPE","PRODUCTMODEL","VISITTYPE","PRICE",

select count(VISITID) group by VISITPRODUCT as "VISITCOUNT"]);

END

but I am facing error as below  incorrect syntax near "select": line 8 col 1 (at pos 559)nSet Schema DDL statement: set schema Type DDL:

can we achieve same using calculated attributes  or I am missing any syntax , request suggetions in solving the problem.

Thanks

Santosh

[former_member184768 · Accepted solution]

I think you need to use CE_AGGREGATION function

Can you please try with CE_AGGREGATION ("_SYS_BIC"."zhanajlr/CRM_JLR_CE_VISIT_CALCVIEW", [count("VISITID" AS "VISITCOUNT"], ["VISITPRODUCT"]);

Please refer to page 30 of the SQL script manual for details.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi

   I have tried that option , there is one catch , you can't pass the calcview name directly ,we need to get the result data into the temporary table directly ,then we can use the variable as input for Aggregation.

TEMP = CE_CALC_VIEW("_SYS_BIC"."zhanajlr/CRM_JLR_CE_VISIT_CALCVIEW",["NAME_FIRST",

"PARTNER","TELNR_LONG","NAME_LAST","SMTP_ADDR","CITY1","COUNTRY",

"DESIGNATIONTEXT","INDUSTRYTEXT","AGE_GROUP","VISITID","VISITDATE",

"VISITPRODUCT","PRODUCTTYPE","PRODUCTMODEL","VISITTYPE","PRICE"]);

var_out = CE_AGGREGATION (:TEMP,

[ count("VISITID") AS "VISITCOUNT"], ["VISITPRODUCT"]);

But the problems with the CE_AGGREGATION functions are

1.what ever we want to show it in output it needs to be part of Group by clause  here it is in the square brackets,otherwise we can't get the column in the output.

2.count(DISTINCT "VISITID") is not supported feature, if I want to avoid duplicates ,how can I achieve that?

3.I tried the below to use nested concept,meaning using CE_AGGREGATION as part of CE_CALCVIEW  as below

var_out = CE_CALC_VIEW("_SYS_BIC"."zhanajlr/CRM_JLR_CE_VISIT_CALCVIEW",

["PARTNER",CE_AGGREGATION(:TEMP, [count("VISITID") AS "VISITCOUNT"], ["VISITPRODUCT"])]);

but it is also not working .

Request your suggetions.

Thanks

Santosh Varada

[former_member184768 · Accepted solution]

Hi Santosh,

I haven't tried this myself, but one suggestion is to use two different CALC_VIEWs. One for all the columns and one for count. Then using the CE_JOIN you can join both the views.

As mentioned earlier, I haven't tried this myself, but see if it can work.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi

  I tried as per your suggestion it is working, but the problem of duplicate rows I could not resolve even at this stage,

Even though the Join gives result, it seems to be a complex data model as I have to built Attribute views,calculation views on top of those attribute views and again one more calculation view on top of existing calculation views,no.of layers are being increased, if  the aggregation can be achieved using any of the CE_CALC functions we can reduce one layer.Also in the aggregation or in any of the CE functions we can't use DISTINCT function it seems.Request your thoughts around this.

Thanks

Santosh Varada

[former_member184768 · Accepted solution]

Hi Santosh,

You are trying to implement a complex business logic using CE functions. Although these functions are optimized for performance as are executed directly by Calc Engine bypassing SQL Parser and Engine, I personally feel they do not provide complex functionalities like correlated joins (nested joins) or complex restrictions.

Your requirement may not be fulfilled in a simple way, but you can try the combination of SQL views (for count distinct) and CE views.

It may be very performance optimized, but till the CE functions mature (which I am sure they will), we will have to use the workarounds.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi

Yes , I agree with you  ,Hope there would be more functionalities supporting with CE functions,otherwise we can't get the whole advantage I believe,because in real-time most of the requirements would be complex.

Thanks for patiently answering my questions,may be some questions look silly .

Thanks

Santosh Varada