Regarding select query with code

ashish_gupta11 — Mon, 23 Jul 2007 06:43:42 GMT

Hi Experts ,

i m taking two transparent table "VBAK , VBAP" WITH CLUSTER TABLE "KONV"

In this Table we create inner join between VBAK ,ABAP to itab and make itab2 with some field of KONV.

===========================================================

&----

*& Report Z_VBAP_REPORT *

*& *

&----

*& *

&----

REPORT Z_VBAP_REPORT .

TABLES:VBAP,VBAK,KONV.

DATA KNUMV LIKE VBAK-KNUMV.

DATA:BEGIN OF ITAB OCCURS 0,

VBELN LIKE VBAK-VBELN,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

NETPR LIKE VBAP-NETPR,

BNAME LIKE VBAK-BNAME,

ERDAT LIKE VBAK-ERDAT,

KBETR LIKE KONV-KBETR,

KNTYP LIKE KONV-KNTYP,

KNUMV LIKE VBAK-KNUMV,

END OF ITAB.

DATA ITAB2 LIKE ITAB OCCURS 0 WITH HEADER LINE.

PARAMETER SALE_DOC LIKE VBAK-VBELN.

SELECT-OPTIONS D_DATE FOR VBAK-ERDAT.

SELECT B_{VBELN A}POSNR A_{MATNR A}NETPR B_{BNAME B}ERDAT INTO

CORRESPONDING FIELDS OF TABLE ITAB FROM VBAP AS A INNER JOIN VBAK

AS B ON ( B_{VBELN = A}VBELN ) WHERE B~ERDAT IN D_DATE AND

A~VBELN = SALE_DOC.

SELECT KBETR KNTYP FROM KONV INTO CORRESPONDING FIELDS OF TABLE ITAB2

WHERE KNUMV = KNUMV .

EXPORT ITAB TO MEMORY ID 'ASHISH'.

EXPORT ITAB2 TO MEMORY ID 'ASHISH1'.

SUBMIT Z_ABAP2 VIA SELECTION-SCREEN AND RETURN.

///////Code for report Z_ABAP2

&----

*& Report Z_ABAP2 *

*& *

&----

*& *

&----

REPORT Z_ABAP2 .

TABLES:VBAP,VBAK,KONV.

DATA:BEGIN OF ITAB OCCURS 0,

VBELN LIKE VBAK-VBELN,

POSNR LIKE VBAP-POSNR,

MATNR LIKE VBAP-MATNR,

NETPR LIKE VBAP-NETPR,

BNAME LIKE VBAK-BNAME,

ERDAT LIKE VBAK-ERDAT,

KBETR LIKE KONV-KBETR,

KNTYP LIKE KONV-KNTYP,

KNUMV LIKE VBAK-KNUMV,

END OF ITAB.

DATA ITAB2 LIKE ITAB OCCURS 0 WITH HEADER LINE.

LOOP AT ITAB.

WRITE : / ITAB-VBELN,

ITAB-POSNR, 25

ITAB-MATNR,

ITAB-NETPR,

ITAB-BNAME,

ITAB-ERDAT,

ITAB-KBETR,

ITAB-KNTYP.

ENDLOOP.

LOOP AT ITAB2.

WRITE 😕 ITAB2-KBETR,

ITAB2-KNTYP.

ENDLOOP.

INITIALIZATION .

IMPORT ITAB FROM MEMORY ID 'ASHISH'.

IMPORT ITAB2 FROM MEMORY ID 'ASHISH1'.

=========================================================

thanks & regards ,

Ashish

Re: Regarding select query with code

Former Member — Mon, 23 Jul 2007 06:54:30 GMT

after the first select use for all entries of this ITAB and fetch data from KONV as below, declare I_FINAL and move the ITAB and ITAB2 data into it and display its output

SELECT B_{VBELN A}POSNR A_{MATNR A}NETPR B_{BNAME B}ERDAT B~KNUMV INTO

CORRESPONDING FIELDS OF TABLE ITAB FROM VBAP AS A INNER JOIN VBAK

AS B ON ( B_{VBELN = A}VBELN ) WHERE B~ERDAT IN D_DATE AND

A~VBELN = SALE_DOC.

IF NOT ITAB[] IS INITIAL.

SELECT KNUMV KPOSN KBETR KNTYP FROM KONV INTO CORRESPONDING FIELDS OF TABLE ITAB2 for all entries in ITAB

WHERE KNUMV = ITAB-KNUMV and KPOSN = ITAB-POSNR .

Loop at itab.

move-corresponding itab to i_final.

read table itab2 with key knumv = itab-knumv and kposn = itab-posnr.

if sy-subrc = 0.

move-corresponding itab2 to i_final.

endif.

append i_final.

clear i_final.

endloop.

Loop at i_final.

write: / I_final (write all the fields)

endloop.

Reward points for useful Answers

Regards

Anji

topic Regarding select query with code in Application Development and Automation Discussions

Regarding select query with code

Re: Regarding select query with code