topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Mon, 16 Feb 2009 06:30:17 GMT

Hi experts,

BKPF ( Header ) : from this table i am selecting BELNR BLART BUDAT AWKEY .

V_AWKEY = IT_BKPF-AWKEY(10).

RSEG ( Item ) : from this table BELNR EBELN EBELP where BELNR = V_AWKEY .

but i am getting only one line item from rseg i want all the line items in RSEG plz guide me how can i get.

Regards,

km.

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:32:58 GMT

hi,

try using outer join .

It would be helpful if u can post ur query.

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:34:31 GMT

Hi,

U r reading RSEG ( Item ) table with variable , so, it'll give only one line item.

If it is not the solution , Can u copy and paste ur code.

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:34:36 GMT

please send the code

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:34:39 GMT

Hi,

Dont write any where condition you will get all information.

Regards

Md.MahaboobKhan

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:35:12 GMT

hi,

When retrieving details from the item table even use EBELN in the where clause of select query, this way you could get more number of line items.

Before this make sure that with respect to your condition in the query

RSEG ( Item ) : from this table BELNR EBELN EBELP where BELNR = V_AWKEY .

there exists more than 1 line item.

Thanks

Sharath

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:35:20 GMT

Please to guide you better you need to provide clear picture!!

BKPF ( Header ) : from this table i am selecting BELNR BLART BUDAT AWKEY .

V_AWKEY = IT_BKPF-AWKEY(10).

pto is V_wakey coming Fine?

RSEG ( Item ) : from this table BELNR EBELN EBELP where BELNR = V_AWKEY .

You mean problem in only Rseg?

regards

sas

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:36:01 GMT

Hi ,

You can create a join with BKPF and RSEG .

Thanks

Pinaki

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:36:07 GMT

have u written for all entries ?

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:36:20 GMT

use like this ...

select * from <dtab > into <itab> for all entries where <select options >

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:44:31 GMT

thanks to all for reply ,

from BKPF table awkey i am taking .

awkey length is 14 but first 10 number in AWKEY field is BELNR in RSEG.

I selecting from rseg i want all the line items ( BELNR = V_AWKEY ) .

code :

SELECT BUKRS BLART belnr budat awkey

INTO TABLE it_final FROM BKPF

where BUKRS = BUKRS AND

budat IN budat AND

( blart = 'RE' OR

blart = 'RN' ) .

DATA : V_AWKEY TYPE BKPF-awkey.

LOOP at it_final.

V_AWKEY = it_final-awkey(10).

SELECT belnr ebeln ebelp matnr menge salk3 kschl wrbtr lifnr shkzg xblnr

FROM rseg INTO TABLE it_rseg WHERE belnr = V_AWKEY.

LOOP AT it_RSEG .

it_final-belnr = it_RSEG-belnr .

it_final-xblnr = it_RSEG-xblnr.

ENDLOOP.

modify it_final.

CLEAR it_final.

CLEAR V_AWKEY.

CLEAR it_RSEG.

ENDLOOP.

Re: Select Statement

former_member402443 — Mon, 16 Feb 2009 06:44:54 GMT

Hi Kumar,

BKPF ( Header ) : from this table i am selecting BELNR BLART BUDAT AWKEY .

V_AWKEY = IT_BKPF-AWKEY(10).

RSEG ( Item ) : from this table BELNR EBELN EBELP where BELNR = V_AWKEY .

but i am getting only one line item from rseg i want all the line items in RSEG plz guide me how can i get.

Please do one thing.

First Select the entries from the BKPF into an internal table say it_bkpf.

Then Select the entries from rseg table into an internal say it_rseg table for all entries in it_bkpf

where belnr = it_bkpf-awkey(10).

with this u will get all the entires regarding the values in the header table

Regards

Manoj Kumar

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:47:16 GMT

Please do not post in ALL CAPITALS

THANKS TO REPLY ALL,

