select query problem

[Former Member]

hi friends,

i am selecting vbeln from vbrk .

and fkimg netwr from vbrp.

i am able to get values for first line item only.

how to slect for multiple line item.

SELECT kschl knumv kwert kposn FROM konv

INTO CORRESPONDING FIELDS OF TABLE cond

WHERE ( kschl = 'JCST' OR kschl = 'ZCST' ).

IF cond[] IS NOT INITIAL.

SELECT vbeln knumv fkdat kunag kunrg FROM vbrk

INTO CORRESPONDING FIELDS OF TABLE itab

FOR ALL ENTRIES IN cond

WHERE knumv = cond-knumv

AND vbeln IN vbeln.

ENDIF.

IF itab[] IS NOT INITIAL.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

• and posnr = cond-kposn.

APPEND item.

ENDSELECT.

ENDIF.

can any one correct my code.

thanks.

[Former Member]

hi,

why dont u write query like this?

try this.

IF itab[] IS NOT INITIAL.

<b>SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF TABLE item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.</b>* and posnr = cond-kposn.

ENDIF.

hope it will b helpful.

[former_member404244]

Hi,

change the select query like this

IF itab[] IS NOT INITIAL.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF table item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

ENDIF.

Regards,

Nagaraj

[Former Member]

Hi Sonu,

While using for all entries one should specify internal table for the result set(selection from DB tables) as it will return all the selected rows at once(hence, internal table needs to be specified).

Also if you use for all entries on an internal table table

for eg: SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

Remember to sort ITAB based on VBELN, before it is used in a FOR ALL ENTRIES statement.

Following piece of code snippet will resolver you issue:

SORT ITAB by VBELN.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF table item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

Replace the select on VBRP with the above code and it will definitely work.

<b><REMOVED BY MODERATOR></b>

Regards,

Dilli

Message was edited by:

Alvaro Tejada Galindo

[former_member189629]

Sonu,

I would say u will have to change the sequence completely.

First avoid using INTO CORRESPONDING FIELDS

Instead let itab have the structure that includes only the fields u select (vbeln knumv fkdat kunag kunrg in this case)

THis is ur code:

SELECT kschl knumv kwert kposn FROM konv

INTO CORRESPONDING FIELDS OF TABLE cond

WHERE ( kschl = 'JCST' OR kschl = 'ZCST' ).

IF cond[] IS NOT INITIAL.

SELECT vbeln knumv fkdat kunag kunrg FROM vbrk

INTO CORRESPONDING FIELDS OF TABLE itab

FOR ALL ENTRIES IN cond

WHERE knumv = cond-knumv

AND vbeln IN vbeln.

ENDIF.

IF itab[] IS NOT INITIAL.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

• and posnr = cond-kposn.

APPEND item.

ENDSELECT.

ENDIF.

You must first access the header, then item and then the condition table instead...

<b>Instead use this</b>

<b>SELECT vbeln knumv fkdat kunag kunrg FROM vbrk

INTO TABLE itab

WHERE vbeln IN vbeln.

if sy-subrc = 0.

if not itab[] is initial.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO TABLE item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

SELECT kschl knumv kwert kposn FROM konv

INTO TABLE cond

FOR ALL ENTRIES IN ITAB

WHERE knumv = itab-knumv

and ( kschl = 'JCST' OR kschl = 'ZCST' ).

endif.

endif.

</b>

REMEMBER THESE:

- ALWAYS USE PKeys in WHERE CONDITIONS

- AVOID USING INTO CORRESPONDING FIELDS

- AVOID SELECT---ENDSELECT

<b><REMOVED BY MODERATOR></b>

Message was edited by:

Karthik

Message was edited by:

Karthik

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

thanks for reply.

my problem is i want to select those document no who's

doc type kschl = 'JCST' or "ZCST'.

also i am not able to get netwr and fkimg line item wise.

tnnx.

[former_member189629]

I would say don't add them in the where condition. or even if u add them, use a READ on the COND table using key KNUMV = ITAB-KNUMV with a BINARY SEARCH.

Use this pattern

loop at ITAB.

READ TABLE COND WITH KEY KNUMV = ITAB-KNUMV BINARY SEARCH.

IF SY-SUBRC = 0.

IF COND-KSCHL = 'JCST'

• populate respective fields in the output tab

ELSEIF COND-KSCHL = 'ZCST'.

• populate respective fields in the output tab

ENDIF.

ENDIF.

ENDLOOP.

[Former Member]

hi kartik thanks.

can u tell me how to select netwr and fkimg line item wise.

means suppose there is one sales order which contain 3 line item.

i am getting for first not remaining two.

how to select that netpr and fkimg line item wise.

