topic Re: Select statement in Application Development and Automation Discussions

Select statement

Former Member — Wed, 15 Nov 2006 18:28:04 GMT

Hello friends,

I am trying to use this select statement and its giving me a syntax error.

Error-

Field it_bsis-blart is unknown.

SELECT vbrk~vbeln

vbrp~matnr

vbrk~kunag

vbrp~aubel

vbrp~fkimg

INTO TABLE it_vbrp

FROM vbrk

INNER JOIN vbrp

ON vbrk_{vbeln EQ vbrp}vbeln

FOR ALL entries IN it_bsis

WHERE vbrp~vbeln = it_bsis-belnr

AND it_bsis-blart = 'RV'.

I have data in blart field in it_bsis Table.

any suggestions,

Shejal.

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:30:11 GMT

Hi,

If you are using FOR ALL ENTRIES the source and the target field shoud have the same data type and length..

What is the error message you are getting..

Also ..There is no field called IT_BSIS-BLART in the table VBRK and VBRP..

FOR ALL entries IN it_bsis

WHERE vbrp~vbeln = it_bsis-belnr

AND it_bsis-blart = 'RV'.

Thanks,

Naren

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:31:22 GMT

You are firing a SELECT statement with internal table field in the WHERE clause of the SELECT. You cannot do that.

IT_BSIS is a internal table BLART is field of the internal and not the database table VBRK or VBRP, so you cannot use that in the where clause

Regards,

Ravi

Note - Please mark the helpful answers

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:32:08 GMT

Hi shejal ,

I have modified the select staement . Please use the below one.

SELECT vbrk~vbeln

vbrp~matnr

vbrk~kunag

vbrp~aubel

vbrp~fkimg

INTO TABLE it_vbrp

FROM vbrk

INNER JOIN vbrp

ON vbrk_{vbeln EQ vbrp}vbeln

FOR ALL entries IN it_bsis

WHERE vbrp~vbeln = it_bsis-belnr

AND vbrk~blart = 'RV'.

Regards,

siva.

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:33:04 GMT

Hi Shejal,

You are actually selecting data from vbrk, and while syntax check it checks whether all the fields listed in the WHERE condition are present in vbrk so its showing an syntax error.

Re: Select statement

suresh_datti — Wed, 15 Nov 2006 18:34:16 GMT

you cannot use it_bsis-blart on the left handside.. the fields on the left hand side should be available in the tables you are SELECTing from..

~Suresh

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:35:34 GMT

Hi Shejal,

The syntax is due to you didnt have any field with name 'it_bsis-blart' in it_vbrp.

When you are building an expression in where condition the left hand field should be in specified table. But in your case you didnt have 'it_bsis-blart' in it_vbrp, so this causes to syntax error.

Just comment this line and check for syntax errors.

Thanks,

Vinay

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:39:18 GMT

Thanks all for the suggestions,

So how do I handle this problem without using the loop statement.

My requirements.

I have to access VBRK and VBRP table and get data into it_vbrp only for those records in it_bsid where blart = RV and comparing it_bsid-vbeln.

I was thinkin gof using the loop at it_bsid where blart = 'RV, and endloop. and write the code withing the loop for selecting.

But that is talking long time for extraction.

Any suggestions.

Thanks in advance,

Shejal.

Re: Select statement

suresh_datti — Wed, 15 Nov 2006 18:41:05 GMT

move the 'RV' entries into another itab & use that itab in the FOR ALL ENTRIES statement..

~Suresh

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:43:10 GMT

IT_BSID_TEMP[] = IT_BSIS[].

DELETE IT_BSID_TEMP WHERE BLART <> 'RV'.

Now use IT_BSID_TEMP in the for all entries clause.

Regards,

Ravi

Note Please mark all the helpful answers

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:44:16 GMT

Hi,

Move the records in the internal table IT_BSIS to temporary internal table IT_BSIS_TMP and remove the records that are not RV.

DATA: IT_BSIS_TMP LIKE IT_BSIS OCCURS 0 WITH HEADER LINE.

IT_BSIS_TMP[] = IT_BSIS[].

DELETE IT_BSIS_TMP WHERE BLART <> 'RV'.

SELECT vbrk~vbeln

vbrp~matnr

vbrk~kunag

vbrp~aubel

vbrp~fkimg

INTO TABLE it_vbrp

FROM vbrk

INNER JOIN vbrp

ON vbrk_{vbeln EQ vbrp}vbeln

FOR ALL entries IN it_bsis_tmp

WHERE vbrp~vbeln = it_bsis_tmp-belnr.

Thanks,

Naren

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:45:17 GMT

Thanks for the suggestion Ravikumar.

I have to check for many different cases where Blart = RV, SA and so on...

Is this the only best possible solution or ...any more advice,

Shejal.

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:48:08 GMT

from a performance perspective, this is the best solution.

Else you have to loop at the internal table, and you know the side effects of the same.

Regards,

Ravi

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:49:33 GMT

Hi,

I don't think you have another solution other than delete ... <> ... and use that internal table..

Otherwise you have use LOOP AT..WHERE ..And use the select with in the loop..

Thanks,

Naren

Re: Select statement

Former Member — Wed, 15 Nov 2006 18:50:54 GMT

Thanks everyone,

Shejal Shetty.

Re: Select statement

Former Member — Wed, 15 Nov 2006 19:01:04 GMT

Shejal,

I dont think we can specify itab-field = <cond> in WHERE clause of SELECT.

Santosh