topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Wed, 13 Sep 2006 13:02:28 GMT

hello friends,

I have to extrace data from a Data Base table depending on somw where sonditions.

I have an internal table it_bsid and it_knvp. it_bsid already has data and it has a fiels vbeln and it_bsid-vbeln is some times empty.

If it_bsid-vbeln is empty i have to get data into it_knvp.

below is the code but i am getting syntax errors.

SELECT kunnr

parvw

kunn2

INTO TABLE it_knvp

FROM knvp

FOR ALL ENTRIES IN it_bsid

WHERE it_bsid-vbeln is null and

kunnr EQ it_bsid-kunnr and

( parvw EQ 'AG' OR parvw EQ 'RE').

Any Advice.

Shejal Shetty.

Re: select statement

AFork — Wed, 13 Sep 2006 13:07:22 GMT

Hey,

try to change "into table it_knvp" to

"appending corresponding field of table it_knvp".

Hope this helps.

Greetings,

Achim

Message was edited by: Achim Fork

Re: select statement

Former Member — Wed, 13 Sep 2006 13:07:36 GMT

Hi,

the table that is used in 'FOR ALL ENTRIES' in your case it_bsid cannot not have any where condition on it.

This means that in where condition you cannot place any selection criteria on the table

it_bsid-vbeln is null <-- this is wrong

Regards

Nishant

I guess it would be better if you filter out the internal table and store some data in where vbeln is blank in another internal table and have nonblank entries in this table and then fire seperate select queries for both.

Message was edited by: Nishant Rustagi

Re: select statement

Former Member — Wed, 13 Sep 2006 13:08:51 GMT

SELECT kunnr

parvw

kunn2

INTO TABLE it_knvp

FROM knvp

FOR ALL ENTRIES IN it_bsid

WHERE it_bsid-vbeln is null and

kunnr EQ it_bsid-kunnr and

( parvw EQ 'AG' OR parvw EQ 'RE').

U cannot have the above condition in the select. If you take that out it would work fine...sure you would have more data than required in it_knvp table. Other wise you can do the below.

itab[] = it_bsid[].

delete itab where itab-vbeln is not null.

sort itab by kunnr.

delete adjacent duplicate comparing kunnr.

SELECT kunnr

parvw

kunn2

INTO TABLE it_knvp

FROM knvp

FOR ALL ENTRIES IN itab

WHERE kunnr EQ itab-kunnr and

( parvw EQ 'AG' OR parvw EQ 'RE').

Re: select statement

Former Member — Wed, 13 Sep 2006 13:14:04 GMT

Tnamks Guys,

I will try and let see what the results are.

Shejal.

Re: select statement

Former Member — Wed, 13 Sep 2006 13:50:48 GMT

Hi Shejal,

Here is another way:

tables: knvp. "(include knvp in the tables statement)

sort it_bsid by vbeln.

loop at it_bsid where vbeln is initial.

select single kunnr parvw kunn2

from knvp where kunnr eq it_bsid-kunnr

and ( parvw EQ 'AG' OR parvw EQ 'RE' ).

move knvp-kunnr to it_knvp-kunnr.

move knvp-parvw to it_knvp-parvw.

move knvp-kunn2 to it_knvp-kunn2.

append it_knvp.

clear it_knvp.

endloop.

Performance wise, this might take more time.

Regards,

Vivek