select statement

[Former Member]

PARAMETERS : P_WERKS LIKE CAUFV-WERKS, " SINGLE OPTION

P_GSTRS LIKE CAUFV-GSTRS. " SINGLE OPTION

SELECT-OPTIONS: S_AUART FOR CAUFV-AUART, " MULTIPLE OPTION

S_ARBID FOR AFVC-ARBID, " MULTIPLE OPTION

S_ARBPL FOR CRHD-ARBPL. " MULTIPLE OPTION

PERFORM data_retrieval.

form data_retrieval .

SELECT SINGLE * FROM CAUFV WHERE

WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF SY-SUBRC NE 0.

MESSAGE I000(ZF999).

LEAVE TO LIST-PROCESSING.

ENDIF.

ENDFORM.

i am unable to retrive values from table. kindly correct me i am not getting the solution.

[Former Member]

Hi Madhukar ,

If you are not assigning value to P_WERKS or P_GSTRS then the select statement may not select data , because in this case it will search for records where WERKS or GSTRS is intial.

Please assign some valid values to all the elements on the selection screen and see if you are able to retreive values , if yes then blank values in the reason for you not getting records from the table.

Regards

Arun

[Former Member]

just go to tcode Se11 and table CAUFV and give the selection criteria as u r giving on selction screen of ur report program and see if any values comes in..

tables: caufv,

afvc,

crhd.

PARAMETERS : P_WERKS LIKE CAUFV-WERKS, " SINGLE OPTION

P_GSTRS LIKE CAUFV-GSTRS. " SINGLE OPTION

SELECT-OPTIONS: S_AUART FOR CAUFV-AUART, " MULTIPLE OPTION

S_ARBID FOR AFVC-ARBID, " MULTIPLE OPTION

S_ARBPL FOR CRHD-ARBPL. " MULTIPLE OPTION

PERFORM data_retrieval.

form data_retrieval .

SELECT SINGLE * FROM CAUFV WHERE

WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF SY-SUBRC NE 0.

write 'Not found'.

else.

write 'found'.

ENDIF.

ENDFORM.

[Former Member]

my requirement is like this... i don't know where i am doing wrong. kindly see the code and help me. i have seen the table in SE16 it has values.

TABLES: AFKO, " Order header data PP orders

AFPO, " Order item

CAUFV, " View "Order Headers PP/CO"

AFVC, " Operation within an order

CRHD.

TYPE-POOLS : SLIS.

PARAMETERS : P_WERKS LIKE CAUFV-WERKS, " SINGLE OPTION

P_GSTRS LIKE CAUFV-GSTRS. " SINGLE OPTION

SELECT-OPTIONS: S_AUART FOR CAUFV-AUART, " MULTIPLE OPTION

S_ARBID FOR AFVC-ARBID, " MULTIPLE OPTION

S_ARBPL FOR CRHD-ARBPL. " MULTIPLE OPTION

DATA : BEGIN OF WA_AFKO OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

END OF WA_AFKO.

DATA : BEGIN OF WA_CAUFV OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

AUFPL LIKE CAUFV-AUFPL,

WERKS LIKE CAUFV-WERKS,

AUART LIKE CAUFV-AUART,

GSTRS LIKE CAUFV-GSTRS,

APLZT LIKE CAUFV-APLZT,

END OF WA_CAUFV.

*DATA IT_CAUFV TYPE STANDARD TABLE OF WA_CAUFV WITH

• DEFAULT KEY.

DATA : BEGIN OF WA_AFVC OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

APLZL LIKE AFVC-APLZL,

ARBID LIKE AFVC-ARBID,

AUFPL LIKE AFVC-AUFPL,

END OF WA_AFVC.

DATA : BEGIN OF WA_CRHD OCCURS 0,

ARBPL LIKE CRHD-ARBPL,

END OF WA_CRHD.

DATA: BEGIN OF ALV_DISPLAY OCCURS 0,

WERKS LIKE CAUFV-WERKS,

AUART LIKE CAUFV-AUART,

