topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Wed, 24 Jan 2007 05:24:23 GMT

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.

Re: select statement

Former Member — Wed, 24 Jan 2007 05:27:37 GMT

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

Re: select statement

Former Member — Wed, 24 Jan 2007 05:28:24 GMT

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

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

Re: select statement

seshatalpasai_madala — Wed, 24 Jan 2007 05:29:01 GMT

Hi,

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

Regards,

Sesh

Re: select statement

Former Member — Wed, 24 Jan 2007 05:29:58 GMT

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

Re: select statement

Former Member — Wed, 24 Jan 2007 05:30:20 GMT

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

Re: select statement

Former Member — Wed, 24 Jan 2007 05:30:57 GMT

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.

Re: select statement

learnsap — Wed, 24 Jan 2007 05:37:54 GMT

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

Re: select statement

Former Member — Wed, 24 Jan 2007 05:41:17 GMT

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.

Re: select statement

Former Member — Wed, 24 Jan 2007 06:06:49 GMT

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.

Re: select statement

Former Member — Wed, 24 Jan 2007 06:07:28 GMT

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.

Re: select statement

Former Member — Wed, 24 Jan 2007 06:11:22 GMT

Hi ,

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

Regards

Arun

Re: select statement

Former Member — Wed, 24 Jan 2007 06:16:57 GMT

that single select statement only

Re: select statement

learnsap — Wed, 24 Jan 2007 06:18:40 GMT

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.

Re: select statement

Former Member — Wed, 24 Jan 2007 06:19:22 GMT

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.

Re: select statement

Former Member — Wed, 24 Jan 2007 06:20:25 GMT

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

Re: select statement

Former Member — Wed, 24 Jan 2007 06:42:40 GMT

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....

Re: select statement

Former Member — Wed, 24 Jan 2007 06:47:57 GMT

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

Re: select statement

Former Member — Wed, 24 Jan 2007 06:59:09 GMT

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

Re: select statement

Former Member — Wed, 24 Jan 2007 07:01:22 GMT

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