Using CE_JOIN with two join fields?

[BenedictV]

Hi,

I am trying to join two tables using the CE_JOIN function. The SQL statement looks like this:

AIRPORT_LOC =

Select a."MANDANT", a."AIRPORT", a."MASTERCITY", b."NAME", a."COUNTRY", b."TIME_ZONE"

   FROM "SFLIGHT"."SCITAIRP" as a

       INNER JOIN "SFLIGHT"."SAIRPORT" as b

            ON a."MANDANT" = b."MANDT"

            AND a."AIRPORT" = b."ID"

       WHERE a."AIRPORT" = :AIRPORT_CODE;

All the examples that I see for CE_JOIN use a single join field. Can someone help me with a CE_JOIN function for the above SQL statement?

Thanks,

Benedict

[former_member184768 · Accepted solution]

Hi Benedict,

The CE_JOIN operator can also take multiple attributes as join attributes, but it expects all the join attributes of the same name.

The documentation specifies the following:

CE_JOIN

operator calculates a natural (inner) join of the given pair of tables on a list of join attributes. For each pair of join attributes, only one attribute will be in the result. Optionally, a projection list of attribute names can be given to restrict the output to the given attributes. Finally, the plan operator requires each pair of join attributes to have identical attribute names. In case of join attributes having different names, one of them must be renamed prior to the join.

To rename the attribute (in your case AIRPORT or ID) you can use CE_PROJECTION where you can provide ALIAS name to the attribute.

The filtering specified in the WHERE clause can be done with CE_CALC.

Regards,

Ravi

[former_member184768 · Accepted solution]

May be you can try something like this:

t_scitairp = CE_COLUMN_TABLE("SFLIGHT"."SCITAIRP", ["MANDANT", "AIRPORT", "MASTERCITY", "COUNTRY"]);

t_sairport = CE_COLUMN_TABLE("SFLIGHT"."SAIRPORT", ["MANDT", "ID", "NAME", "TIME_ZONE"]);

tp_scitairp = CE_PROJECTION(:t_scitairp, ["MANDANT" as "MANDT", "AIRPORT" as "ID", "MASTERCITY", "COUNTRY"], '"AIRPORT" = '':airport_code''');

airport_loc = CE_JOIN(:tp_scitairp, :t_sairport, ["MANDT", "ID"], ["MANDT", "ID", "MASTERCITY", "COUNTRY", "NAME", "TIME_ZONE"]);

Regards,

Ravi

[BenedictV · Accepted solution]

hi Ravi,

I used the code that you gave me with minor modifications,

t_scitairp = CE_COLUMN_TABLE("SFLIGHT"."SCITAIRP",
        ["MANDANT", "AIRPORT", "MASTERCITY", "COUNTRY"]);

tp_scitairp = CE_PROJECTION(:t_scitairp,
       ["MANDANT" as "MANDT", "AIRPORT" as "ID", "MASTERCITY", "COUNTRY"]);

tp2_scitairp = CE_PROJECTION(:tp_scitairp,
       [ "MANDT", "ID", "MASTERCITY", "COUNTRY"],
       '"ID" = :AIRPORT_CODE' );
      
t_sairport = CE_COLUMN_TABLE("SFLIGHT"."SAIRPORT",
       ["MANDT", "ID", "NAME", "TIME_ZONE"]);
      
AIRPORT_LOC = CE_JOIN(:tp2_scitairp, :t_sairport,
      ["MANDT", "ID"],
      ["MANDT", "ID", "MASTERCITY", "NAME", "COUNTRY", "TIME_ZONE"]);

            
END;

it 'CHECKS' and ACTIVATES ok, but while calling the method I am getting the following error:

=> ...' in 5 ms 41 µs . SAP DBTech JDBC: [2048]: column store error: search table error: [34023] Internal error during instantiating calculation model

While debugging, the debugger terminates without any warning at the second CE_PROJECTION line where the restriction is used.

I went through the following link "http://scn.sap.com/thread/3318963", but I could'nt figure out how to solve the error.

Can you please help?

Thanks,

Benedict

[former_member184768 · Accepted solution]

Hi Benedict,

For some reason, you need to put the filter condition value in TWO single quotes as shown in my code.

'"AIRPORT" = '':airport_code'''

Regards,

Ravi

[BenedictV · Accepted solution]

I tried doing that too, but I get the following error:

Description Resource Path Location Type

