Inner join

[Former Member]

Hi All,

I have 2 analaytic view.

For eg:

Analytic view (AN1):

MANDT

MATNR

MTART

BREGW

in Anaytic view 2(AN2):

MATNR

MBLNR

LGORT

DMBTR

This analytic view has created in the same schema "SCH1"  an package name "P1".

I need to create a calculation view using SQL script to combine the two analytic view and i have wrote the statement

VAR_OUT = SELECT MA1.MANDT, MA1.MATNR,MA1.MTART,MG1.MBLNR,MG1.LGORT,MG1.DBTMR FROM "_SYS_BIC"."SCH1.P1/AN1" AS MA1

INNER JOIN "_SYS_BIC"."SCH1.P1/AN2" AS MG1 ON MA1."MATNR" = MG1."MATNR";

im getting a error:MDXRuntime: Internal deployment of object failed: Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "WHERE": line 7 col 47 (at pos 449)nSet Schema DDL statement: set schema "HECSCHEM800"nTable Type DDL: create type "_SYS_BIC"."inf165157/CAL_SQL/proc/tabletype/VAR_OUT" as table ("MANDT" NVARCHAR(3), "MATNR" NVARCHAR(18), "MTART" NVARCHAR(4), "MBLNR" NVARCHAR(15), "LGORT" NVARCHAR(15), "BRGEW" DECIMAL(13,3), "DMBTR" DECIMAL(13,3))nProcedure DDL: create procedure "_SYS_BIC"."P1/CAL_SQL/proc" ( OUT var_out "_SYS_BIC"."P1/CAL_SQL/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t var_out = SELECT MA1.MANDT,MA1.MATNR,MA1.MTART,MG1.MBLNR,MG1.LGORT,n t MA1.BRGEW,MG1.DMBTR FROM n t "_SYS_BIC"."P1/AN1" AS MA1 INNER JOIN n t "_SYS_BIC"."P1/AN2" AS MG1 WHERE MA1."MATNR" = MG1."MATNR";n t nnEND /********* End Procedure Script ************/nVersion: 42n

I created the object with same data type in define output parameter .

Could you please guide me how to resolve this? or give a sample statement to join two analytic view using SQL statement not with CE statement?.

Thanks in advance.

[Former Member]

Hi Ravi,

Im not using AWS. Please correct me , i think cloudshare and AWS are totally different.... i

I checked all the possibiliies in SQL as you said but that doesnt help.... if  this issue is related to bug in rev 31?

As per your statement, i guess its yes...

Can you please look into the code once again... My mistake i may be done wrong...

[former_member184768]

Hi Gene,

My bad. I use own HANA system so not very much aware of Cloudshare or AWS system.

Your code looks very much fine. I can't think of a reason why it is not working. As I already mentioned, similar code and Calc view works fine for me.

Can you try with one column at a time and increase to 2, then 3 and so on.. from the select statement to identify if any particular column is causing the issue. You can try the same with the where and group by conditions. Just to isolate if there is any issue with any column / where condition / group by field.

Having said that I'd still suggest the upgrade. Please contact the administrator (if possible) to check the feasibility of the upgrade.

Regards,

Ravi

[Former Member]

Hi Ravi,

1) How am i supposed to update the latest version in cloudshare?

2) the Alias name is given correctly AMOUNT_SOLD and QUANTITY_SOLD are the measures in the analytical view. AMOUNT, QTY are the ALIAS names in the calculation view.

Thanks!

[former_member184768]

Hi Gene,

Please refer to the following documents:

http://scn.sap.com/docs/DOC-30980

You can also download the latest client. The above link and for client download is available on Developer center.

http://scn.sap.com/community/developer-center/hana

Regards,

Ravi

[Former Member]

Hi Ravi,

1. The query on each individual analytic view is works fine. The QUANTITY and AMOUNT are existing measure in the analytic view.

2. Im using the cloudshare and its version is 31

TIA

[former_member184768]

Hi Gene,

1) I think on cloudshare you can upgrade to the latest revision.

2) Did I get you correct, are you saying AMOUNT and QTY are existing measures along with AMOUNT_SOLD and QUANTITY_SOLD. If so, you might want to change the AS alias to some other name like AMOUNT_1 and QUANTITY_1.

Regards,

Ravi

[Former Member]

Hi Ravi,

I used the same code but im getting error. PFA screenshot for ur reference.

1. Please check the code wether i have done any mistake?

2. Is this any way related to revision issue?

3. Is this related to define output parameter (Var_out) issue?

[former_member184768]

Hi Gene,

Couple of quick checks:

1) Did you try running the SQL statement directly in SQL window to see if it delivers any output.

2) Did you check that the data types defined in VAR_OUT structure matches exactly with the source column data types and lengths.

I could create a calc view with SQL join in my system. I am on revision 36.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thanks for the quick response.

