topic Re: fetch records in Application Development and Automation Discussions

fetch records

Former Member — Thu, 25 Jun 2009 03:34:44 GMT

Hi,

I hav one internal table itab1 with all cust num's.

i hav another itab2 there i wanna place all related fields of the cust by using itab1-kunnr.

i wrote a select statement with sme inner joi conditions.

so, when it satisfies all conditions only data is displaying. if any of the condition is failed in select stmt, i coudlnt able to get atleast one field.

is ther any possibility to find for which kunnr the condition is failing?

kindly let me knw

Re: fetch records

Former Member — Thu, 25 Jun 2009 03:57:22 GMT

Hi Renu,

if you want to find out the failed records,then you should loop the internal table2 with the condition kunnr form the internal table1 if any of the conditions failed you can capture in another internal table or you can do whatever logic you want.

Re: fetch records

Former Member — Thu, 25 Jun 2009 04:03:27 GMT

Hi,

You have already kunnrs in first internal table.

write a query using FOR ALL ENTRIES in itab1 into itab2.

Now loop through itab2.

loop at itab2.

read table itab1 with key kunnr = itab2-kunnr binary search.

if sy-subrc NE 0.

move kunnrs to itab3.

endif.

endloop.

Regards,

Kumar Bandanadham

Re: fetch records

Former Member — Thu, 25 Jun 2009 05:15:29 GMT

REPORT  zsd_test1.

DATA: BEGIN OF itab1 OCCURS 0,
      kunnr LIKE kna1-kunnr,
      END OF itab1,

      BEGIN OF itab2 OCCURS 0,
      kunnr LIKE kna1-kunnr,
      land1 LIKE kna1-land1,
      flag TYPE c,             " To find existance
      END OF itab2.


* sample query- You would have used some othr logic to fetch the recrds
SELECT kunnr
FROM kna1
INTO TABLE itab1
UP TO 5 ROWS.


* Now to fetch the related fields
SELECT
kunnr
land1
FROM kna1
INTO TABLE itab2
FOR ALL ENTRIES IN itab1
WHERE kunnr = itab1-kunnr.


*NOw to find the missing rec- WHerever flag is set, record doesnt exist in itab1

LOOP AT itab2.
  READ TABLE itab1 WITH KEY kunnr = itab2-kunnr.
  IF sy-subrc <> 0.
    itab2-flag = 'X'.
    MODIFY itab2.
  ENDIF.
ENDLOOP.

Re: fetch records

Former Member — Thu, 25 Jun 2009 07:43:57 GMT

Here is my program


       SELECT VBAK~KUNNR
              VEDA~VBELN
              VEDA~VBEGDAT
              VEDA~VENDDAT
              VEDA~VKUESCH
              VBAP~MATNR
              OBJK~EQUNR
              VBAP~MEINS
              VBAP~ZMENG
         INTO TABLE  CONTRACT_MASTER
         FROM VBAP INNER JOIN SER02
              ON VBAP~POSNR EQ SER02~POSNR
              AND VBAP~VBELN EQ SER02~SDAUFNR
         INNER JOIN OBJK
              ON SER02~OBKNR EQ OBJK~OBKNR
         INNER JOIN VBAK
              ON VBAP~VBELN EQ VBAK~VBELN
         INNER JOIN VEDA
              ON VBAK~VBELN EQ VEDA~VBELN
         FOR ALL ENTRIES IN IT_CHNGE_DIR
         WHERE VBAK~VBELN EQ IT_CHNGE_DIR-OBJECTID.

it_chnge_dir is an internal table with some fields.

among the values of it_chnge_dir, if any one fields conditions is failed, how can i retrieve only failed record field.

Re: fetch records

Former Member — Thu, 25 Jun 2009 07:58:55 GMT

Hi,

Try to use For All entries and avoid inner join.

This will not only solve your problem but performance also get improved.

if inner join is unavoidable use it in header and item tables only.

Feel free to ask any doubts.

Regards,

Vijay

Re: fetch records