Repository: Internal error during statement execution, please see the database error traces for additional details;error executing statement create procedure "_SYS_BIC"."myprojpackage.opensapproj.Models/SFLIGHT_PROCEDURE_CE_W3U3" (  in AIRPORT_CODE NVARCHAR(3) ,  out AIRPORT_LOC "_SYS_BIC"."myprojpackage.opensapproj.Models/SFLIGHT_PROCEDURE_CE_W3U3/tabletype/tt_AIRPORT_LOC"  ) language SQLSCRIPT sql security invoker reads sql data as CREATE PROCEDURE SFLIGHT_PROCEDURE_CE_W3U3 (nttin AIRPORT_CODE nVARCHAR(3),nttout AIRPORT_LOC tt_AIRPORT_LOCnt ) ntLANGUAGE SQLSCRIPTntSQL SECURITY INVOKER ntREADS SQL DATA ASnBEGINn/***************************** ntWrite your procedure logic n *****************************/nnt_scitairp = CE_COLUMN_TABLE("SFLIGHT"."SCITAIRP",nttttttt ["MANDANT", "AIRPORT", "MASTERCITY", "COUNTRY"]);nntp_scitairp = CE_PROJECTION(:t_scitairp, nttttttt["MANDANT" as "MANDT", "AIRPORT" as "ID", "MASTERCITY", "COUNTRY"]);nntp2_scitairp = CE_PROJECTION(:tp_scitairp, nttttttt[ "MANDT", "ID", "MASTERCITY", "COUNTRY"], nttttttt'"ID" = '':AIRPORT_CODE''' );ntttttttnt_sairport = CE_COLUMN_TABLE("SFLIGHT"."SAIRPORT", nttttttt["MANDT", "ID", "NAME", "TIME_ZONE"]);ntttttttnAIRPORT_LOC = CE_JOIN(:tp2_scitairp, :t_sairport, ntttttt["MANDT", "ID"], ntttttt["MANDT", "ID", "MASTERCITY", "NAME", "COUNTRY", "TIME_ZONE"]);nntttttt ntttttttttttttttttt tttttnEND;; sql syntax error: incorrect syntax near "SFLIGHT_PROCEDURE_CE_W3U3": line 1 col 315 (at pos 315) at qp_gram.y:34124 SFLIGHT_PROCEDURE_CE_W3U3.procedure /OpenSAPDemoProj/Models line 1 Repository Activation Problem

I am using the cloudshare system(REV 48). Is that an issue?

[former_member184768 · Accepted solution]

Hi Benedict,

Can you please post your complete code and also the table structures. I'll try to create the tables and procedure on my system.

Regards,

Ravi

[BenedictV · Accepted solution]

Hi Ravi,

Thanks for the efforts you are taking. I am using the SFLIGHT tables. Here's my code:

CREATE PROCEDURE SFLIGHT_PROCEDURE_CE_W3U3 (
  in AIRPORT_CODE nVARCHAR(3),
  out AIRPORT_LOC tt_AIRPORT_LOC
  )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
/*****************************
Write your procedure logic
*****************************/

t_scitairp = CE_COLUMN_TABLE("SFLIGHT"."SCITAIRP",
        ["MANDANT", "AIRPORT", "MASTERCITY", "COUNTRY"]);

tp_scitairp = CE_PROJECTION(:t_scitairp,
       ["MANDANT" as "MANDT", "AIRPORT" as "ID", "MASTERCITY", "COUNTRY"]);

tp2_scitairp = CE_PROJECTION(:tp_scitairp,
       [ "MANDT", "ID", "MASTERCITY", "COUNTRY"],
       '"ID" = '':AIRPORT_CODE''' );
      
t_sairport = CE_COLUMN_TABLE("SFLIGHT"."SAIRPORT",
       ["MANDT", "ID", "NAME", "TIME_ZONE"]);
      
AIRPORT_LOC = CE_JOIN(:tp2_scitairp, :t_sairport,
      ["MANDT", "ID"],
      ["MANDT", "ID", "MASTERCITY", "NAME", "COUNTRY", "TIME_ZONE"]);                  
END;

The table type:

CREATE TYPE tt_AIRPORT_LOC AS TABLE (

MANDT nVARCHAR(3),

ID nVARCHAR(3),

MASTERCITY nVARCHAR(20),

NAME nVARCHAR(25),

COUNTRY nVARCHAR(2),

TIME_ZONE nVARCHAR(6)

)

The call statement:

call "_SYS_BIC"."myprojpackage.opensapproj.Models/SFLIGHT_PROCEDURE_CE_W3U3" (

    AIRPORT_CODE => 'ACA', AIRPORT_LOC => ? );

Thanks,

Benedict

[Former Member · Accepted solution]

Hi Vasanth,

I believe to work on open SAP exercises, you should hve HANA rev 51 or higher. Please upgrade your HANA studio and check once.

Regards,

Venkat

[BenedictV · Accepted solution]

Hi Venkat,

Thats true if we have to import and work on the delivery units provided by openSAP. I am just trying to use the same code for my SFLIGHT tables. Like I said, activating the procedure is not a problem. But a 'Call' does not work and debug terminates abruptly with no message.

Thanks,

Benedict

[BenedictV · Accepted solution]

hi Ravi,

I will close this post. i have raised a separate discussion for this syntax error. Please post a reply there if you find a solution.

Thank you for all the help and efforts,

Benedict

[Former Member]

Hi Vasanth,

Attribute names should be same in both the tables for join.

http://help.sap.com/hana/hana_dev_sqlscript_en.pdf (please refer page 19).

to make the field names same, you can use CE_PROJECTION on table SAIRPORT and then join this projection with the table SCITAIRP using CE_JOIN.

Where clause can be included in projection level itself.

Note: Don't use normal sql and CE functions in the same calculation view, this leads to bad performance.

Regards,

Venkat