ARBID LIKE AFVC-ARBID,

GSTRS LIKE CAUFV-GSTRS,

• CRHD

END OF ALV_DISPLAY.

• ALV DATA DECLARATIONS.

DATA : FIELDCATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,

GD_TABGROUP TYPE SLIS_T_SP_GROUP_ALV,

GD_LAYOUT TYPE SLIS_LAYOUT_ALV,

GD_REPID TYPE SY-REPID,

GT_EVENT TYPE SLIS_T_EVENT,

GD_PRNTPARAMS TYPE SLIS_PRINT_ALV.

• START OF SELECTION.

START-OF-SELECTION.

PERFORM data_retrieval.

PERFORM build_fieldcatalog.

• PERFORM layout.

• PERFORM bulid_events.

• PERFORM print_parms.

• PERFORM display_alv_report.

&----

---

*& Form data_retrieval

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form data_retrieval .

SELECT SINGLE * FROM CAUFV WHERE

WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF SY-SUBRC NE 0.

MESSAGE I000(ZF999).

LEAVE TO LIST-PROCESSING.

ENDIF.

• START OF SELECTION.

*

SELECT AUFNR WERKS AUART GSTRS AUFPL APLZT FROM CAUFV

INTO CORRESPONDING FIELDS OF TABLE WA_CAUFV

WHERE WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF NOT WA_CAUFV[] IS INITIAL.

SELECT AUFPL APLZL ARBID FROM AFVC INTO WA_AFVC

FOR ALL ENTRIES IN WA_CAUFV

WHERE AUFPL EQ WA_CAUFV-AUFPL AND

APLZL EQ WA_CAUFV-APLZT.

ENDSELECT.

ENDIF.

*

LOOP AT WA_CAUFV.

READ TABLE WA_AFVC WITH KEY AUFPL = WA_CAUFV-AUFPL.

• APLZT = WA_CAUFV-APLZL. BINARY SEARCH.

IF SY-SUBRC EQ 0.

ALV_DISPLAY-WERKS = WA_CAUFV-WERKS.

ALV_DISPLAY-AUART = WA_CAUFV-AUART.

ALV_DISPLAY-ARBID = WA_AFVC-ARBID.

ALV_DISPLAY-GSTRS = WA_CAUFV-GSTRS.

ENDIF.

ENDLOOP.

endform. " data_retrieval

&----

---

*& Form build_fieldcatalog

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form build_fieldcatalog .

endform.

[Former Member]

Hi ,

Did you debug and see which select statement is not retreiving the values.

Regards

Arun

[Former Member]

that single select statement only

[Former Member]

my requirement is like this... i don't know where i am doing wrong. kindly see the code and help me. i have seen the table in SE16 it has values.

TABLES: AFKO, " Order header data PP orders

AFPO, " Order item

CAUFV, " View "Order Headers PP/CO"

AFVC, " Operation within an order

CRHD.

TYPE-POOLS : SLIS.

PARAMETERS : P_WERKS LIKE CAUFV-WERKS, " SINGLE OPTION

P_GSTRS LIKE CAUFV-GSTRS. " SINGLE OPTION

SELECT-OPTIONS: S_AUART FOR CAUFV-AUART, " MULTIPLE OPTION

S_ARBID FOR AFVC-ARBID, " MULTIPLE OPTION

S_ARBPL FOR CRHD-ARBPL. " MULTIPLE OPTION

DATA : BEGIN OF WA_AFKO OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

END OF WA_AFKO.

DATA : BEGIN OF WA_CAUFV OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

AUFPL LIKE CAUFV-AUFPL,

WERKS LIKE CAUFV-WERKS,

AUART LIKE CAUFV-AUART,

GSTRS LIKE CAUFV-GSTRS,

APLZT LIKE CAUFV-APLZT,

END OF WA_CAUFV.

*DATA IT_CAUFV TYPE STANDARD TABLE OF WA_CAUFV WITH

• DEFAULT KEY.

