select more fields within one select

Question

I manage to do the selection with more selects and a loop. 4 tables ( the last one was just for collecting all the data )

But now i'm thinking of a way to select all the fields i need with just one select statement. Here is the huge select :)

  SELECT vbak~vbeln vbak~audat
         tvakt~bezei
         vbap~posnr vbap~matnr vbap~kwmeng vbap~vrkme
         lips~vbeln lips~posnr lips~werks lips~lfimg
         vbfa~vbtyp_n
    FROM vbak JOIN vbap ON vbak~vbeln = vbap~vbeln
              JOIN tvakt ON vbak~auart = tvakt~auart
              LEFT JOIN vbfa ON vbfa~vbelv = vbak~vbeln AND vbfa~posnv = vbap~posnr
              JOIN lips ON vbfa~vbeln = lips~vbeln AND vbfa~posnn = lips~posnr
    INTO TABLE gt_salord
    WHERE tvakt~spras = 'EN' AND
          vbak~vbeln IN s_vbeln AND
          vbak~audat IN s_audat.

The problem is this doesn't work. When i try to activate it throws this error: " Unable to compare with "VBAP~POSNR". A table can be joined with a maximum of one other table using LEFT OUTER JOIN " If i don't use LEFT JOIN and only JOIN it works but i don't get all what i want. I need to get all the SALES ORDERS even if they don't have a DELIVERY ORDER assigned. Is there a way to do that, or do i really have to split my select?

Answer 1

Can you try the following selection:

SELECT vbak~vbeln vbak~audat
       tvakt~bezei
       vbap~posnr vbap~matnr vbap~kwmeng vbap~vrkme
       lips~vbeln lips~posnr lips~werks lips~lfimg
       vbfa~vbtyp_n
  FROM vbak JOIN vbap ON vbak~vbeln = vbap~vbeln
            JOIN tvakt ON vbak~auart = tvakt~auart
            LEFT JOIN vbfa ON vbfa~vbelv = vbap~vbeln AND vbfa~posnv = vbap~posnr
            JOIN lips ON vbfa~vbeln = lips~vbeln AND vbfa~posnn = lips~posnr
  INTO TABLE gt_salord
  WHERE tvakt~spras = 'EN' AND
        vbak~vbeln IN s_vbeln AND
        vbak~audat IN s_audat.

I can't test the result, but the syntax check say: ok.

There is only one tiny difference:

                                                  x---- difference
                                                  v
                LEFT JOIN vbfa ON vbfa~vbelv = vbap~vbeln AND vbfa~posnv = vbap~posnr 
                LEFT JOIN vbfa ON vbfa~vbelv = vbak~vbeln AND vbfa~posnv = vbap~posnr

You compared vbfa~vbelv with vbak~vbeln, I do it with vbap~vbeln. Both have the same value, but in the on-clause you use again vbap.

Answer 2

I have noticed in SAP that it's more efficient to simplify select statements and proceed with LOOP and SELECT SINGLE for table that do not participate in data selection.

In your case data from table VBFA could be fetch after data selection (it is not restricting the amount of data fetched from the DB).

Of course it depends on indexes, application server buffering... but, even though it might be counter-intuitive for SQL experts, keeping select statements not too complex in SAP is best.

Answer 3

I dont know about SAP Abap . But from SQL point of view you can use derived query if it is supported in SAP.

here is some approach :

select * from
(
select * from
table1 inner join table2 on table1.key=table2.key
inner join table3 on table1.key=table3.key
) a left outer join table4 b 
on a.key=b.key

Posting this as the question is tagged as SQL. Hope it works

Answer 4

Try to change the order of table fields in the on clause of left join. Put vbap~vbeln = vbfa~vbelv