I tried runnig the SQL statement directly, but it throws an error stating

"feature not supported: not allowed over OLAP view: search without aggregation or grouping by expression other than view column"

The data type in VAR_OUT matches exactly with the source.

[former_member184768]

Typically you get this error message when the Measures are not defined with SUM() function and Attributes are not included in GROUP BY clause.

Can you please check if the above conditions are satisfied. Are all the group by clause fields defined as attributes and both the columns defined with SUM are measures.

Regards,

Ravi

[Former Member]

Hi Ravi,

All conditions that you have mentioned are satisfied already.

Thanks

[former_member184768]

Last two suggestions:

1) Please check you can execute any query against the analytic view, even without join. Please check if the individual queries on each of the Analytic views work fine. Also ensure that AMOUNT and QTY are not existing measures in the Analytic view.

2) Can you please check if you can upgrade to recent revision, if you are not already on it.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thanks for the code...

I want to check how the join statement using SQL works....

If possible can you provide me the code for joining 2 analytic view using sql statement.

[former_member184768]

Hi Gene,

following code works fine for me:

select a."ZPROD_ID", b."ZCAT_ID", sum(b.zrowcount) from

"_SYS_BIC"."hanatest/ZPROD" a

, "_SYS_BIC"."hanatest/ZCAT" b

where a."ZCAT_ID" = b."ZCAT_ID"

and b."ZCAT_ID" = '03'

group by a."ZPROD_ID", b."ZCAT_ID"

Regards,

Ravi

[Former Member]

Hi Ravi,

Thanks for your valuable inputs!!!!

The code which i tried is mentioned above in this chain. If you tried can you please post the code?

Is there any possibilities to check whether this code goes through calc engine or SQL engine?

[former_member184768]

Please find the code below. It has been modified from the original code to simplify.

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

BEGIN

            v_qty = CE_OLAP_VIEW

                   ( "_SYS_BIC"."hanatest/ZQTY"

                   , ["SOURCE"

                   , "STORE"

                   , "CALDAY"

                   , "PRODUCT"

                   , "Z_QTY_1"

                   ]);

            v_rate = CE_OLAP_VIEW

                   ( "_SYS_BIC"."hanatest/ZRATE"

                   , ["SOURCE"

                   , "STORE"

                   , "PRODUCT"

                   , "CALDAY"

                   , "ZPRICE"

                   ]);

            vag_qty = CE_AGGREGATION

                   ( :v_qty

                   , [sum("Z_QTY_1") AS "Z_QTY_1"]

                   , ["SOURCE"

                   , "STORE"

                   , "PRODUCT"

                   ]);

            vag_rate = CE_AGGREGATION

                   ( :v_rate

                   , [sum("ZPRICE") AS "ZPRICE"]

                   , ["SOURCE"

                   , "STORE"

                   , "PRODUCT"

                   ]);

       vjv_qty_rate = CE_JOIN

                   ( :vag_rate

                   , :vag_qty

                   , ["SOURCE"

                   , "STORE"

                   , "PRODUCT"]

                   , ["SOURCE"

                   , "STORE"

                   , "PRODUCT"

                   , "ZPRICE"

                   , "Z_QTY_1"

                   ]);

            var_out = CE_PROJECTION

                   ( :vjv_qty_rate

                   , ["SOURCE"

                   , "STORE"

                   , "PRODUCT"

                   , "ZPRICE"

                   , "Z_QTY_1"

                   , CE_CALC('"Z_QTY_1" * "ZPRICE"', decimal(17,2)) AS "Z_VALUE"]

                   );

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

Regards

Ravi

[Former Member]

Hi Ravi,

Initially, i used the CE functions only and it works.

Later, want to check how come the CE functions is better than SQL. So that created using SQL. Its for testing purpose only.

Could you please guide me hoe the CE function will be efficicent from performance than SQL ?

Can we able to join the analytiv view using SQL?

Is there any possbilities to check the code for the calculation view using graphical?

TIA

[former_member184768]

Hi Gene,

CE functions are better than SQL because of the fact that these are interpreted directly by the Calc Engine and can also be optimized by the engine by selecting only required columns. SQL goes through the SQL engine and has additional overheads. It also fetches data and performs calculation even though not required by the front end.

Hence it is always recommended to use CE functions over SQL unless there is no alternative like using a BETWEEN join condition or complex logic implementation.

Although I never tried joining Analytic views in SQL, technically it should be possible. But a word of caution, it is advised to avoid mix of SQL and CE functions (used in Analytic view creation) to implement additional views. Hence unless really required, I'd suggest avoid it. I really doubt it will improve performance over SQL joins on the underlying tables.

Code for Calc views using Graphical options is something even I am searching for. But to my knowledge it is bit complex to decipher the code written and hence I didn't investigate much into that direction.

Regards,

Ravi

[Former Member]