I TRIED JOINS AND FORALL ENTRIES.

I AM COMPARING RESG-BELNR = BKPF-AWKEY(10).

PLZ GUIDE ME.

Edited by: Matt on Feb 16, 2009 11:10 AM

Re: Select Statement

Former Member — Mon, 16 Feb 2009 06:59:44 GMT

Hi kumar

please try this code


if it_fina[] is not initial.
SELECT belnr ebeln ebelp matnr menge salk3 kschl wrbtr lifnr shkzg xblnr
FROM rseg INTO TABLE it_rseg 
for all entries in it_final               ------------> use for all entries for reteving all reacard.
WHERE belnr = it_final-awkey
endif.

Re: Select Statement

Former Member — Mon, 16 Feb 2009 07:12:47 GMT

HI,

Either try it like this.

DATA : V_AWKEY TYPE BKPF-awkey(10).

LOOP at it_final.

V_AWKEY = it_final-awkey.

SELECT belnr ebeln ebelp matnr menge salk3 kschl wrbtr lifnr shkzg xblnr

FROM rseg INTO TABLE it_rseg WHERE belnr = V_AWKEY.

OR like this

LOOP at it_final.

SELECT belnr ebeln ebelp matnr menge salk3 kschl wrbtr lifnr shkzg xblnr

FROM rseg INTO TABLE it_rseg WHERE belnr = it_final-AWKEY.

Regards

Rajesh Kumar

Re: Select Statement

former_member585060 — Mon, 16 Feb 2009 08:16:57 GMT

Hi,

Try the below code

TABLES : bkpf.

TYPES : BEGIN OF ty_final,
         bukrs TYPE bkpf-bukrs,
         blart TYPE bkpf-blart,
         belnr TYPE bkpf-belnr,          ' belnr from BKPF
         budat TYPE bkpf-budat,
         awkey(10), 
         belnr1 TYPE bkpf-belnr,         ' belnr from RSEG
         xblnr TYPE rseg-xblnr,
        END OF ty_final.


TYPES : BEGIN OF ty_rseg,
          belnr TYPE rseg-belnr,
          ebeln TYPE rseg-ebeln,
          ebelp TYPE rseg-ebelp,
          matnr TYPE rseg-matnr,
          menge TYPE rseg-menge,
          salk3 TYPE rseg-salk3,
          kschl TYPE rseg-kschl,
          wrbtr TYPE rseg-wrbtr,
          lifnr TYPE rseg-lifnr,
          shkzg TYPE rseg-shkzg,
          xblnr TYPE rseg-xblnr,
         END OF ty_rseg.

SELECT-OPTIONS : s_budat FOR bkpf-budat.

PARAMETERS : p_bukrs TYPE bkpf-bukrs.

DATA : it_final TYPE TABLE OF ty_final,
        wa_final TYPE ty_final,
       it_rseg TYPE TABLE OF ty_rseg,
        wa_rseg TYPE ty_rseg.

DATA : w_tabx TYPE sy-tabix.

SELECT bukrs
       blart
       belnr
       budat
       awkey
              INTO CORRESPONDING FIELDS OF TABLE it_final
              FROM bkpf
              WHERE bukrs = p_bukrs AND
                    budat IN s_budat AND
                   ( blart = 'RE' OR
                     blart = 'RN' ) .


SELECT belnr
       ebeln
       ebelp
       matnr
       menge
       salk3
       kschl
       wrbtr
       lifnr
       shkzg
       xblnr
            FROM rseg INTO TABLE it_rseg.


LOOP AT it_final INTO wa_final.
  w_tabx = sy-tabix.
  READ TABLE it_rseg INTO wa_rseg WITH KEY belnr = wa_final-awkey.
  IF sy-subrc = 0.
    wa_final-belnr1 = wa_rseg-belnr.
    wa_final-xblnr = wa_rseg-xblnr.
    MODIFY it_final INDEX w_tabx FROM wa_final TRANSPORTING belnr1 xblnr.
  ENDIF.
  CLEAR w_tabx.