tnx

[former_member189629]

ok ok. got ur prob.

see, once u pass the KNUMV to the table KONV, you will see as many items u see in VBRP for the same VBELN whose KNUMV u pass to KONV. For more clarity, check 1 VBELN in VBRK (from se11/16) take the same VBELN to VBRP and see the number of items. Now, go to KONV with the same VBELN's KNUMV and you will see the same no of items (VBRK-KNUMV = KONV-KNUMV & VBRP-POSNR = KONV-KPOSN).

So in this case,

Loop at VBRK.

LOOP AT VBRP where VBELN = VBRK-VBELN.

clear KONV.

read table KONV with key KNUMV = VBRK-KNUMV

KPOSN = VBRP-POSNR

BINARY SEARCH.

• Now use your IF/CASE to populate your price fields accordingly..

ENDLOOP.

Endloop.

Lemme know if u still have probs. The above code is generic. u will have to modify it as per ur internal table names.

[Former Member]

hi kartik,

i check that. posnr is equal to kposn.

but now i will simplify my problem

suppose there is one sales order.

1000004148. this sales order contain three line item.

item mat quan price

10 material 1 100 2000

20 mat 2 200 300

30 mat3 200 900

how to select this against one sales order no.

hope u will understand.

i am able to get for first item not for othrt two.

[former_member189629]

pl be clear. what fields r u not gettin?

[Former Member]

i am selecting fkimg invoice quanty and netwr price form vbrp .

i am able to get for first line item .

but if sales order contain more than one line item

then it will quantity and price for 2 and 3 rd item

[Former Member]

i am selecting fkimg invoice quanty and netwr price form vbrp .

i am able to get for first line item .

but if sales order contain more than one line item

then it will not give quantity and price for 2 and 3 rd item

[former_member189629]

have u checked the values in VBRP after thye select stmt? it will surely have multiple lines. i still din get ur problem. if poss paste the code again.

[Former Member]

hi kartik,

SELECT kschl knumv kwert kposn FROM konv

INTO CORRESPONDING FIELDS OF TABLE cond

WHERE ( kschl = 'JCST' OR kschl = 'ZCST' ).

IF cond[] IS NOT INITIAL.

SELECT vbeln knumv fkdat kunag kunrg FROM vbrk

INTO CORRESPONDING FIELDS OF TABLE itab

FOR ALL ENTRIES IN cond

WHERE knumv = cond-knumv

AND vbeln IN vbeln.

sort itab[] by vbeln.

ENDIF.

IF itab[] IS NOT INITIAL.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

APPEND item.

ENDSELECT.

ENDIF.

against one sales order no suppose 10000004148.

it may contain multiple line item.

10 mat1 200 2000.00

20 mat2 300 3000.00

30 mat3 400 4000.00

i am able to get value for first item in thia case 10 mat1 200 2000.00

but i am not able to get values for 20 mat2 300 3000.00

30 mat3 400 4000.00

hope now problem is clear

[Former Member]

Hi,

Replace the select on VBRP with the following codes,it will definitely work.

SORT ITAB by VBELN.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF table item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

NOTE: Need to add 'table' keyword in the select query on VBRP in your code.

-

---

Hope this resolve your issue.

<b><REMOVED BY MODERATOR></b>

Regards,

Dilli

Message was edited by:

Alvaro Tejada Galindo

[Former Member]

hi ,

i tried that it is giving run time error.

any other solution

thanks

[Former Member]

Hi,

You need to specify the fields in the select query of VBRP in the same order in which it appears in the internal table ITEM.

If it gives error , let me know...

Regards,

Dilli

[Former Member]

working fine.

<b>but still i am not able toget values for second line item .</b>hope u understood my problem

[former_member189629]

Use this code Sonu. Ensure the ITEM table has a header line. <b>Clear the header line before every append.</b>

IF itab[] IS NOT INITIAL.

SELECT vbeln fkimg netwr posnr FROM vbrp

INTO CORRESPONDING FIELDS OF item

FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

APPEND item.

<b>CLEAR ITEM.</b>" Clear the header

ENDSELECT.

ENDIF.

This should help

Message was edited by:

Karthik

[Former Member]

hi

good

you take three internal table rather than taking a single internal table,and use append statement to add the data into the internal table.

thanks

mrutyun^

[Former Member]

Sonu - I executed your code and retrieved multiple line items for VBRP. I think your problem lies elsewhere.

Rob

[Former Member]

Hi sonu,

I have checked ur code, it is working fine, and if there are two entries into vbrp for the same vbeln then also quantity and value is coming.

I thing in your vbrp table the values are not present. Plz check it out.

Thanks

Dharmishta