Hi All,

Thanks for your inputs!!!!

I changed the code as you said and even i got the below error.

And i checked by joining two tables using SQL statement,here i can able do the datapreview and when i checked with 2 analytic view i cannot able to join.

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: SqlScript: Could not derive table type for variable "VAR_OUT" (SQL error: feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping by expression other than view column)nSet Schema DDL statement: nType DDL: create type "_SYS_BIC"."0000000-pkg/TEST_CAL1/proc/tabletype/VAR_OUT" as table ("SHOP_FACTS_ID" INTEGER, "ARTICLE_ID" INTEGER, "WEEK_ID" INTEGER, "SHOP_ID" INTEGER, "COLOR_CODE" INTEGER, "MARGIN" DECIMAL(34,3), "AMOUNT" DECIMAL(34,3), "QTY" INTEGER)nProcedure DDL: create procedure "_SYS_BIC"."0000000-pkg/TEST_CAL1/proc" ( OUT var_out "_SYS_BIC"."0000000-pkg/TEST_CAL1/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t var_out = SELECT AN1.SHOP_FACTS_ID,n t AN1.ARTICLE_ID,n t AN1.WEEK_ID,n t AN1.SHOP_ID,n t AN1.COLOR_CODE,n t  t AN2.MARGIN,n t  t SUM(AN1.AMOUNT_SOLD) AS AMOUNT,n t  t SUM(AN2.QUANTITY_SOLD) AS QTYn t  t FROM "_SYS_BIC"."0000000-pkg/AN_CA_TEST1" AS AN1, "_SYS_BIC"."0000000-pkg/AN_CA_TEST2" AS AN2n t WHERE AN1.ARTICLE_ID = AN2.ARTICLE_ID ANDn t AN1.SHOP_FACTS_ID = AN2.SHOP_FACTS_ID AND n t AN1.WEEK_ID = AN2.WEEK_ID andn t AN1.SHOP_ID = AN2.SHOP_IDn t GROUP BY AN1.SHOP_FACTS_ID,n t AN1.ARTICLE_ID,n t AN1.WEEK_ID,n t AN1.SHOP_ID,n t AN1.COLOR_CODE,n t  t AN2.MARGIN;nnEND /********* End Procedure Script ************/nVersion: 66n

please find below code and i created the output parameter with same structure.

BEGIN

      var_out = SELECT AN1.SHOP_FACTS_ID,

      AN1.ARTICLE_ID,

      AN1.WEEK_ID,

      AN1.SHOP_ID,

      AN1.COLOR_CODE,

            AN2.MARGIN,

            SUM(AN1.AMOUNT_SOLD) AS AMOUNT,

            SUM(AN2.QUANTITY_SOLD) AS QTY

            FROM "_SYS_BIC"."0000000-pkg/AN_CA_TEST1" AS AN1, "_SYS_BIC"."0000000-pkg/AN_CA_TEST2" AS AN2

      WHERE AN1.ARTICLE_ID = AN2.ARTICLE_ID AND

      AN1.SHOP_FACTS_ID = AN2.SHOP_FACTS_ID AND

      AN1.WEEK_ID = AN2.WEEK_ID and

      AN1.SHOP_ID = AN2.SHOP_ID

      GROUP BY AN1.SHOP_FACTS_ID,

      AN1.ARTICLE_ID,

      AN1.WEEK_ID,

      AN1.SHOP_ID,

      AN1.COLOR_CODE,

            AN2.MARGIN;

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

please tell can we able 2 join two analytic view using SQL statement and could tou please tell me how to join with eample

[former_member184768]

Hi Gene,

Just out of curiosity, why are you using SQL script and not CE functions. It would be easier to use CE_OLAP_VIEW for the Analytic views and then CE_JOIN to join them.

This way it will be even more efficient from performance perspective.

Regards,

Ravi

[Former Member]

Hi Gene,

This error occurs normally when there is a problem with the Output parameters.

Make sure that your output parameters match 1:1 with your columns in the select statement. (including their data types and sizes). [Name matching doesn't work, they have to be in right order.]

Also, go to the output node of Calculation view and add some columns in the final output structure (as attribute/measures).

Hope this helps,

[patrickbachmann]

Try defining each selection like this;

SELECT

MA1.MANDT AS "MyDate",

MA1.MATNR AS "MaterialNumber", 

etc etc

FROM "_SYS_BIC"."SCH1.P1/AN1" MA1 <----- REMOVE THE AS MA1

Then on the outputs you would define a field for each;

"MyDate" NVARCHAR 10

"MaterialNumber" NVARCHAR 8

etc

etc

At least this is how it's working for my code I've done.  I hope it helps.

[patrickbachmann]

Oh also example of where clause I use that works;

WHERE MA1.MATNR = '12345'

The difference being I don't put a quotation around MATNR as your example seems to show.