DATA : BEGIN OF WA_AFVC OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

APLZL LIKE AFVC-APLZL,

ARBID LIKE AFVC-ARBID,

AUFPL LIKE AFVC-AUFPL,

END OF WA_AFVC.

DATA : BEGIN OF WA_CRHD OCCURS 0,

ARBPL LIKE CRHD-ARBPL,

END OF WA_CRHD.

DATA: BEGIN OF ALV_DISPLAY OCCURS 0,

WERKS LIKE CAUFV-WERKS,

AUART LIKE CAUFV-AUART,

ARBID LIKE AFVC-ARBID,

GSTRS LIKE CAUFV-GSTRS,

• CRHD

END OF ALV_DISPLAY.

• ALV DATA DECLARATIONS.

DATA : FIELDCATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,

GD_TABGROUP TYPE SLIS_T_SP_GROUP_ALV,

GD_LAYOUT TYPE SLIS_LAYOUT_ALV,

GD_REPID TYPE SY-REPID,

GT_EVENT TYPE SLIS_T_EVENT,

GD_PRNTPARAMS TYPE SLIS_PRINT_ALV.

• START OF SELECTION.

START-OF-SELECTION.

PERFORM data_retrieval.

PERFORM build_fieldcatalog.

• PERFORM layout.

• PERFORM bulid_events.

• PERFORM print_parms.

• PERFORM display_alv_report.

&----

---

*& Form data_retrieval

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form data_retrieval .

SELECT SINGLE * FROM CAUFV WHERE

WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF SY-SUBRC NE 0.

MESSAGE I000(ZF999).

LEAVE TO LIST-PROCESSING.

ENDIF.

• START OF SELECTION.

*

SELECT AUFNR WERKS AUART GSTRS AUFPL APLZT FROM CAUFV

INTO CORRESPONDING FIELDS OF TABLE WA_CAUFV

WHERE WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF NOT WA_CAUFV[] IS INITIAL.

SELECT AUFPL APLZL ARBID FROM AFVC INTO WA_AFVC

FOR ALL ENTRIES IN WA_CAUFV

WHERE AUFPL EQ WA_CAUFV-AUFPL AND

APLZL EQ WA_CAUFV-APLZT.

ENDSELECT.

ENDIF.

*

LOOP AT WA_CAUFV.

READ TABLE WA_AFVC WITH KEY AUFPL = WA_CAUFV-AUFPL.

• APLZT = WA_CAUFV-APLZL. BINARY SEARCH.

IF SY-SUBRC EQ 0.

ALV_DISPLAY-WERKS = WA_CAUFV-WERKS.

ALV_DISPLAY-AUART = WA_CAUFV-AUART.

ALV_DISPLAY-ARBID = WA_AFVC-ARBID.

ALV_DISPLAY-GSTRS = WA_CAUFV-GSTRS.

ENDIF.

ENDLOOP.

endform. " data_retrieval

&----

---

*& Form build_fieldcatalog

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form build_fieldcatalog .

endform.

[Former Member]

Hi ,

Please check the value of the three selection screen elements which are used in the where clause and please revert back with the values , also do check if there is a record with the same set of values in the database table.

Regards

Arun

[Former Member]

I can able to retrive values into wa_caufv. but it is not retriving values into wa_afvc. select for all entries is not getting any data. sy-subrc eq 4.. please clarify this dobut. i can able to see single values by giving related plant data....

[Former Member]

Hi ,

The select statement is not correct

SELECT AUFPL APLZL ARBID FROM AFVC INTO WA_AFVC
FOR ALL ENTRIES IN WA_CAUFV
WHERE AUFPL EQ WA_CAUFV-AUFPL AND
APLZL EQ WA_CAUFV-APLZT.
ENDSELECT.

why do you want endselect and secondly wa_afvc as per your decleration is an intern table.

so use

SELECT AUFPL APLZL ARBID FROM AFVC

INTO table WA_AFVC

FOR ALL ENTRIES IN WA_CAUFV

