Select statement

[Former Member]

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.

[Former Member]

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 <b>it_bsis-blart</b> = 'RV'.

Thanks,

Naren

[Former Member]

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.

[suresh_datti]

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

~Suresh

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

Thanks everyone,

Shejal Shetty.

[Former Member]

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

[Former Member]

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.

[Former Member]

Hi Shejal,

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

[suresh_datti]

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

[Former Member]

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

[Former Member]

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 <b>it_bsis_tmp</b>

WHERE vbrp~vbeln = <b>it_bsis_tmp</b>-belnr.

Thanks,

Naren

[Former Member]

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

[Former Member]

Shejal,

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

Santosh