Former Member — Thu, 25 Jun 2009 08:05:13 GMT

use for ALL ENTRIE IN ITAB1 in ur second queryy u will get ur output...

Re: fetch records

Former Member — Thu, 25 Jun 2009 08:17:03 GMT

HI,

I ALREADY USED FOR ALL ENTRIES.

KINDLY HAVE A LOOK IN TO THE ABOVE THREAD OF MINE AND LET ME KNW HOW CAN I MODIFY THE SELECT STATEMENT FOR BETTER PERFORMANCE.

THANKS IN ADVANCE,

RENU

Re: fetch records

Former Member — Thu, 25 Jun 2009 08:26:14 GMT

Hi,

use the code given below.......

SELECT VBAK~KUNNR
              VEDA~VBELN
              VEDA~VBEGDAT
              VEDA~VENDDAT
              VEDA~VKUESCH
              VBAP~MATNR
              OBJK~EQUNR
              VBAP~MEINS
              VBAP~ZMENG
         INTO TABLE  CONTRACT_MASTER
         FROM VBAP INNER JOIN SER02
              ON VBAP~POSNR EQ SER02~POSNR
              AND VBAP~VBELN EQ SER02~SDAUFNR
         INNER JOIN OBJK
              ON SER02~OBKNR EQ OBJK~OBKNR
         INNER JOIN VBAK
              ON VBAP~VBELN EQ VBAK~VBELN
         INNER JOIN VEDA
              ON VBAK~VBELN EQ VEDA~VBELN
         FOR ALL ENTRIES IN IT_CHNGE_DIR
         WHERE VBAK~VBELN EQ IT_CHNGE_DIR-OBJECTID.

" to print the failed object id's...................
sort contract_master by vbeln.
loop at it_chnge_dir.
    read table contract_master with key vbeln eq it_chnge_dir-objectid binary search.
    if sy-subrc <> 0.
        write / it_chnge_dir-objectid.
    endif.
endloop.

hope this solves your issue

Regards,

Siddarth

Re: fetch records

Former Member — Thu, 25 Jun 2009 08:38:36 GMT

Try these steps

First do a fetch from VBAK based on the vbeln.

Then for all entries of vbak , fetch values from VBAP and then VEDA.

Then try to fetch values from OBJK.

Then loop into the table vbap and then read the other tables.

Regards

Ramesh Sundaram

Re: fetch records

Former Member — Thu, 25 Jun 2009 08:47:01 GMT

hI SIDDHARTH,

THANKS FOR UR IMMIDIATE RESPONSE.

BUT HERE IF ANY ONE CONDITION FAILS, CONTRACT_MASTER TABLE IS NOT FILLING.

THEN THE ABOVE CODE WHICH U HAV GIVEN WONT WORK WITH OUT VALUES IN CONTRACT_MASTER.

KINDLY PUT SOME LIGHT ON THIS.

Re: fetch records

Former Member — Thu, 25 Jun 2009 10:03:20 GMT

Hi,

could you please give me the declaration of IT_CHNGE_DIR....

Regards,

Siddarth

Re: fetch records

Former Member — Fri, 26 Jun 2009 03:20:55 GMT

here itab it_chnge_dir format is

TYPES: BEGIN OF chnge_dir,

objectid TYPE vbeln,

changenr TYPE cdchangenr,

END OF chnge_dir.

DATA: it_chnge_dir TYPE TABLE OF chnge_dir WITH HEADER LINE.

Re: fetch records

Former Member — Fri, 26 Jun 2009 04:07:04 GMT

Hi Renusree,

This is what the inner join does.. If any one of the condition fails it will not return any value... Better segregate the select statement using for all entries... If you do this you will come to know in debug mode in which select query and under what condition the selection is failing for the corresponding table...

Re: fetch records

Former Member — Fri, 26 Jun 2009 04:14:22 GMT

Hi Renusree,

Here is a sample code:



select posnr 
       vbeln 
       meins
       zmeng
       from table vbap 
       into table itab1
       FOR ALL ENTRIES IN IT_CHNGE_DIR
         WHERE VBAK~VBELN EQ IT_CHNGE_DIR-OBJECTID.
       where vbeln = FOR ALL ENTRIES IN IT_CHNGE_DIR
         WHERE VBAK~VBELN EQ IT_CHNGE_DIR-OBJECTID.

if sy-subrc eq o.

select the records from the 2nd table for all entries in itab1...

From the records you get select from 3rs table for the records in itab2.. and so on..
 
Finally you will get your contact_history table filled with appropriate value..

"To print failed customer No.
sort contract_master by vbeln.
loop at it_chnge_dir into ls_change.
    read table contract_master with key vbeln eq ls_change binary search.
    if sy-subrc  0.
       Do what you want to do..
    endif.
endloop.

This will solve your problem and you will also knwo which statement is failing...

Re: fetch records

Former Member — Fri, 26 Jun 2009 05:26:45 GMT

Hi sneha,

Can u kindly modify my above select statment with out inner joins.

Thanks in Advance,

Renu

Re: fetch records

Former Member — Fri, 26 Jun 2009 06:24:04 GMT

Hi,

Your select statment should be something like this:



SELECT posnr
       vbeln
       matnr
       meins
       zmeng
       FROM table vbap
       INTO TABLE itab1
       FOR ALL ENTRIES IN it_chnge_dir
         WHERE vbeln EQ it_chnge_dir-objectid.

SELECT obknr
       FROM table objk
       INTO TABLE itab2
       FOR ALL ENTRIES IN table itab1
       WHERE sdaufnr = itab1-vbeln.
SELECT equnr
       FROM table objk
       INTO TABLE itab3
       FOR ALL ENTRIES IN itab2
       WHERE obknr = itab2-obknr.

SELECT vbeln
       vbegdat
       venddat
       vkuesch
       FROM table veda
       INTO TABLE itab4
       FOR ALL ENTRIES IN table itab1
       WHERE vbeln = itab1-vbeln

Please modify it if somewhere i went wrong or have not used all your tables concerned...

Then fill in your contact_history internal table..

Re: fetch records

Former Member — Fri, 26 Jun 2009 11:38:45 GMT

Hi Renushree,

Let me make one thing a bit clear.... Inner joins will give values if atleast one record matches to the joint conditions properly, so it becomes a contradictory statement with what sneha has written

I had tried it with a sample example also.... not offending her but trying to correct her and you with the example i have tried ....

Coming to your issue....

may be segregating inner joins might give you some result but if you dont loop in properly the values in the final table that is contract_master will not have correct entries.........

I just want to you try one small step with inner joins itself.....

you said that it does not give any result when it has one wrong value....

just want to know how did you try giving the values.....

secondly try out this way........

give all the correct values in the it_chnge_dir table and get the result, once you get the result properly in the contract_master table.....

keep the values of the it_chnge_dir as it is and just append one record with a wrong value and then check if you get it.... if you get it then i think you might be able to understand what is going wrong and if you dont get it, pls. let me know from where are you getting the values of it_chnge_dir.... let us have a check on it........ and also let us know what values did u give when you checked......

i mean the values which fed you result and the values which didn't feed u results................

Regards,

Siddarth

Re: fetch records

Former Member — Fri, 26 Jun 2009 14:22:16 GMT

TYPES: BEGIN OF ty_vbak_vbap,
         vbeln  TYPE vbak-vbeln,
         kunnr  TYPE vbak-kunnr,
         posnr  TYPE vbap-posnr,
         matnr  TYPE vbap-matnr,
         meins  TYPE vbap-meins,
         zmeng  TYPE vbap-zmeng,
       END OF ty_vbak_vbap,

       BEGIN OF ty_veda,
         vbeln    TYPE veda-vbeln,
         vposn    TYPE veda-vposn,
         vbegdat  TYPE veda-vbegdat,
         venddat  TYPE veda-venddat,
         vkuesch  TYPE veda-vkuesch,
       END OF ty_veda,

       BEGIN OF ty_ser02_objk,
         sdaufnr  TYPE ser02-sdaufnr,
         posnr    TYPE ser02-posnr,
         equnr    TYPE objk-equnr,
       END OF ty_ser02_objk,

       tt_vbak_vbap   TYPE STANDARD TABLE OF ty_vbak_vbap,
       tt_veda        TYPE STANDARD TABLE OF ty_veda,
       tt_ser02_objk  TYPE STANDARD TABLE OF ty_ser02_objk.