WHERE AUFPL EQ WA_CAUFV-AUFPL AND

APLZL EQ WA_CAUFV-APLZT..

Regards

Arun

• Do assign points if reply is helpful

[Former Member]

i tryed that. but i am not getting values into wa_afvc.. i am not getting where i am doing wrong.

[Former Member]

So the same stuff , take a sample value from your internal table and check if that value exists in the database.

Do revert back with what you found out

[Former Member]

Hi Madhukar ,

In the select statement dont use the field APLZL in the where clause.

so write the select statement as below

SELECT AUFPL APLZL ARBID FROM AFVC

INTO table WA_AFVC

FOR ALL ENTRIES IN WA_CAUFV

WHERE AUFPL EQ WA_CAUFV-AUFPL.

Regards

Arun

• Assign Points if reply is useful

[Former Member]

still i am not getting the values into wa_afvc.

[Former Member]

did you check with a combination of AUFPL and APLZL whethere there is a record in the database table and secondly see my previous post regarding the select statement.

[Former Member]

where u store ur retrive value .either for ur case.

if sy-subrc = 0 it means it retrive the values.

[seshatalpasai_madala]

Hi,

Check by giving SELECT INTO TABLE. And then use READ TABLE.

Regards,

Sesh

[Former Member]

Hi Madhukar

If no values are passed to parameters and we use the parameter in selection, the extraction will happen for only blank values.

Eg:

SELECT SINGLE * FROM CAUFV 
  WHERE WERKS EQ P_WERKS   
  AND   AUART IN S_AUART 
  AND   GSTRS EQ P_GSTRS.

If no value is passed for P_WERKS or P_GSTRS, you many not get the desired results.

Please check.

Regards

Eswar

[Former Member]

Hi Madhukar,

Check in Se16 whether you have the data for the parameters you are supplying in where clause.

also when you are selecting i dont see a work area in the select clause (select single * from <> into wa where ...... )

where the data is to be collected.

Hope this helps.

Regards,

Kinshuk

[Former Member]

Hi Madhukar,

Pass some values into the parameters declared. As u are using them in the select statement if they are blank values will not be slected. Just check them out once.

Try to assign some values and then check the statement in the debugging mode.

Regards,

Priyanka.

[learnsap]

Hi Madhukar,

If you are not passing any values into the parameters p_werks and p_gstrs, the select query checks the database for the entries with null values in the corresponding fields. So make sure that you are passing some values into these parameters.

Regards,

Ramesh

[Former Member]

my requirement is like this... i don't know where i am doing wrong. kindly see the code and help me. i have seen the table in SE16 it has values.

TABLES: AFKO, " Order header data PP orders

AFPO, " Order item

CAUFV, " View "Order Headers PP/CO"

AFVC, " Operation within an order

CRHD.

TYPE-POOLS : SLIS.

PARAMETERS : P_WERKS LIKE CAUFV-WERKS, " SINGLE OPTION

P_GSTRS LIKE CAUFV-GSTRS. " SINGLE OPTION

SELECT-OPTIONS: S_AUART FOR CAUFV-AUART, " MULTIPLE OPTION

S_ARBID FOR AFVC-ARBID, " MULTIPLE OPTION

S_ARBPL FOR CRHD-ARBPL. " MULTIPLE OPTION

DATA : BEGIN OF WA_AFKO OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

END OF WA_AFKO.

DATA : BEGIN OF WA_CAUFV OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

AUFPL LIKE CAUFV-AUFPL,

WERKS LIKE CAUFV-WERKS,

AUART LIKE CAUFV-AUART,

GSTRS LIKE CAUFV-GSTRS,

APLZT LIKE CAUFV-APLZT,

END OF WA_CAUFV.

*DATA IT_CAUFV TYPE STANDARD TABLE OF WA_CAUFV WITH

• DEFAULT KEY.

DATA : BEGIN OF WA_AFVC OCCURS 0,

AUFNR LIKE AFKO-AUFNR,

APLZL LIKE AFVC-APLZL,

