topic Re: select query problem in Application Development and Automation Discussions

select query problem

Former Member — Fri, 14 Sep 2007 09:08:30 GMT

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.

Re: select query problem

Former Member — Fri, 14 Sep 2007 09:12:11 GMT

hi,

why dont u write query like this?

try 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.* and posnr = cond-kposn.

ENDIF.

hope it will b helpful.

Re: select query problem

former_member404244 — Fri, 14 Sep 2007 09:12:33 GMT

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

Re: select query problem

Former Member — Fri, 14 Sep 2007 09:17:46 GMT

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.

Regards,

Dilli

Message was edited by:

Alvaro Tejada Galindo

Re: select query problem

former_member189629 — Fri, 14 Sep 2007 09:18:01 GMT

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...

Instead use this

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.

REMEMBER THESE:

- ALWAYS USE PKeys in WHERE CONDITIONS

- AVOID USING INTO CORRESPONDING FIELDS

- AVOID SELECT---ENDSELECT

Message was edited by:

Karthik

Message was edited by:

Karthik

Message was edited by:

Alvaro Tejada Galindo

Re: select query problem

Former Member — Fri, 14 Sep 2007 09:28:59 GMT

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.

Re: select query problem

former_member189629 — Fri, 14 Sep 2007 09:37:26 GMT

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.

ENDLOOP.

Re: select query problem

Former Member — Fri, 14 Sep 2007 09:47:41 GMT

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

Re: select query problem

former_member189629 — Fri, 14 Sep 2007 09:58:57 GMT

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.

Re: select query problem

Former Member — Fri, 14 Sep 2007 10:15:04 GMT

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.

Re: select query problem

former_member189629 — Fri, 14 Sep 2007 10:19:44 GMT

pl be clear. what fields r u not gettin?

Re: select query problem

Former Member — Fri, 14 Sep 2007 10:36:31 GMT

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

Re: select query problem

Former Member — Fri, 14 Sep 2007 10:37:47 GMT

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

Re: select query problem

Former Member — Fri, 14 Sep 2007 10:38:45 GMT

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^

Re: select query problem

former_member189629 — Fri, 14 Sep 2007 10:44:08 GMT

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.

Re: select query problem

Former Member — Fri, 14 Sep 2007 11:03:14 GMT

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

Re: select query problem

Former Member — Fri, 14 Sep 2007 11:12:22 GMT

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.

Regards,

Dilli

Message was edited by:

Alvaro Tejada Galindo

Re: select query problem

Former Member — Fri, 14 Sep 2007 11:24:17 GMT

hi ,

i tried that it is giving run time error.

any other solution

thanks

Re: select query problem

Former Member — Fri, 14 Sep 2007 11:38:05 GMT

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

Re: select query problem

Former Member — Fri, 14 Sep 2007 11:56:34 GMT

working fine.

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