topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Thu, 12 Jul 2007 05:15:44 GMT

Hi all

I hav the situation

When radio button =Storage location

Extract the data specified by parameter and store in the internal table.

CONDITION:

ISEG-MATNR　(Material number) = <Selection screen>MATNR(Material number)

ISEG-WERKS　(Plant) = <Selection screen>WERKS(Plant)

ISEG-LGORT　(Storage location)= <Selection screen>LGORT(Storage location)

IKPF-GJAHR　(Fiscal year) =Fiscal year obtained in 2.

IKPF-ZLDAT　(Count date)=Initial value

ISEG-ERFMG　(Unit of entry quantity)=Initial value

ISEG-XNULL　(Count result zero)=Blank

Join：

ISEG inner IKPF

join

ISEG inner MARA

join

ISEG inner MARC

join

ISEG inner T064B

join

ISEG inner MAKT

join

ISEG-IBLNR　(Physical inventory document) =IKPF-IBLNR　(Physical inventory document)

ISEG-GJAHR　(Fiscal year)=IKPF-GJAHR　(Fiscal year)

ISEG-MATNR　(Material number) =MARA-MATNR　(Material number)

ISEG-MATNR　(Material number) =MARC-MATNR　(Material number)

ISEG-WERKS　(Plant)=MARC-WERKS　(Plant)

ISEG-BSTAR　(Stock type)=T064B-BSTAR　(Stock type)

T064B-SPRAS　(Language key)=SY-LANGU

MAKT-SPRAS　(Language key) =SY-LANGU

I hav to extract

ISEG-MATNR Material number

ISEG-WERKS Plant

ISEG-LGORT Storage location

IKPF-IBLNR Physical inventory document

IKPF-GJAHR Fiscal year

IKPF-BLDAT Document date of document

IKPF-GIDAT Inventory count plan date

IKPF-ZLDAT Count date

ISEG-ZEILI Item

ISEG-CHARG Batch number

ISEG-SOBKZ Special stock indicator

ISEG-BSTAR Stock type

ISEG-KUNNR Customer number

ISEG-KDAUF Sales order number

ISEG-KDPOS Sales order item number

ISEG-ERFMG Quantity of unit entry

ISEG-MEINS Base unit of measure

ISEG-XNULL Count result zero

MARA-MEINSSERIAL Serial number profile

MARA-PRDHA Material hierarchy

MARA-MATKL Material group

MARC-BWTTY Valuation category

T064B-BTEXT Stock type text

MAKT-MAKTX Material text

for this how can i write select query

Re: select query

Former Member — Thu, 12 Jul 2007 05:23:57 GMT

1. First join the table IKPF and ISEG using the fields IBLNR and GJAHR into table ITAB with the selections creen fields

2. For all entries of this ITAB fetch data from MARA and MARC and MAKT and join all these tables with MATNR field and fetch into ITAB1 int table where MARA_{MATNR = ITAB-MATNR and MARC}werks = ITAB-WERKS and MAKT~SPRAS = <sel screen spras>

3. For all entries of ITAb fetch data from T064B into ITAB2 where BSTAR = itab-BSTAR.

4. Loop at ITAB.

move corresponding ITAB to i_final.

read table itab1....

move related fields to i_final.

read table itab2....

move related fields to i_final.

Append i_final.

clear i_final.

endloop

Use I_final for displaying data

Reward points for useful Answers

Regards

Anji

Re: select query

former_member196299 — Thu, 12 Jul 2007 05:25:02 GMT

hi Ramesh ,

can u explain whats the meaning of the following stmt so that it will be easy for us to help you :

IKPF-GJAHR　(Fiscal year) = Fiscal year obtained in 2. [ \b ]

Regards,

Ranjita

Re: select query

Former Member — Thu, 12 Jul 2007 05:39:40 GMT

see the basic is like this

bkpf and bseg , mkpf and mseg

like yours is IKP0F and ISEG ...

*Code to demonstrate select command
*Code to demonstrate select into internal table command
TYPES: BEGIN OF t_bkpf,
*  include structure bkpf.
  bukrs LIKE bkpf-bukrs,
  belnr LIKE bkpf-belnr,
  gjahr LIKE bkpf-gjahr,
  bldat LIKE bkpf-bldat,
  monat LIKE bkpf-monat,
  budat LIKE bkpf-budat,
  xblnr LIKE bkpf-xblnr,
  awtyp LIKE bkpf-awtyp,
  awkey LIKE bkpf-awkey,
 END OF t_bkpf.
DATA: it_bkpf TYPE STANDARD TABLE OF t_bkpf INITIAL SIZE 0,
      wa_bkpf TYPE t_bkpf.

TYPES: BEGIN OF t_bseg,
*include structure bseg.
  bukrs     LIKE bseg-bukrs,
  belnr     LIKE bseg-belnr,
  gjahr     LIKE bseg-gjahr,
  buzei     LIKE bseg-buzei,
  mwskz     LIKE bseg-mwskz,         "Tax code
  umsks     LIKE bseg-umsks,         "Special G/L transaction type
  prctr     LIKE bseg-prctr,         "Profit Centre
  hkont     LIKE bseg-hkont,         "G/L account
  xauto     LIKE bseg-xauto,
  koart     LIKE bseg-koart,
  dmbtr     LIKE bseg-dmbtr,
  mwart     LIKE bseg-mwart,
  hwbas     LIKE bseg-hwbas,
  aufnr     LIKE bseg-aufnr,
  projk     LIKE bseg-projk,
  shkzg     LIKE bseg-shkzg,
  kokrs     LIKE bseg-kokrs,
 END OF t_bseg.
DATA: it_bseg TYPE STANDARD TABLE OF t_bseg INITIAL SIZE 0,
      wa_bseg TYPE t_bseg.


so what you have to  do  for  join is   like this  ....
Select FOR ALL ENTRIES command
SELECT bukrs belnr gjahr bldat monat budat xblnr awtyp awkey
  UP TO 100 ROWS
  FROM bkpf
  INTO TABLE it_bkpf.

IF sy-subrc EQ 0.
* The FOR ALL ENTRIES comand only retrieves data which matches
* entries within a particular internal table.
  SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart
         dmbtr mwart hwbas aufnr projk shkzg kokrs
    FROM bseg
    INTO TABLE it_bseg
    FOR ALL ENTRIES IN it_bkpf
    WHERE bukrs EQ it_bkpf-bukrs AND
          belnr EQ it_bkpf-belnr AND
          gjahr EQ it_bkpf-gjahr.
ENDIF.

reward points if it is usefull....

Girish