topic select in Application Development and Automation Discussions

select

VenuAnumayam — Fri, 11 Aug 2006 21:02:28 GMT

Hi Experts,

I'm a beginner.

Can you please tell me what is wrong with the program.

REPORT ZTEST1 .

tables: mara,

mch1.

constants: c_mat type mara-matnr value '107901',

c_pla type marc-werks value 'XXX',

c_bat type mch1-charg value '0000066584'.

data: l_matnr type mara-matnr,

l_charg type mch1-charg,

l_mtart type mara-mtart.

select mtart into l_mtart from mara

where matnr = c_mat.

endselect.

if l_mtart = 'FERT'.

select matnr into l_matnr from mch1

where charg = c_bat.

write: / l_matnr.

endselect.

endif.

thnx a ton.

Re: select

RichHeilman — Fri, 11 Aug 2006 21:05:52 GMT

Make sure that your material number has the internal format with leading zeros.





<b>constants: c_mat type mara-matnr value '000000000000107901',</b>
c_pla type marc-werks value 'XXX',
c_bat type mch1-charg value '0000066584'.

Regards,

Rich Heilman

Re: select

Former Member — Fri, 11 Aug 2006 21:10:31 GMT

Hi,

If I remember correct mara-matnr is char of 10 .

So you need to pad the text with correct number of zero's to make the value as 10 digit.

Also,

You are using select endselect. I donno if this is by choice but checking l_mtart in the end of this will actually have the last read data so if you have 10 data for the same it will have value for the 10th data when your program progresses.

Also,

