topic Re: Select query ..... in Application Development and Automation Discussions

Select query .....

Former Member — Tue, 02 Oct 2007 09:31:39 GMT

Hi Experts,

I wanted to select all material from Mara where 12th digit is matching with S,R and F. How to write in Select statement ?

I have used like :

Select * from Mara into table it_Mara

Where Matnr IN Matnr1 AND

Matkl IN Matkl1 AND

Mtart = 'ZFIN' AND

Matnr+0(12) IN ('S','R','F').

But it is giving error. Pl.. give us solution.

Yusuf

Re: Select query .....

Former Member — Tue, 02 Oct 2007 09:36:26 GMT

check this

Select * from Mara into table it_Mara

Where Matnr IN Matnr1 AND

Matkl IN Matkl1 AND

Mtart = 'ZFIN' AND

Matnr+11(1) IN ('S','R','F').

or do like this

Select * from Mara into table it_Mara

Where Matnr IN Matnr1 AND

Matkl IN Matkl1 AND

Mtart = 'ZFIN'.

Loop at it_mara.

if not it_mara-Matnr+11(1) IN ('S','R','F').

delete it_mara index sy-tabix.

endif.

Regards

Anji

Message was edited by:

Anji Reddy Vangala

Re: Select query .....

Former Member — Tue, 02 Oct 2007 09:37:55 GMT

Hi Yusuf,

You <b>can not</b> use offset on fields in select query on the left hand side of expression in the WHERE condition.

You need to get the data first into internal table and have query of 12th digit in where condition on internal table.

Check this code.

Select * from Mara into table it_Mara

Where Matnr IN Matnr1 AND

Matkl IN Matkl1 AND

Mtart = 'ZFIN'.

LOOP AT IT_MARA.

IF IT_MARA-MATNR+11(1) = 'S' OR

IT_MARA-MATNR+11(1) = 'F' OR

IT_MARA-MATNR+11(1) = 'R'.

DELETE IT_MARA.

ENDIF.

ENDLOOP.

Thanks,

Vinay

Re: Select query .....

Former Member — Tue, 02 Oct 2007 09:45:38 GMT

Hi Yusuf,

You can use WILD Card option while selecting records:

Suggest to do search in SDN with key - '' <b>Wild Card'.</b>

Will get a lot of helpful discussions.

Regards,

Manish

Re: Select query .....

Former Member — Tue, 02 Oct 2007 09:46:36 GMT

Hi Yusuf,

it's not possible to select the way you want it, because fieldname Matnr+0(12)

is unknown.

You have to use Wildcards like

'_' -> for one letter -> you need 11 underscores before your 'R;S;F'

'%' -> for any string

So do this:

Select * from Mara into table it_Mara

Where Matnr IN Matnr1 AND

Matkl IN Matkl1 AND

Mtart = 'ZFIN' AND

( Matnr LIKE '____________R%' or

Matnr LIKE '____________S%' or

matnr LIKE '____________S%' ).

But this will do a full table scan and kill your performance.

Try to select mara into internal table and then filter within your program.

Regards Henner

Re: Select query .....

Former Member — Tue, 02 Oct 2007 09:47:38 GMT

Hi Vinaykumar,

U R Right. It wont work. At last I took data in internal table and removed apart from S,R,F as per your path.

Yusuf

Re: Select query .....

Former Member — Tue, 02 Oct 2007 09:59:08 GMT

Hi Yusuf,

U can try a more easy and a simple thing which is:

First select all the matnr from Mara, without any comprehencive match for S,R, F.

Then Take a local variable, say T_matnr.

Move mara-matnr11(1) to T_matnr.

Now again apply ur select query.

Select * from Mara into table it_Mara

Where Matnr IN T_matnr AND

Matkl IN Matkl1 AND

Mtart = 'ZFIN' .

This is the most optimised way you can apply to ur select query.

Hope this serves ur purpose.

Do assign points.

Regards,

S.Agarwal