ARBID LIKE AFVC-ARBID,

AUFPL LIKE AFVC-AUFPL,

END OF WA_AFVC.

DATA : BEGIN OF WA_CRHD OCCURS 0,

ARBPL LIKE CRHD-ARBPL,

END OF WA_CRHD.

DATA: BEGIN OF ALV_DISPLAY OCCURS 0,

WERKS LIKE CAUFV-WERKS,

AUART LIKE CAUFV-AUART,

ARBID LIKE AFVC-ARBID,

GSTRS LIKE CAUFV-GSTRS,

• CRHD

END OF ALV_DISPLAY.

• ALV DATA DECLARATIONS.

DATA : FIELDCATALOG TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE,

GD_TABGROUP TYPE SLIS_T_SP_GROUP_ALV,

GD_LAYOUT TYPE SLIS_LAYOUT_ALV,

GD_REPID TYPE SY-REPID,

GT_EVENT TYPE SLIS_T_EVENT,

GD_PRNTPARAMS TYPE SLIS_PRINT_ALV.

• START OF SELECTION.

START-OF-SELECTION.

PERFORM data_retrieval.

PERFORM build_fieldcatalog.

• PERFORM layout.

• PERFORM bulid_events.

• PERFORM print_parms.

• PERFORM display_alv_report.

&----

---

*& Form data_retrieval

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form data_retrieval .

SELECT SINGLE * FROM CAUFV WHERE

WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF SY-SUBRC NE 0.

MESSAGE I000(ZF999).

LEAVE TO LIST-PROCESSING.

ENDIF.

• START OF SELECTION.

*

SELECT AUFNR WERKS AUART GSTRS AUFPL APLZT FROM CAUFV

INTO CORRESPONDING FIELDS OF TABLE WA_CAUFV

WHERE WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

IF NOT WA_CAUFV[] IS INITIAL.

SELECT AUFPL APLZL ARBID FROM AFVC INTO WA_AFVC

FOR ALL ENTRIES IN WA_CAUFV

WHERE AUFPL EQ WA_CAUFV-AUFPL AND

APLZL EQ WA_CAUFV-APLZT.

ENDSELECT.

ENDIF.

*

LOOP AT WA_CAUFV.

READ TABLE WA_AFVC WITH KEY AUFPL = WA_CAUFV-AUFPL.

• APLZT = WA_CAUFV-APLZL. BINARY SEARCH.

IF SY-SUBRC EQ 0.

ALV_DISPLAY-WERKS = WA_CAUFV-WERKS.

ALV_DISPLAY-AUART = WA_CAUFV-AUART.

ALV_DISPLAY-ARBID = WA_AFVC-ARBID.

ALV_DISPLAY-GSTRS = WA_CAUFV-GSTRS.

ENDIF.

ENDLOOP.

endform. " data_retrieval

&----

---

*& Form build_fieldcatalog

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form build_fieldcatalog .

endform.

[learnsap]

Hi Madhukar,

IF NOT WA_CAUFV[] IS INITIAL.

SELECT AUFPL APLZL ARBID FROM AFVC INTO WA_AFVC

FOR ALL ENTRIES IN WA_CAUFV

WHERE AUFPL EQ WA_CAUFV-AUFPL AND

APLZL EQ WA_CAUFV-APLZT.

ENDSELECT.

ENDIF.

Here you are not moving the data into the table, you should mention into table wa_afvc.Check this out.

Regards,

Ramesh Babu S

If useful reward the points.

Have any further queries revert back.

[Former Member]

Hi Madhukar,

Declare a work area say wa_caufv of type caufv and then use the below statement.

SELECT SINGLE * FROM CAUFV into wa_acufv WHERE

WERKS EQ P_WERKS AND

AUART IN S_AUART AND

GSTRS EQ P_GSTRS.

Make sure p_waers and p_gstrs are having valid values.If there is atleast one record in the table caufv which satisfies your select condition you should get a result.

Pls reward points if found useful.

Thanks

Shyam