please try to get data in internal table and use that instead of using the select-endselect ( especially if it's avoidable) as this select-endselect decreases the performance in general.

Hope this helps.

Regards

Nishant

Re: select

VenuAnumayam — Fri, 11 Aug 2006 21:10:49 GMT

thnx rich.

but its not yielding anything.

i ran it in debug mode. nothing shows up in MARA table, while there are so many records exist.

whatz wrong with this.

thnx.

ravi

Re: select

Former Member — Fri, 11 Aug 2006 21:11:08 GMT

What is your requirement and why do you think there is an error in this code? You can simply combine the two into a join as below.


SELECT a~matnr INTO l_matnr
               FROM mch1 AS a
         INNER JOIN mara AS b
                 ON a~matnr = b~matnr
              WHERE b~mtart = 'FERT'
                AND b~matnr = c_mat
                AND a~charg = c_bat.
  WRITE:/ l_matnr.
ENDSELECT.

You can also change it to single selects as you have the primary keys of both the tables.


SELECT SINGLE mtart INTO l_mtart 
                    FROM mara AS b
                   WHERE matnr = c_mat.
CHECK sy-subrc = 0 AND
      l_mtart  = 'FERT'.
SELECT SINGLE matnr INTO l_matnr
                    FROM mch1
                   WHERE matnr = c_mat
                     AND charg = c_bat.
CHECK sy-subrc = 0.
WRITE:/ l_matnr.

Re: select

Former Member — Fri, 11 Aug 2006 21:13:52 GMT

> thnx rich.

> but its not yielding anything.

> i ran it in debug mode. nothing shows up in MARA

> table, while there are so many records exist.

> whatz wrong with this.

> thnx.

> ravi

That is probably because the material you chose is not a FERT material type. Instead of hardcoding the material number, try with a select option as follows.

SELECT-OPTIONS: s_matnr FOR l_matnr.

Now change the MARA SELECT as follows.

SELECT ... FROM MARA WHERE MATNR IN S_MATNR.

Re: select

VenuAnumayam — Fri, 11 Aug 2006 21:15:48 GMT

Hi.

i'm wriring this piece of code in a METHOD of CLASS which is in a BADI.

i'm afraid i can't use internal tables in OOABAP, if i'm correct.

Re: select

VenuAnumayam — Fri, 11 Aug 2006 21:20:29 GMT

Hi Srinivas,

i hve checked that material.

it is a FERT type.

Re: select

VenuAnumayam — Fri, 11 Aug 2006 21:22:20 GMT

Nishant,

how can avoid this select - endselect without using the internal table. can u plz give me an example.

thnx.

Re: select

Former Member — Fri, 11 Aug 2006 21:27:50 GMT

Hi,

You can use internal tables in OOABAP.

Also, just try using a variable in place of constant and try to change the value @ runtime. And yes please insert leading zero's while changing field content @ runtime and check if in one select you are able to get some result.

Regards

Nishant

Re: select

VenuAnumayam — Fri, 11 Aug 2006 21:44:12 GMT

i meant to say that we can't use internal tables in 'methods' of a class.

plz advise.

Re: select

RichHeilman — Fri, 11 Aug 2006 21:53:59 GMT

Yes, you can, you just can't use header lines.

For example, when declaring internal tables in a class/method.

data: imara type table of mara.
data: wmara like line of imara.

Regards,

Rich Heilman

Re: select

VenuAnumayam — Fri, 11 Aug 2006 22:07:41 GMT

But Rich,

I cant use "Loop - Endloop" in that context, right?

thnx,

Message was edited by: ravi kumar

Re: select

RichHeilman — Fri, 11 Aug 2006 22:14:29 GMT

Yes, you can. Take the example declaration in my previous post. You have an internal table imara.

loop at imara into wmara.
 write:/ wmara-matnr.
endloop.

Regards,

Rich Heilman

Re: select

VenuAnumayam — Fri, 11 Aug 2006 22:15:42 GMT

Rich,

Can you please advise me an approach for my requirement.

I'll post the code.

Thnx.

Re: select

RichHeilman — Fri, 11 Aug 2006 22:24:32 GMT

I need to know exactly what your requirement is, then I can give you the exact code.

Regards,

Rich Heilman

Re: select

VenuAnumayam — Fri, 11 Aug 2006 22:38:10 GMT

Rich,

The requirement is:

For transaction MSC2N, i'm implementing a BADI.

The details:

METHOD: IF_EX_BATCH_MASTER~CHECK_DATA_BEFORE_SAVE

BADI Implementation: ZM_BATCH_MASTER

BADI Definition: BATCH_MASTER

Enhancements for Batch Master Transactions

In that method, i'm writing the code.

Now for the values entered in transaction MSC2N,

(For eg: MATNR = 107901

CHARG = 665847

WERKS = USA)

1. First I hve to check the entered material type is 'FERT'.

2. If it is 'FERT'(finished good) material type, then for the entered batch, i need to get all the material numbers tied to that batch.

3. Now Check for any 'HALB'(bulk goods) material type materials exist in that.

4. For this 'HALB', I need to get the batch number.

For this functionality, i did the coding like this:

The error i'm getting is 'you cant use internal tables with header line in OO context' . if i declare the internal table without header line - it's giving error : 'internal table does not have header'.

Plz advise.

Thanks a ton.

<b>data: l_matnr type mara-matnr, "material number

l_charg type mch1-charg, "batch number

l_mtart type mara-mtart, "material type

l_bumat type mara-matnr. "bulk material

data: i_data type table of mcha.

data: w_data like line of i_data.

*-- Select the material type

select MTART into l_mtart from MARA

where matnr = i_batch_data_database-matnr.

endselect.

*-- Check if entered material number is a finished good

if l_mtart = 'FERT'.

*-- Get all material numbers associate with the batch

select MATNR into i_data-matnr from MCH1

where charg = i_batch_data_database-charg.

*-- Find the BULK material number

loop at i_data.

select MATNR into l_bumat from MARA

and mtart = 'HALB'.

endloop.

*-- Get the BULK batch number

select CHARG into l_charg from MCH1

where matnr = l_bumat.

endselect.</b>

Message was edited by: ravi kumar

Re: select

RichHeilman — Fri, 11 Aug 2006 22:50:42 GMT

The only concern i have with your requirement is about the checking the materials for a HALB. Do you want to only get the first one that is a HALB, or do you need all of them.

Here is the modified code, I didn't want to change too much here, I hope you are awhere that there may be a better way to select your data using inner joins.



data: l_matnr type mara-matnr, "material number
l_charg type mch1-charg, "batch number
l_mtart type mara-mtart, "material type
l_bumat type mara-matnr. "bulk material

data: i_data type table of mcha.
data: w_data like line of i_data.

*-- Select the material type
select <b>Single</b> MTART into l_mtart from MARA
where matnr = i_batch_data_database-matnr.
<b>*endselect.</b>

*-- Check if entered material number is a finished good
<b>if sy-subrc = 0 and</b> l_mtart = 'FERT'.

*-- Get all material numbers associate with the batch
select * into table i_data
*   MATNR into i_data-matnr 
       from MCH1
          where charg = i_batch_data_database-charg.

*-- Find the BULK material number
<b>loop at i_data into w_data.</b>
select Single MATNR into l_bumat from MARA
<b>          where matnr = w_data-matnr</b>
            and mtart = 'HALB'.
* If you get a hit for a material, get
* out of the loop.  I'm assuming that you want
* to get the first material that is a HALB.
If sy-subrc = 0.
exit.
endif.
endloop.

*-- Get the BULK batch number
select <b>Single</b>  CHARG into l_charg from MCH1
        where matnr = l_bumat.
<b>*endselect.</b>

Regards,

Rich Heilman

Sorry, I forgot about the itab/wa

Message was edited by: Rich Heilman

Re: select

VenuAnumayam — Fri, 11 Aug 2006 23:10:14 GMT

Rich,

I hve tried your code.

No errors found.

Thank you very much. I'll test it.

Message was edited by: ravi kumar

Re: select

RichHeilman — Fri, 11 Aug 2006 23:33:54 GMT

Cool, let me know.

Please make sure to award points for helpful answers and mark your post as solved when solved completely. Thanks.

Regards,

Rich Heilman