ENDLOOP.

REFRESH it_rseg.

BREAK-POINT.

Regards

Bala Krishna

Re: Select Statement

Former Member — Mon, 16 Feb 2009 09:39:51 GMT

thanks for reply Bala Krishna,

plz check my code and correct me .

code :

&----

*& Report ZPURCHASE_REGISTER_PL

&----

REPORT ZPURCHASE_REGISTER_PL.

TABLES : bkpf, bseg.

DATA : BEGIN OF WA_FINAL,

BUKRS TYPE bkpf-BUKRS,

BLART TYPE BKPF-blart,

belnr TYPE bkpf-belnr ,

belnr1 TYPE rseg-belnr,

budat TYPE bkpf-budat,

awkey TYPE bkpf-awkey,

xblnr TYPE RSEG-xblnr,

BUZEI TYPE rseg-buzei,

END OF WA_FINAL.

DATA: BEGIN OF WA_rseg,

belnr TYPE rseg-belnr,

ebeln TYPE rseg-ebeln,

ebelp TYPE rseg-ebelp,

matnr TYPE rseg-matnr,

menge TYPE rseg-menge,

salk3 TYPE rseg-salk3,

kschl TYPE rseg-kschl,

wrbtr TYPE rseg-wrbtr,

lifnr TYPE rseg-lifnr,

shkzg TYPE rseg-shkzg,

xblnr TYPE rseg-xblnr,

buzei TYPE rseg-buzei,

end(2),

END OF WA_rseg.

data: it_final like TABLE OF WA_FINAL with HEADER LINE.

DATA: it_RSEG LIKE TABLE OF WA_RSEG WITH HEADER LINE.

----

Copy this code in every ALVreport -

TYPE-POOLS: slis.

DATA : fieldcat TYPE slis_t_fieldcat_alv WITH HEADER LINE.

data: it_events type slis_t_event WITH HEADER LINE,

wa_events TYPE slis_t_event.

*--- Declaration for specifying column position of output list

DATA: col_pos TYPE i,

eventcat TYPE slis_t_event,

eventcat_ln LIKE LINE OF eventcat,

layout_in TYPE slis_layout_alv.

it_sort TYPE slis_t_sortinfo_alv.

*--- Dec aration for report id

DATA: gv_repid LIKE sy-repid.

*--- Declaration for field catalogue & layout

DATA : gt_fieldcat TYPE slis_t_fieldcat_alv.

DATA : wa_fieldcat TYPE slis_fieldcat_alv.

DATA: v_repid LIKE sy-repid.

----

SELECTION-SCREEN BEGIN OF BLOCK b WITH FRAME TITLE text-000.

PARAMETERS : BUKRS type BKPF-bukrs ,

WERKS TYPE BSEG-WERKS .

SELECT-OPTIONS : BUDAT FOR BKPF-BUDAT .

SELECTION-SCREEN END OF BLOCK b .

START-OF-SELECTION.

PERFORM get_data.

IF NOT it_final[] IS INITIAL.

PERFORM display_data.

ENDIF.

DATA : w_tabx TYPE sy-tabix.

form get_data.

SELECT BUKRS BLART belnr budat awkey

INTO TABLE it_final FROM BKPF

where BUKRS = BUKRS AND

budat IN budat AND

( blart = 'RE' OR

blart = 'RN' ) .

DATA : V_AWKEY TYPE BKPF-awkey.

LOOP AT it_final.

V_AWKEY = it_final-awkey(10).

SELECT belnr ebeln ebelp matnr menge salk3 kschl wrbtr lifnr shkzg xblnr BUZEI

FROM rseg INTO TABLE it_rseg WHERE belnr = V_AWKEY .

CLEAR counter.

LOOP AT it_RSEG.

it_final-belnr = it_RSEG-belnr.

it_final-xblnr = it_RSEG-xblnr.

