Skip to Content
-1

sap hana sql

Hi Expert,

What should I do that combine the data of two inner tables(it_tab_h1 and it_tab_k1)?

picture.png (7.2 kB)
picture2.png (13.9 kB)
picture3.png (17.4 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Oct 24, 2019 at 08:15 AM

    Hi Ivan,

    Based on CLIENT, FMSNO and DOCTY columns you can join both these tables. The join could left or inner based on table data.

    Refer below sample SQL:

    DO BEGIN
    it_tab_h = SELECT 100 AS CLIENT,
    'a' as FMSNO,
    'AA' as DOCTY,
    'x1' as FMS_FCR,
    'y1' as DOCNO_1
    FROM DUMMY
    UNION ALL
    SELECT 100 AS CLIENT,
    'a' as FMSNO,
    'AA' as DOCTY,
    'x2' as FMS_FCR,
    'y2' as DOCNO_1
    FROM DUMMY
    ;
    
    
    it_tab_h1 = SELECT
    CLIENT,
    FMSNO,
    DOCTY,
    STRING_AGG(CASE WHEN(FMS_FCR != '') THEN FMS_FCR ELSE NULL END ,'/') AS FMS_FCR,
    STRING_AGG(CASE WHEN(DOCNO_1 != '') THEN DOCNO_1 ELSE NULL END ,'/') AS DOCNO_1
    FROM :it_tab_h
    GROUP BY 
    CLIENT,
    FMSNO,
    DOCTY
    ;
    
    
    it_tab_k = SELECT 100 AS CLIENT,
    'a' as FMSNO,
    'AA' as DOCTY,
    'm1' as MAWB,
    'n1' as MAWB_1,
    'o1' as HAWB_1
    FROM DUMMY
    UNION ALL
    SELECT 100 AS CLIENT,
    'a' as FMSNO,
    'AA' as DOCTY,
    'm2' as MAWB,
    'n2' as MAWB_1,
    'o2' as HAWB_1
    FROM DUMMY
    ;
    
    
    it_tab_k1 = SELECT
    CLIENT,
    FMSNO,
    DOCTY,
    STRING_AGG(CASE WHEN(MAWB != '') THEN MAWB ELSE NULL END ,'/') AS MAWB,
    STRING_AGG(CASE WHEN(MAWB_1 != '') THEN MAWB_1 ELSE NULL END ,'/') AS MAWB_1,
    STRING_AGG(CASE WHEN(HAWB_1 != '') THEN HAWB_1 ELSE NULL END ,'/') AS HAWB_1
    FROM :it_tab_k
    GROUP BY 
    CLIENT,
    FMSNO,
    DOCTY
    ;
    
    
    SELECT T1.CLIENT,
    T1.FMSNO,
    T1.DOCTY,
    T1.FMS_FCR,
    T1.DOCNO_1,
    T2.MAWB,
    T2.MAWB_1,
    T2.HAWB_1
    FROM 
    :it_tab_h1 AS T1
    INNER JOIN
    :it_tab_k1 AS T2
    ON T1.CLIENT = T2.CLIENT AND T1.FMSNO = T2.FMSNO AND T1.DOCTY = T2.DOCTY
    ;
    END


    capture.jpg (26.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • HI Kedar,

      Recently I start to learn about amdp,Thank you very much for answering my question again´╝ü

      I hope that the result of using sring_agg function is not duplicat.

      such as:the fms_fcr field is duplicat according to fmsno,Similarly docno_1 mawb mawb_1 hawb_1 according to fmsno expected are not duplicat.

      it_tab_h = SELECT100ASCLIENT,

      'a'as FMSNO,

      'AA'as DOCTY,

      'x1'as FMS_FCR,

      'y1'as DOCNO_1

      FROMDUMMY

      UNIONALL

      SELECT100ASCLIENT,

      'a'as FMSNO,

      'AA'as DOCTY,

      'x1'as FMS_FCR,

      'y2'as DOCNO_1

      FROMDUMMY

      ;

      the expected result is

      this following is my sql,but it_warno cannot inner join it_cabno promoting an error,Please guide me how to correct,

      CLASS zcl_flights_demo_cds DEFINITION

      PUBLIC

      FINAL

      CREATE PUBLIC .

      PUBLIC SECTION.

      INTERFACES if_amdp_marker_hdb.

      CLASS-METHODS: flights_connections FOR TABLE FUNCTION ZDEMO_FLIGHTS_TABLE_FUNCTION.

      PROTECTED SECTION.

      PRIVATE SECTION.

      ENDCLASS.

      CLASS zcl_flights_demo_cds IMPLEMENTATION.

      METHOD flights_connections

      BY DATABASE FUNCTION

      FOR HDB

      LANGUAGE SQLSCRIPT

      OPTIONS READ-ONLY

      USING ZHX_FMST0059 ZHX_FMST0059I_BD .

      it_warno = SELECT

      DISTINCT A.MANDT as CLIENT,

      A.FMSNO as FMSNO,

      B.warno AS warno FROM ZHX_FMST0059 as A

      INNER JOIN ZHX_FMST0059I_BD AS B on A.DOCNO = B.DOCNO

      WHERE DOCTY = 'A';

      it_cabno = SELECT

      DISTINCT A.MANDT as CLIENT,

      A.FMSNO as FMSNO,

      B.cabno as cabno FROM ZHX_FMST0059 as A

      INNER JOIN ZHX_FMST0059I_BD AS B on A.DOCNO = B.DOCNO

      WHERE DOCTY = 'A';

      it_tab_h = SELECT

      A.CLIENT,

      A.FMSNO,

      A.WARNO,

      B.CABNO

      FROM : it_warno AS A

      LEFT JOIN : it_cabno AS B ON A.FMSNO = B.FMSNO;

      RETURN SELECT

      CLIENT,

      fmsno,

      STRING_AGG(CASE WHEN(warno != '') THEN warno ELSE NULL END ,'/ ') AS warno,

      STRING_AGG(CASE WHEN(CABNO != '') THEN CABNO ELSE NULL END ,'/ ') AS CABNO

      FROM :it_tab_h

      GROUP BY CLIENT, fmsno;

      ENDMETHOD.

      ENDCLASS.

      picture.png (10.3 kB)
      picture2.png (52.6 kB)

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.