-1

To get ORT02 field from KNA1 table, I am trying to join VBRK to KNA1 but it currently returns 0 line.

I am not sure about the links.

It doesn't allow me to select options from ORT02.

How shall I join these 2 tables?

Tables: VBRK,
        KNA1,
        VBRP.


TYPES: Begin of itab5,

   VKORG TYPE VBRK-VKORG,
   FKDAT TYPE VBRK-FKDAT,
   ARKTX TYPE VBRP-ARKTX,
   FKIMG TYPE VBRP-FKIMG,
   VRKME TYPE VBRP-VRKME,
   ORT02 TYPE KNA1-ORT02,

 End of itab5.

DATA: wa_ma5 TYPE itab5,
      it_ma5 TYPE STANDARD TABLE OF itab5,
      quan TYPE VBRP-FKIMG,
      curr TYPE vbrp-vrkme.

SELECT-OPTIONS: DATE FOR VBRK-FKDAT.
SELECT-OPTIONS: Organ FOR VBRK-VKORG.

APPEND date.
APPEND Organ.

START-OF-SELECTION.
  SELECT VBRK~VKORG VBRK~FKDAT VBRP~FKIMG VBRP~VRKME VBRP~ARKTX KNA1~ORT02 FROM VBRK
    inner JOIN VBRP ON VBRP~vbeln = VBRK~vbeln
    INNER JOIN KNA1 ON VBRK~KUNAG = KNA1~KUNNR AND VBRK~KUNRG = KNA1~KUNNR
     INTO CORRESPONDING FIELDS OF TABLE it_ma5
     WHERE FKDAT in DATE AND VKORG in Organ AND ARKTX LIKE '%Semoule%' AND ORT02 LIKE '%ORA%'.
Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
A.Ikram
  • 77
  • 10
  • 3
    Not related to the question, but what is you intent with the two APPENDs to the select options? I have no access to an SAP system to check but I believe KUNAG and KUNRG are two different types of customers on the order, check your data to see which one has the value you expect. Changing the JOIN condition to an OR might also fix the problem. – Gert Beukema Sep 20 '20 at 17:17
  • 1
    I did not notice the LOOP before, the IF sy-subrc = 0 inside the LOOP does probably not do what you think. – Gert Beukema Sep 20 '20 at 17:20
  • Please tell us the results of your debug: how many lines are in `it_ma5` and are you sure that you enter the statement `quan = quan + wa_ma5-fkimg.`? – Sandra Rossi Sep 20 '20 at 17:53
  • @GertBeukema the APPEND statements are to append all values in one field of each. I even tried to join one Either KUNAG (sold to party) or KUNRG (Bill to party) but the same thing. – A.Ikram Sep 21 '20 at 07:31
  • @GertBeukema I didn't understand your question about LOOP – A.Ikram Sep 21 '20 at 07:37
  • @SandraRossi there is no error after directing the report, but quan takes 0 value. when I remove the condition related to ORT02 , I can get a value in the quan. – A.Ikram Sep 21 '20 at 07:41
  • Okay so the issue is not the loop and the sum, the issue is that SELECT returns 0 line. Please don't post text and code not related to the issue (sum), please let me simplify your question/code. – Sandra Rossi Sep 21 '20 at 08:58
  • 3
    As Gert said, the two lines `APPEND` are useless because the select-options already contain the lines entered by the user (you are probably confused with the concept of "Header Lines" in the debugger). Concerning your query, you have to test each selection independently to understand which one is correct and which one is wrong. – Sandra Rossi Sep 21 '20 at 09:02
  • I tried to test each separately when I keep on the condition of ARKTX ,as in the below code, it prints the correct value of quantity. ``` START-OF-SELECTION. SELECT VBRK~VKORG VBRK~FKDAT VBRP~FKIMG VBRP~VRKME VBRP~ARKTX KNA1~ORT02 FROM VBRK inner JOIN VBRP ON VBRP~vbeln = VBRK~vbeln INNER JOIN KNA1 ON KNA1~KUNNR = VBRK~KUNRG INTO CORRESPONDING FIELDS OF TABLE it_ma5 WHERE FKDAT in DATE AND VKORG in Organ AND ARKTX LIKE '%Semoule%'. END-OF-SELECTION. ``` – A.Ikram Sep 22 '20 at 07:22
  • Whereas when I specify ORT02 it gives 0 value for quantity – A.Ikram Sep 22 '20 at 07:27