DATA: lt_vbak_vbap  TYPE tt_vbak_vbap,
      lt_veda       TYPE tt_veda,
      lt_ser02_objk TYPE tt_ser02_objk,

      wa_vbak_vbap  TYPE ty_vbak_vbap,
      wa_veda       TYPE ty_veda,
      wa_ser02_objk TYPE ty_ser02_objk.

* Populate data form VBAK and VBAP tables
IF it_chnge_dir[] IS NOT INITIAL.
  SELECT vbak~vbeln
         vbak~kunnr
         vbap~posnr
         vbap~matnr
         vbap~meins
         vbap~zmeng
    INTO TABLE lt_vbak_vbap
    FROM vbak INNER JOIN vbap
      ON vbak~vbeln EQ vbap~vbeln
     FOR ALL ENTRIES IN it_chnge_dir
   WHERE vbak~vbeln EQ it_chnge_dir-objectid.
ENDIF.

* Populate data form VEDA table for all the data found form 
* VBAK & VBAP.
IF lt_vbak_vbap[] IS NOT INITIAL.
  SELECT vbeln
         vposn
         vbegdat
         venddat
         vkuesch
    FROM veda
    INTO TABLE lt_veda
     FOR ALL ENTRIES IN lt_vbak_vbap
   WHERE vbeln EQ lt_vbak_vbap-vbeln
     AND vposn EQ lt_vbak_vbap-posnr. 

* Populate data form SEr02 & OBJK tables
  
  SELECT ser02~sdaufnr
         ser02~posnr
         objk~equnr
    INTO TABLE lt_ser02_objk
    FROM ser02 INNER JOIN objk
      ON ser02~obknr EQ objk~obknr
     FOR ALL ENTRIES IN lt_vbak_vbap
   WHERE ser02~sdaufnr EQ lt_vbak_vbap-vbeln
     AND ser02~posnr EQ lt_vbak_vbap-posnr. 
ENDIF.

Please see Population of contract manager in the next reply

Re: fetch records

Former Member — Fri, 26 Jun 2009 14:23:38 GMT

* Final output table Population
LOOP AT lt_vbak_vbap INTO wa_vbak_vbap.
  contract_master-kunnr = wa_vbak_vbap-kunnr.
  contract_master-matnr = wa_vbak_vbap-matnr.
  contract_master-meins = wa_vbak_vbap-meins.
  contract_master-zmeng = wa_vbak_vbap-zmeng.
  
  READ TABLE lt_veda INTO wa_veda 
    WITH KEY vbeln = wa_vbak_vbap-vbeln
             vposn = wa_vbak_vbap-posnr.
  IF sy-subrc = 0.
    contract_master-vbegdat = wa_veda-vbegdat.
    contract_master-venddat = wa_veda-venddat.
    contract_master-vkuesch = wa_veda-vkuesch.
  ENDIF.             
  
  READ TABLE lt_ser02_objk INTO wa_ser02_objk
    WITH KEY sdaufnr = wa_vbak_vbap-vbeln
             posnr   = wa_vbak_vbap-posnr.
  IF sy-subrc = 0.
    contract_master-equnr = wa_ser02_objk-equnr.
  ENDIF.  
  
  APPEND contract_master.
  
  CLEAR: wa_vbak_vbap,
         wa_veda,
         wa_ser02_objk.
ENDLOOP.

This is how you can speedup the code perfomance as well as you can get the correct data also.

Please let me know if you are still getting problem.