2019 Aug 07 4:32 PM
Hello Everyone,
I am an absolute beginner in ABAP (and coding) and I am facing a problem which I am not sure how to describe. Well, I am asked to make a simple program which will output a list with Customer Number, Name of the Client, City and number of orders made by each clients.
I am also instructed that, if a customer number is missing (or non-existent) it should still show it in the list as 'Client not found' or as 'X'. For example I am searching for customer number 1 to 50 and the system can't find any result for customer number 15-20. In that case the list will give output from 1 to 14 and then write 'X' for the next 6 records and then again will display as usual outputs with value from 21 and onward.
My problem is, I could not solve this part of the problem. My output shows only the customer numbers which could be found in the system.
I appreciate your help and thank you people in advance.
Best regards,
Arnab Goswami
REPORT z_XYZ
TYPES: BEGIN OF g_ty_kna1,
KUNNR TYPE KUNNR,
NAME1 TYPE NAME1,
ORT01 TYPE ORT01,
END OF g_ty_kna1.
DATA: g_it_kna1 TYPE STANDARD TABLE OF g_ty_kna1,
g_wa_kna1 TYPE g_ty_kna1,
g_kunnr TYPE kna1-KUNNR,
g_name1 TYPE kna1-NAME1,
g_ORT01 TYPE kna1-ORT01,
anzahl TYPE i.
SELECTION-SCREEN BEGIN OF BLOCK Eingabe WITH FRAME TITLE text-001.
SELECT-OPTIONS s_kunnr FOR g_kunnr.
SELECTION-SCREEN END OF BLOCK Eingabe.
IF s_kunnr IS INITIAL.
MESSAGE 'Bitte geben Sie eine Kundennummer an.'(003) TYPE 'E'.
ELSE.
PERFORM Datenerfassung.
PERFORM Datenverarbeitung.
ENDIF.
*&---------------------------------------------------------------------*
*& Form Datenerfassung
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM Datenerfassung.
SELECT KUNNR NAME1 ORT01
FROM KNA1
INTO CORRESPONDING FIELDS OF TABLE g_it_kna1
WHERE KUNNR IN s_kunnr.
ENDFORM. "Datenerfassung
*&---------------------------------------------------------------------*
*& Form Datenverarbeitung
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM Datenverarbeitung.
NEW-LINE.
ULINE (78).
NEW-LINE.
WRITE: sy-vline,
(7) :'Kunde'(004), sy-vline,
(22):'Name'(005), sy-vline,
(18):'Ort'(006), sy-vline,
(18):'Anzahl Aufträge'(007), sy-vline.
NEW-LINE.
ULINE (78).
NEW-LINE.
LOOP AT g_it_kna1 INTO g_wa_kna1. "WHERE KUNNR = g_wa_kna1-KUNNR.
SELECT COUNT( * )
INTO anzahl
FROM VBAK
WHERE KUNNR = g_wa_kna1-KUNNR
GROUP BY KUNNR.
ENDSELECT.
IF sy-index = g_kunnr.
WRITE: sy-vline,
(7) : g_wa_kna1-KUNNR, sy-vline,
(22): g_wa_kna1-NAME1, sy-vline,
(18): g_wa_kna1-ORT01, sy-vline,
(18): anzahl, sy-vline.
NEW-LINE.
ULINE (78).
NEW-LINE.
ELSE.
DO 6 TIMES.
CHECK sy-index BETWEEN 14 and 21.
WRITE : / sy-vline,
(7) : 'X' UNDER 'Kunde'(004), sy-vline,
(22):'Der Kunde existiert nicht.'(002) UNDER 'Name', sy-vline,
(18): 'X' UNDER 'Ort', sy-vline,
(18): 'X' UNDER 'Anzahl Aufträge', sy-vline.
ENDDO.
NEW-LINE.
ULINE (78).
NEW-LINE.
ENDIF.
ENDLOOP.
ENDFORM. "Datenverarbeitung
TOP-OF-PAGE.
NEW-LINE.
ULINE (78).
NEW-LINE.
WRITE: sy-vline,
(7) :'Datum:'(009), sy-datum, sy-vline,
(10):'Uhrzeit:'(010), sy-uzeit, sy-vline,
(18):'Benutzer/in:'(011), sy-uname, sy-vline.
NEW-LINE.
ULINE (78).
NEW-LINE.
WRITE: 'Gesamtdatensatz abgerufen'(008), sy-dbcnt.
2019 Aug 08 1:39 AM
i think you can left join kna1 and vbak instead of do the check inside loop, ideal like:
SELECT kna1~KUNNR
,kna1~NAME1
,kna1~ORT01
,COALESCE( vbak~VBELN, 'Not found' ) as vbeln
FROM kna1 LEFT OUTER JOIN vbak ON ( kna1~kunnr = vbak~kunnr )
WHERE kna1~kunnr in @s_kunnr
ORDER BY vbak~VBELN
INTO TABLE @DATA(customer_info).
2019 Aug 07 6:34 PM
SY-INDEX is a system counter updated only by DO and WHILE loops.
For LOOP AT, if the table is standard or sorted, you may use SY-TABIX. Be careful, it's potentially changed by any READ TABLE. Anyway, you may also use your own counter variable.
PS: don't forget to debug your code. Currently, it seems you don't because your question is not very precise.
2019 Aug 07 8:11 PM
Hi,
The requirement to display nonexistent customers does not make a lot of sense considering that you can have several ranges in the select option or customer number can be alphanumeric.
However, technically you could have done as follows if the select-option had the option no-extensions... but the solution may not work if the select option is filled differently....
lv_kunnr = s_kunnr-low.
while lv_kunnr <= s_kunnr-high.
read g_it_kna1.....
if sy-subrc = 0.
select from vbak...
else.
display 'Client not found'.
endif.
lv_kunnr = lv_kunnr + 1.
endwhile.
2019 Aug 08 1:39 AM
i think you can left join kna1 and vbak instead of do the check inside loop, ideal like:
SELECT kna1~KUNNR
,kna1~NAME1
,kna1~ORT01
,COALESCE( vbak~VBELN, 'Not found' ) as vbeln
FROM kna1 LEFT OUTER JOIN vbak ON ( kna1~kunnr = vbak~kunnr )
WHERE kna1~kunnr in @s_kunnr
ORDER BY vbak~VBELN
INTO TABLE @DATA(customer_info).
2019 Aug 08 8:36 AM
For some reason this solution gives me a "Comma without preceding colon (after SELECT?)" warning.
2019 Aug 08 10:18 AM
2019 Aug 08 11:42 AM
What is your sap version, maybe we could come up with other solution...
2019 Aug 13 9:39 AM
@ quynh.doanmanh
Sorry I did not see your message earlier. It's ECC 6.0, Basis 7.01. Thanks in advance.
2019 Aug 08 8:15 AM
It looks like you are trying to report on all customers that have no orders. In that case just check the return code after the select on VBAK, something like this:
SELECT COUNT(*)
INTO anzahl
FROM VBAK
WHERE KUNNR = g_wa_kna1-KUNNR
GROUPBY KUNNR.
ENDSELECT.
IF sy-subrc = 0. "Customer has orders
ELSE. "Customer has no orders
ENDIF.
2019 Aug 08 12:01 PM
Actually, I am trying to report all the customer numbers, even if 'NO' client is assigned to that particular customer-number. For example, I am trying to fetch the infos for Customer number 1-100 and there is no record for customer number 5-15. I want the output to show 'No Customer Found' or 'X' in those 11 places. Thank you.
2019 Aug 08 12:10 PM
Arnab,
Where are you deriving your list of customer numbers from? Will the user enter all the customer numbers on the selection screen? Without a definitive list of customer numbers you cannot say which customer exists and which do not. You can't use a select option unless you limit the selection to single values only with sign 'EQ'.
Until you tell us where your list of customers is coming from we can't help much.
2019 Aug 08 12:54 PM
surbjeet.dhillon,
I am getting the customer numbers from KNA1 table. At the selection screen the user will be able to input a range from 1 to N.
2019 Aug 08 1:02 PM
No you are not getting the customer numbers from the KNA1 table. Your are checking the customer numbers against the KNA1 table.
If the user will enter a nice list of customer numbers on the selection screen like 1, 2, 3, 4, 5, all the way to N then great you can check each one against KNA1. If however the user can enter any selection range like 1 to 100, 1 to 9999999999, 1*23*4 to *999, etc etc then how will you derive your list of customer numbers to start searching KNA1????????
You need to set some rules on how you will use the selection screen to derive your initial list of customer numbers.
2019 Aug 09 2:18 AM
I think you get it wrong direction. customer information get from KNA1 with selection-option on screen which is normal. it doesn't matter what user inputted. the question is not about check the existence of customers but to check within that range of customers, who don't have sale document.
2019 Aug 09 9:12 AM
I need to write a program which will meet these requirements. Thanks to all.
2019 Aug 09 9:31 AM
You had a nice idea to post the request, because I "understand" it but not your original question 😉
In fact, you need to ask the requestor to confirm one point: SAP selection screens have input fields which may contain wildcards and intervals, and the requestor seems to just ignore that. So does he mean that wildcards and intervals are to be ignored? In that case, you should define a SELECT-OPTIONS with limited features by using the function module SELECT_OPTIONS_RESTRICT (the UI rendering will be better).
If they are to be ignored, it means that the list of customer numbers is exactly the list that is to be displayed, so it's easy.
In your code, don't forget that you must "never" (unless exceptions) include SQL inside loops.
2019 Aug 14 3:22 PM
Thanks to all the people who have shared their valuable input. Finally got it right by myself. I am sharing my code here for any one who may face the same problem in the future.
Report: z_XYZ
TYPES: BEGIN OF g_ty_kna1,
KUNNR TYPE KUNNR,
NAME1 TYPE NAME1,
ORT01 TYPE ORT01,
END OF g_ty_kna1.
TYPES: BEGIN OF g_ty_vbak,
KUNNR TYPE KUNNR,
VBELN TYPE VBELN,
END OF g_ty_vbak.
DATA: g_it_kna1 TYPE STANDARD TABLE OF g_ty_kna1,
g_wa_kna1 TYPE g_ty_kna1,
g_kunnr TYPE kna1-KUNNR,
g_it_vbak TYPE STANDARD TABLE OF g_ty_vbak,
g_wa_vbak TYPE g_ty_vbak,
G_R_KUNDE TYPE RANGE OF kunnr,
g_wa_KUNDE LIKE LINE OF G_R_KUNDE,
g_anzahl TYPE i.
SELECTION-SCREEN BEGIN OF BLOCK Eingabe WITH FRAME TITLE text-001.
SELECT-OPTIONS s_kunnr FOR g_kunnr NO INTERVALS.
SELECTION-SCREEN END OF BLOCK Eingabe.
AT SELECTION-SCREEN.
IF s_kunnr IS INITIAL and sy-ucomm = 'ONLI'.
MESSAGE 'Bitte geben Sie eine Kundennummer an.'(003) TYPE 'E'.
ENDIF.
START-OF-SELECTION.
PERFORM Datenerfassung.
PERFORM Datenverarbeitung.
*&---------------------------------------------------------------------*
*& Form Datenerfassung
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM Datenerfassung.
SELECT KUNNR NAME1 ORT01
FROM KNA1
INTO CORRESPONDING FIELDS OF TABLE g_it_kna1
WHERE KUNNR IN s_kunnr.
IF sy-subrc IS INITIAL.
SELECT KUNNR VBELN FROM VBAK
INTO CORRESPONDING FIELDS OF TABLE g_it_vbak
FOR ALL ENTRIES IN g_it_kna1
WHERE KUNNR EQ g_it_kna1-kunnr.
ENDIF.
ENDFORM. "Datenerfassung
*&---------------------------------------------------------------------*
*& Form Datenverarbeitung
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
FORM Datenverarbeitung.
NEW-LINE.
ULINE (87).
NEW-LINE.
WRITE: sy-vline,
(8) :'Kunde'(004), sy-vline,
(30):'Name'(005), sy-vline,
(18):'Ort'(006), sy-vline,
(18):'Anzahl Aufträge'(007), sy-vline.
NEW-LINE.
ULINE (87).
NEW-LINE.
LOOP AT s_kunnr INTO g_wa_kunde.
READ TABLE g_it_kna1 INTO g_wa_kna1 WITH KEY kunnr = g_wa_kunde-low.
IF sy-subrc = 0.
CLEAR g_anzahl.
LOOP AT g_it_vbak INTO g_wa_vbak WHERE KUNNR = g_wa_kna1-kunnr.
g_anzahl = g_anzahl + 1.
* ADD 1 to g_anzahl.
ENDLOOP.
WRITE: sy-vline,
(8) : g_wa_kna1-kunnr, sy-vline,
(30): g_wa_kna1-NAME1, sy-vline,
(18): g_wa_kna1-ORT01, sy-vline,
(18): g_anzahl , sy-vline.
NEW-LINE.
ULINE (87).
NEW-LINE.
ELSE.
WRITE : / sy-vline,
(8) : g_wa_kunde-low UNDER 'Kunde'(004), sy-vline,
(30):'Der Kunde existiert nicht.'(002) UNDER 'Name'(005), sy-vline,
(18): '' UNDER 'Ort', sy-vline,
(18): '0' UNDER 'Anzahl Aufträge'(007), sy-vline.
NEW-LINE.
ULINE (87).
NEW-LINE.
ENDIF.
ENDLOOP.
ENDFORM. "Datenverarbeitung