7 Replies Latest reply: Sep 24, 2010 2:14 PM by Karl Pover RSS

    SAP Tables Join and concatenate together

    Sravan Puppala

      Hi all,

      May be I am doing not right. correct me If I am wrong.

       


      VBAK:
      Load ,
      VBELN,
      VBELN as SALESDOC,
      ...,
      .....from VBAK.qvd;
      VBAP_temp:
      Load
      VBELN,
      ....,
      .....from VBAP.qvd
      where exists (VBELN, VBELN);
      VBAP:
      Load
      1 as reason,
      VBELN,
      VBELN as SALESDOC,
      VBELN/POS as SALESDOCITEM,
      ......
      ......
      Resident VBAP_temp
      where GRUND>0;
      concatenate
      Load
      VBELN,
      VBELN as SALESDOC,
      VBELN/POS as SALESDOCITEM,
      2 as reason,
      ......
      ......
      Resident VBAP_temp
      where GRUND<0;
      Drop Table VBAP_temp;


      Actually I want VBAK Inner Join VBAP, I think I cannot do it here?

      Any other way to do it keeping optimized load in tact!

      Thanks in Advance

      sravan

        • SAP Tables Join and concatenate together
          Karl Pover

          sraven,

          Like you said it should be at least VBAK left join VBAP. Concatenate will not work. If you're worried about the load speed why don't you keep the tables separate? In that case you can use the function 'keep' to make sure there is no header in VBAK without line positions in VBAP. Or why don't you want to do a join?

          Regards.

           

            • SAP Tables Join and concatenate together
              Sravan Puppala

              Hi Karl,

              The problem is that I have a lot of tables in the Data model and I am compressing to reduce the overall data and in this process reducing the tables with the help of Joins. Keep is a good advice ok, that I can do but I think still I have two tables. Innerjoin to make work I have to load again the same table like this

              Load * resident VBAK

              InnerJoin

              Load* resident VBAP

              innerjoin

              Load * resident VBEP

              etc..

              As you see I am loading tables 2 times. I think this can be done in another way with out loading the same table again. Left Keep again will not merge. I need here Join to merge.

              may be someone can help

              Thanks

              Sravan

                • SAP Tables Join and concatenate together
                  Karl Pover

                  Hi Sravan,

                  To do the join you don't need to load the table 2 times. The condition "where exists (VBELN, VBELN);" is not necessary since the inner join will automatically verifty that no VBELN that doesn't exist in the 2 tables is in the final table. I assume that you are using VBELN as you key, correct?

                  If you have the table,

                  VBAK:
                  VBELN | KUNNR
                  1 | 2341
                  2 | 3928
                  3 | 2928

                  inner join

                  VBAP:
                  VBELN | POSNR
                  1 | 1
                  1 | 2
                  2 | 1

                  your final result without doing the exists is:

                  VBELN | KUNNR | POSNR
                  1 | 2341 | 1
                  1 | 2341 | 2
                  2 | 3928 | 1

                    • SAP Tables Join and concatenate together
                      Sravan Puppala

                      Hey Karl,

                       

                      Thanks again. You are absolutely right but what my problem is, I think you are not getting.

                      Normal 0 21 false false false DE X-NONE X-NONE MicrosoftInternetExplorer4

                      VBAK:
                      VBELN | KUNNR
                      1 | 2341
                      2 | 3928
                      3 | 2928

                      inner Join

                      VBAP: But here in VBAP I need to concatenate two tables into one because of ABGRU>0 and ABGRU<0

                      VBAP:

                      Load * from VBAP where ABGRU>0(Flag1);

                      concatenate Load * from VBAP where ABGRU<0(Flag2);

                      VBELN|POSNR|ABGRU|Flag

                      1|1|01|1

                      1|2||2

                      2|1|02|1

                      [/ncode]

                      And Here innerjoin does not work because I am concatenating. Thats why I loaded VBAP_temp(More than 4 million records) reduced it with where exists and then concatenated(400 Thousand records) to reduce the Data.

                      If I need to innerjoin VBAK to VBAP then I must again reload the Data from VBAK and then Innerjoin VBAP.

                      Can you recommed otherway here??

                      Thanks for your answer. Hope I am clear now.