topic problem in joins in Application Development and Automation Discussions

problem in joins

Former Member — Wed, 25 Feb 2009 07:49:51 GMT

Hi folks,

in my following code the idnlf data is not fetching.other than idnlf field all datas are fetching..please help

me on this as soon as possible..

if iam using for all entries it is showing error..

can u please provide me a sample coding in this case.

tables: mara,marc,eina.

at selection-screen on oldmatno.

select single bismt from mara into w_bismt

where bismt = oldmatno.

start-of-selection.

if oldmatno is not initial.

select mara~mtart

mara~mfrpn

mara~mfrnr

mara~brand_id

marc~nfmat

marc~atpkz

makt~maktx

into table itab

from mara inner join marc

on mara_{matnr = marc}matnr

inner join makt

on mara_{matnr = makt}matnr

where bismt in oldmatno

and makt~spras = 'EN'.

select idnlf from eina into table itab1

where matnr = mara-matnr.

Thanks,

Neelu.

Re: problem in joins

Former Member — Wed, 25 Feb 2009 07:56:00 GMT



select  mara~matnr    <--- select material no tooo
mara~mtart
mara~mfrpn
mara~mfrnr
mara~brand_id
marc~nfmat
marc~atpkz
makt~maktx
into table itab
from mara inner join marc
on mara~matnr = marc~matnr
inner join makt
on mara~matnr = makt~matnr
where bismt in oldmatno
and makt~spras = 'EN'.

if not itab is initial.
select idnlf from eina into table itab1 for all entries in itab
where matnr = itab-matnr.
endif.

revrt back if any issue,

Regards,

Naveen

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:00:58 GMT

Hi Naveen,

still the idnlf field data is not fetching..can u please help me out regarding this.

Thanks,

Neelu.

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:03:59 GMT

Hi Neelima,

plz check in debug mode,

Is ITAB have material numbers filled,

and check if there are entries in eina table which have idnlf fild and matnr Filled with data in SE11.

Regards,

Naveen

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:08:02 GMT

Hi,

where have you included idnlf field in the select query...

there is no field in the select query which gives you the record of idnlf field.

please check

Regards,

Siddarth

Re: problem in joins

faisalatsap — Wed, 25 Feb 2009 08:08:56 GMT

Hi,

Test the following Sample Code Hope will help you to solve out your problem,

TABLES: mara,marc,eina.

SELECT-OPTIONS: oldmatno FOR mara-bismt.

DATA: BEGIN OF itab OCCURS 100,
  matnr like mara-matnr,
  mtart LIKE mara-mtart,
  mfrpn LIKE mara-mfrpn,
  mfrnr LIKE mara-mfrnr,
  brand_id LIKE  mara-brand_id,
  nfmat LIKE  marc-nfmat,
  atpkz LIKE  marc-atpkz,
  maktx LIKE  makt-maktx,
  END OF itab.

SELECT mara~matnr mara~mtart mara~mfrpn mara~mfrnr mara~brand_id marc~nfmat marc~atpkz makt~maktx
INTO CORRESPONDING FIELDS OF TABLE itab
FROM mara INNER JOIN marc ON ( mara~matnr = marc~matnr )
          INNER JOIN makt ON ( mara~matnr = makt~matnr )
  WHERE bismt IN oldmatno
    AND makt~spras = 'EN'.

Please Reply if any Issue,

Kind Regards,

Faisal

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:16:39 GMT

Hi Naveen,

i checked in debug mode also, in debug mode the data for idnlf is not showing,but itab data is showing and the itab data is printing. and idnlf data is there in se11 the matnr for which idnlf value is existing , that matnr no only im selecting.thought the idnlf data is not selecting..

please help me out..

Thanks ,

Neelima.N

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:20:37 GMT

you want to select MATNR field from MARA table. Because you are using this in where condition.

Regards,

Joan

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:21:28 GMT

Hi,

This is my code..except the idnlf data is not fetching..idnlf data is existing i checked in se11 against bismt , matnr in mara.

Tables:Mara,Marc,Eina,Makt.

Data : begin of itab occurs 0, "for material number

matnr type mara-matnr,

mtart type mara-mtart,

mfrpn type mara-mfrpn,

mfrnr type mara-mfrnr,

brand_id type mara-brand_id,

nfmat type marc-nfmat,

atpkz type marc-atpkz,

maktx type makt-maktx,

end of itab.

Data : begin of itab1 occurs 0, "for material number

matnr type mara-matnr,

idnlf type eina-idnlf,

end of itab1.

Data : begin of i_final occurs 0, "for material number

mtart type mara-mtart,

mfrpn type mara-mfrpn,

mfrnr type mara-mfrnr,

brand_id type mara-brand_id,

maktx type makt-maktx,

nfmat type marc-nfmat,

atpkz type marc-atpkz,

idnlf type eina-idnlf,

end of i_final.

Data: w_matnr(10) type c,

w_bismt(10) type c.

at selection-screen on oldmatno.

select single bismt from mara into w_bismt

where bismt = oldmatno.

start-of-selection.

if oldmatno is not initial.

select mara~matnr

mara~mtart

mara~mfrpn

mara~mfrnr

mara~brand_id

marc~nfmat

marc~atpkz

makt~maktx

into table itab

from mara inner join marc

on mara_{matnr = marc}matnr

inner join makt