ENDLOOP.

REFRESH it_rseg.

modify it_final.

CLEAR it_final.

CLEAR V_AWKEY.

CLEAR it_RSEG.

ENDLOOP.

ENDFORM.

form display_data.

*DELETE ADJACENT DUPLICATES FROM IT_FINAL COMPARING ZUONR BLART = 'DZ'.

CLEAR wa_fieldcat.

ADD 1 TO col_pos.

wa_fieldcat-row_pos = 1.

wa_fieldcat-col_pos = col_pos.

wa_fieldcat-fieldname = 'BELNR'.

wa_fieldcat-seltext_l = 'docno'.

wa_fieldcat-outputlen = 14.

wa_fieldcat-DO_SUM = 'X'.

APPEND wa_fieldcat TO gt_fieldcat.

CLEAR wa_fieldcat.

ADD 1 TO col_pos.

wa_fieldcat-row_pos = 1.

wa_fieldcat-col_pos = col_pos.

wa_fieldcat-fieldname = 'BUDAT'.

wa_fieldcat-seltext_l = 'DATE'.

wa_fieldcat-outputlen = 14.

wa_fieldcat-DO_SUM = 'X'.

APPEND wa_fieldcat TO gt_fieldcat.

CLEAR wa_fieldcat.

ADD 1 TO col_pos.

wa_fieldcat-row_pos = 1.

wa_fieldcat-col_pos = col_pos.

wa_fieldcat-fieldname = 'XBLNR'.

wa_fieldcat-seltext_l = 'Vendor Invoice'.

wa_fieldcat-outputlen = 14.

wa_fieldcat-DO_SUM = 'X'.

APPEND wa_fieldcat TO gt_fieldcat.

IF BUKRS = 'KAPL'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = gv_repid

it_fieldcat = gt_fieldcat

i_default = 'X'

i_save = 'A'

is_layout = layout_in

it_events = it_events[]

i_grid_title = 'KAPL '

i_callback_top_of_page = it_headings[]
I_BACKGROUND_ID = 'ALV_BACKGROUND'
it_sort = it_sort

TABLES

t_outtab = it_final[]

EXCEPTIONS

program_error = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

ELSE.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = gv_repid

it_fieldcat = gt_fieldcat

i_default = 'X'

i_save = 'A'

is_layout = layout_in

is_variant = variant

it_events = it_events[]

i_callback_user_command = 'USER_COMMAND'

i_grid_title = 'KTC'

i_callback_top_of_page = 'page header'
I_BACKGROUND_ID = 'ALV_BACKGROUND'
it_sort = it_sort

TABLES

t_outtab = it_final[]

EXCEPTIONS

program_error = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

ENDFORM.

Re: Select Statement

Former Member — Mon, 16 Feb 2009 09:58:02 GMT

Hi Kumar,

Before suggesting any solutions, i want to clarify something. Did u check the table entries in RSEG. Is it having multiple entries for the value in V_AWKEY used by it. If yes then this code should fetch all the records. Or have u deduced that it is fetching 1 entry because u are having the BELNR and XBLNR values filled for only 1 entry in IT_FINAL. If this is the case, then it is bacause of ur modify statement that is out side the loop. Put that back into the loop endloop structure.

Hope this helps...

Regards... Amit

Re: Select Statement

matt — Mon, 16 Feb 2009 10:10:55 GMT

Please do not post in ALL CAPITALS

Re: Select Statement

Former Member — Mon, 16 Feb 2009 10:11:15 GMT

Amit thanks for reply,

ya multiple entries is there in RSEG but it's picking only one ( last row ).

i want to pick all the rows.

and one more i change that modify like this .

LOOP AT it_RSEG.

it_final-belnr = it_RSEG-belnr.

it_final-xblnr = it_RSEG-xblnr.

modify it_final.

ENDLOOP.

but no use it's picking same as previous.

plz see my code correct it.