3 Answers3

2

Can you get any results from this tables according to your conditions via SE16 (SE16N/SE16H)? I mean try to look at KNA1 with filter on ORT02, then select all customers and filter VBAK by them, then go to VBAP with selected VBELNs and filter by text. Did you get something? Are you sure that there are any data with your restrictions? If yes, then try to remove all where conditions and leave only VBAP~KUNAG or VBAP~KUNRG in your join conditions. Then add one after another to find the problem condition. Select statement looks fine and there's no any tricks in this fields.

astentx
  • 6,393
  • 2
  • 16
  • 25
  • I didn't exactly get what you have said but I went into se16n and KUNRG is the field I should use . About Selection statement I get 0 value only when specifying ORT02 but I get correct values with the others. – A.Ikram Sep 22 '20 at 07:46
  • 2
    He's try to explain you the simplest and the most effective approach to solve any problem: [break-down approach](https://www.stiernholm.com/en/blog/how-to-divide-a-large-task-into-smaller-pieces), when you try and fail and exclude any non-relevant hypotheses (pieces) that cause the issue. In your case the JOIN restrictions/SELECT filters are the pieces – Suncatcher Sep 22 '20 at 15:15
  • 1
    @A.Ikram You should imagine yourself an abap and try to execute your select statement by yourself with SE16. Start from, for example, KNA1 and find all the customers by ORT2. Then go to VBAK and find all relevant (to your selected customers) sales orders. Then finally go to VBAP and filter by name. With this incremental approach on each step you can easily find is it cuts something off or not. – astentx Sep 22 '20 at 21:59
  • I finally used ORT01 instead of ORT02 and In the select query I joined VBRK and KNA1 by using KUNNR and KUNRG so it specified the bill-to party . I got correct results of quantities. – A.Ikram Sep 24 '20 at 08:23
  • Maybe Since ORT02 is District, it doesn't show values. – A.Ikram Sep 24 '20 at 08:26
  • Do you really think abap cares about semantical meaning of the field? You've put some restriction on field and it just applied it. If there's no rows with that value, you'll get nothing. You didn't provide any business-case you need to solve, so nobody can guess what your filters are. Field descriptions you can see in SE11 tcode. – astentx Sep 27 '20 at 00:23
0

This is a misunderstanding of the SQL JOIN or of the involved data structures.

It doesn't make sense to JOIN VBRK with KNA1 by KUNAG AND KUNRG. One is Sold-To Party and the other one is Payer. If you need info related to both partners you can join 2 times with KNA1, once for sold-to and once for payer. Something like:

    select vbrk~vbeln, 
           vbrk~kunag, 
           kna1_sp~ort02 as ort02_soldto, 
           vbrk~kunrg, 
           kna1_py~ort02 as ort02_payer 
      into ....
      from vbrk inner join vbrp on vbrp~vbeln = vbrk~vbeln 
                inner join kna1 as kna1_sp on kna1_sp~kunnr = vbrk~kunag
                inner join kna1 as kna1_py on kna1_py~kunnr = vbrk~kunrg
      ....
Valentin
  • 31
  • 1
  • I finally used ORT01 instead of ORT02 and In the select query I joined VBRK and KNA1 by using KUNNR and KUNRG so it specified the bill-to party . I got correct results of quantities. – A.Ikram Sep 24 '20 at 08:23
-1

You can use ADRC table , it contains the CITY1 field which may be useful for your needs.

Devhop
  • 1