topic Re: regarding select query in Application Development and Automation Discussions

regarding select query

Former Member — Wed, 14 Feb 2007 14:43:50 GMT

i want to select the matnr, and material description unit of measurement , from mara and makt. the select query used is

select single a_{matnr a}meins b_{maktx from mara as a inner join makt as b on b}matnr = a_{matnr and b}spras eq 'EN' into

corresponding fields of it_bitem1 where a~matnr = matnr.

Here in the internal table no values are fetched . What is the error in this .

Thanks in advance.

Re: regarding select query

Former Member — Wed, 14 Feb 2007 14:52:08 GMT

Hi,

Error is Leading Zeros are missing in MATNR.

Use FM 'Conversion_exit_aplha_input' for MANTR before u pass it to Select. i.e.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

input = matnr

IMPORTING

output = matnr.

select single a_{matnr a}meins b_{maktx from mara as a inner join makt as b on b}matnr = a_{matnr and b}spras eq 'EN' into

corresponding fields of it_bitem1 where a~matnr = matnr.

Reward points if the answer is helpful.

Regards,

Mukul

Message was edited by:

Mukul R. Kulkarni

Re: regarding select query

Former Member — Wed, 14 Feb 2007 14:52:17 GMT

hi,

rewrite your query like this:

select a~matnr

a~meins

b~maktx

into table it_bitem1

from mara as a inner join makt as b

on a_{matnr eq b}matnr.

if you got any select-option or parameter then specify it in the where condition.

if you are having matnr as select-option then write in

where condition a~matnr in s_matnr

or if it is parameter then write

where a~matnr eq p_matnr.

Re: regarding select query

Former Member — Wed, 14 Feb 2007 14:52:20 GMT

change to this and checkout if it works

declare workare for it_bitem1 .

data : wa_bitem1 type it_bitem1 .

select single a~matnr a~meins b~maktx from mara as a inner join makt as b on a~matnr = b~matnr and b~spras eq 'EN' into
corresponding fields of wa_bitem1 where a~matnr = matnr.

Re: regarding select query

Former Member — Wed, 14 Feb 2007 14:59:31 GMT

hi,

try tis...

select single a~matnr

a~meins

b_{maktx from mara as a inner join makt as b on b}matnr = a_{matnr into corresponding fields of it_bitem1 where a}matnr = matnr and b~spras eq 'EN' .

siva

Re: regarding select query

hermanoclaro — Wed, 14 Feb 2007 15:04:14 GMT

Hi there,

Maybe the problem is the spras at the on statement.

Try the select like this:


select single a~matnr a~meins b~maktx
from mara as a inner join
        makt as b
on b~matnr = a~matnr
into corresponding fields of it_bitem1
where a~matnr = matnr
    and b~spras eq 'EN'.

Hope this helps.

Re: regarding select query

Former Member — Wed, 14 Feb 2007 15:10:38 GMT

change SPRAS as EN to E as in MAKT length of SPRAS is only 1

b~spras eq 'E'

Re: regarding select query

Former Member — Wed, 14 Feb 2007 15:12:23 GMT

hi,

rewrite your query like this:

select a~matnr

a~meins

b~maktx

into table it_bitem1

from mara as a inner join makt as b

on a_{matnr eq b}matnr

where spras eq 'EN'.

if you got any select-option or parameter then specify it in the where condition.

if you are having matnr as select-option then write in

where condition a~matnr in s_matnr

or if it is parameter then write

where a~matnr eq p_matnr.

Re: regarding select query

Former Member — Wed, 14 Feb 2007 15:13:19 GMT

reward points all useful answers,if ur problem get solved...

Re: regarding select query

Former Member — Wed, 14 Feb 2007 15:46:20 GMT

Hi ,

I have faced the similar problems too . And Using the Function Module ''CONVERSION_EXIT_ALPHA_INPUT' solved the problem . As the material number is a 16 digit the select query expects the full 16 digit number . Hence usage of the above given FM converts the material number into a 16 digit number and your select statement would returns the correct number of records.

Regards,

Sowmya.

Re: regarding select query

Former Member — Wed, 14 Feb 2007 15:59:10 GMT

Thanks for all your replies ,

i have tried all these choices but i cant able to solve the problem. still it doesn't fetch any datas

Re: regarding select query

hermanoclaro — Wed, 14 Feb 2007 17:09:32 GMT

Hi,

I've tried your piece of code here and everything is fine as follows:

DATA: BEGIN OF it_bitem1,
        matnr type mara-matnr,
        meins type mara-meins,
        maktx type makt-maktx,
      END OF it_bitem1,
      matnr type mara-matnr
        value '000000000000000142'.

select single a~matnr a~meins b~maktx
from mara as a inner join
     makt as b
on a~matnr = b~matnr
into corresponding fields of it_bitem1
where a~matnr = matnr
    and b~spras eq 'E'.

break-point.

What can be happening for you is:

1. Veriable matnr in where statement could be a select-options and must be declared as a~matnr IN matnr.

2. There's none data in one of these tables (since it's inner join) with the spras you are using (deppending would be nice to change the hardcode 'E' for sy-langu).

3. The variable matnr is a parameter and is empty.

Check these possibilities.

Hope this helps you out.