on mara_{matnr = makt}matnr

where bismt in oldmatno

and makt~spras = 'EN'.

if not itab[] is initial.

select idnlf from eina into table itab1

for all entries in itab

where matnr = itab-matnr.

endif.

loop at itab.

i_final-mtart = itab-mtart.

i_final-mfrpn = itab-mfrpn.

i_final-mfrnr = itab-mfrnr.

i_final-brand_id = itab-brand_id.

i_final-maktx = itab-maktx.

i_final-nfmat = itab-nfmat.

i_final-atpkz = itab-atpkz.

i_final-idnlf = itab1-idnlf.

append i_final.

clear i_final.

endloop.

loop at i_final.

write:/ i_final-mtart,

i_final-mfrpn,

i_final-mfrnr,

i_final-brand_id,

i_final-nfmat,

i_final-atpkz,

i_final-maktx,

i_final-idnlf.

endloop.

Thanks,

Neelu.

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:23:34 GMT

Hi,

try to pass 18 digit material number , by placing leading zeros ( convesrion exit ) and check it.

or in debug mode place leading zeros for material and .

if not itab is initial.

select idnlf from eina into table itab1 for all entries in itab

where matnr = itab-matnr. <--- pass 18 digit material no

endif.

regards,

naveen

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:23:37 GMT

Use Idlnf Field in Select Statement and try...

like u used

MARA~MATNR

mara~idlnf

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:40:04 GMT

Hi,

now iam selecting matnr value in second selct query along with idnlf.now the data is showing in debugging mode but data is not printing.

can u help me where iam going wrong.

Tables:Mara,Marc,Eina,Makt.

Data : begin of itab occurs 0, "for material number

matnr type mara-matnr,

mtart type mara-mtart,

mfrpn type mara-mfrpn,

mfrnr type mara-mfrnr,

brand_id type mara-brand_id,

nfmat type marc-nfmat,

atpkz type marc-atpkz,

maktx type makt-maktx,

end of itab.

Data : begin of itab1 occurs 0, "for material number

matnr type mara-matnr,

idnlf type eina-idnlf,

end of itab1.

Data : begin of i_final occurs 0, "for material number

mtart type mara-mtart,

mfrpn type mara-mfrpn,

mfrnr type mara-mfrnr,

brand_id type mara-brand_id,

maktx type makt-maktx,

nfmat type marc-nfmat,

atpkz type marc-atpkz,

idnlf type eina-idnlf,

end of i_final.

Data: w_matnr(10) type c,

w_bismt(10) type c.

at selection-screen on oldmatno.

select single bismt from mara into w_bismt

where bismt = oldmatno.

start-of-selection.

if oldmatno is not initial.

select mara~matnr

mara~mtart

mara~mfrpn

mara~mfrnr

mara~brand_id

marc~nfmat

marc~atpkz

makt~maktx

into table itab

from mara inner join marc

on mara_{matnr = marc}matnr

inner join makt

on mara_{matnr = makt}matnr

where bismt in oldmatno

and makt~spras = 'EN'.

if not itab[] is initial.

selectmatnr idnlf from eina into table itab1

for all entries in itab

where matnr = itab-matnr.

endif.

loop at itab.

i_final-mtart = itab-mtart.

i_final-mfrpn = itab-mfrpn.

i_final-mfrnr = itab-mfrnr.

i_final-brand_id = itab-brand_id.

i_final-maktx = itab-maktx.

i_final-nfmat = itab-nfmat.

i_final-atpkz = itab-atpkz.

i_final-idnlf = itab1-idnlf.

append i_final.

clear i_final.

endloop.

loop at i_final.

write:/ i_final-mtart,

i_final-mfrpn,

i_final-mfrnr,

i_final-brand_id,

i_final-nfmat,

i_final-atpkz,

i_final-maktx,

i_final-idnlf.

endloop.

Thanks,

Neelu.

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:40:21 GMT

Hi,

check it now...


if not itab is initial.
select idnlf from eina into table itab1 for all entries in itab
where matnr = itab-matnr.
endif.

replace above with 

if not itab is initial.
select matnr idnlf from eina into  corresponding fields of table itab1 for all entries in itab
where matnr = itab-matnr.
endif.

revrt back,

Reagrds,

Naveen

Re: problem in joins

keerthy_k — Wed, 25 Feb 2009 08:40:37 GMT

Hi Neelima,

U are going wrong inside the loop.

loop at itab.

i_final-mtart = itab-mtart.

i_final-mfrpn = itab-mfrpn.

i_final-mfrnr = itab-mfrnr.

i_final-brand_id = itab-brand_id.

i_final-maktx = itab-maktx.

i_final-nfmat = itab-nfmat.

i_final-atpkz = itab-atpkz.

i_final-idnlf = itab1-idnlf.

append i_final.

clear i_final.

endloop.

Inside the loop of itab u wont have values for itab1. If u want the values, u need to write the read statement inside the loop of itab, like:

Read table itab1 into wa with key matnr = itab-matnr.

then assign the idnlf value as:

i_final-idnlf = wa-idnlf.

.....

endloop.

Keerthi

Edited by: Keerthy K on Feb 25, 2009 9:41 AM

Re: problem in joins

Former Member — Wed, 25 Feb 2009 08:44:14 GMT

Thank u so much for the people who have helped.

my problem got solved .