topic select query in Application Development and Automation Discussions

select query

Former Member — Fri, 27 Feb 2009 05:05:29 GMT

Hi experts,

I am new to abap and i have to create a report in alv . i am getting problem in select statement.

These are my tables and it's fields.

TABLES : kna1,bsik,vbrk.

DATA : BEGIN OF itab1 OCCURS 0,

bukrs TYPE bsik-bukrs,

netwr TYPE vbrk-netwr,

mwsbk TYPE vbrk-mwsbk,

vbeln TYPE vbrk-vbeln,

kunrg TYPE vbrk-kunrg,

END OF itab1.

DATA : BEGIN OF itab2 OCCURS 0,

kunnr LIKE kna1-kunnr,

lifnr LIKE kna1-lifnr,

END OF itab2.

DATA : BEGIN OF itab3 OCCURS 0,

lifnr LIKE bsik-lifnr,

xblnr LIKE bsik-xblnr,

END OF itab3.

*********Select screen option******

SELECT-OPTIONS:s_bukrs FOR vbrk-bukrs.

**************************************************

****Select Query************************

i have to show netwr mwsbk vbeln kunrg fields of table vbrk in the output with condition that

vbeln = xblnr of bsik and

kunrg = kunnr of kna1.

i tried this select query using inner join :

select vbrk_{netwr vbrk}mwsbk vbrk_{vbeln vbrk}kunrg

kna1_{kunnr kna1}lifnr bsik_{xblnr bsik}lifnr

into corresponding fields of table itab1

from vbrk inner join bsik on vbrk_{vbeln = bsik}xblnr

inner join kna1 on vbrk_{kunrg = kna1}kunnr

and bsik_{lifnr = kna1}lifnr

where vbrk~bukrs IN s_bukrs.

Query doesn't give any output ,must be something wrong....soooo i need experts help

Thanks in advance,

Harsha Anand

Re: select query

Former Member — Fri, 27 Feb 2009 05:11:47 GMT

Hi,

Try rhis...

DATA : BEGIN OF itab1 OCCURS 0,
bukrs TYPE bsik-bukrs,
netwr TYPE vbrk-netwr,
mwsbk TYPE vbrk-mwsbk,
vbeln TYPE vbrk-vbeln,
kunrg TYPE vbrk-kunrg,
kunnr LIKE kna1-kunnr,
lifnr LIKE kna1-lifnr,
lifnr LIKE bsik-lifnr,
xblnr LIKE bsik-xblnr,
END OF itab1.

select bsik~bukrs vbrk~netwr vbrk~mwsbk vbrk~vbeln vbrk~kunrg
kna1~kunnr kna1~lifnr bsik~lifnr  bsik~xblnr 
into corresponding fields of table itab1
from vbrk inner join bsik on vbrk~vbeln = bsik~xblnr
inner join kna1 on vbrk~kunrg = kna1~kunnr
and bsik~lifnr = kna1~lifnr
where vbrk~bukrs IN s_bukrs.

Re: select query

Former Member — Fri, 27 Feb 2009 05:12:28 GMT

Hi,

the syntex of ur query is wrong use like this examle

select a~PLNTY

a~PLNNR

b~PLNKN

b~ZAEHL

b~DATUV

b~SUMNR

b~VORNR

b~STEUS

into table t_plpo

from PLas as a

inner join plpo as b

on a_{plnnr = b}plnnr and

a_{plnty = b}plnty and

a_{plnkn = b}plnkn and

a_{zaehl = b}zaehl

where a~plnnr eq itab_plaf-plnnr and

a~plnty eq itab_plaf-plnty and

a~plnal eq itab_plaf-alnal and

b~datuv <= itab_plaf-paltr and

b~loekz eq ' '.

and dont use occurs 0 its not supportd in OO context.

Use:TYPES:

BEGIN OF t_afru,

rueck TYPE afru-rueck, "Completion confirmation number

ism01 TYPE afru-ism01, "Labor Standard Value

ism02 TYPE afru-ism02.

END OF t_afru.

then have a internal table and work area

DATA:

i_t_afru TYPE STANDARD TABLE OF t_afru,

wa_t_afru TYPE t_afru.

Re: select query

Former Member — Fri, 27 Feb 2009 05:13:38 GMT

Please check some sample data in both the tables manually going into SE11. I doubt with and bsik_{lifnr = kna1}lifnr syntax. check once

Re: select query

Former Member — Fri, 27 Feb 2009 05:22:23 GMT

Hi,

If you use:

select vbrk_{netwr vbrk}mwsbk vbrk_{vbeln vbrk}kunrg

kna1_{kunnr kna1}lifnr bsik_{xblnr bsik}lifnr

into corresponding fields of table t_itab1

from vbrk inner join bsik on vbrk_{vbeln = bsik}xblnr

join kna1 on vbrk_{kunrg = kna1}kunnr

*and bsik_{lifnr = kna1}lifnr <<<<<<<<<<<<<Comment this line

where vbrk~bukrs IN s_bukrs.

You will get records.

Check wether there are any matchin records for bsik_{lifnr = kna1}lifnr

or remove the join cretria.

Hope this resolves your issue.

Regards,

Gurpreet

Re: select query

Former Member — Fri, 27 Feb 2009 05:25:04 GMT

Hi Harsha,

Check this.

REPORT ZTEST961.

TABLES : kna1,bsik,vbrk.

DATA : BEGIN OF itab1 OCCURS 0,

*netwr TYPE vbrk-netwr,

*mwsbk TYPE vbrk-mwsbk,

vbeln TYPE vbrk-vbeln,

kunnr LIKE kna1-kunnr,

bukrs TYPE bsik-bukrs,

*kunrg TYPE vbrk-kunrg,

END OF itab1.

*DATA : BEGIN OF itab2 OCCURS 0,

*kunnr LIKE kna1-kunnr,

*lifnr LIKE kna1-lifnr,

*END OF itab2.

*DATA : BEGIN OF itab3 OCCURS 0,

*lifnr LIKE bsik-lifnr,

*xblnr LIKE bsik-xblnr,

*END OF itab3.

*********Select screen option******

SELECT-OPTIONS:s_bukrs FOR vbrk-bukrs.

**************************************************

****Select Query************************

*i have to show netwr mwsbk vbeln kunrg fields of table vbrk in the output with condition that

*vbeln = xblnr of bsik and

*kunrg = kunnr of kna1.

*i tried this select query using inner join :

*select vbrk~netwr

vbrk~mwsbk
vbrk~vbeln
vbrk~kunrg
kna1~kunnr
kna1~lifnr
bsik~xblnr
bsik~lifnr

*into corresponding fields of table itab1

*from vbrk inner join bsik on vbrk_{vbeln = bsik}xblnr

*inner join kna1 on vbrk_{kunrg = kna1}kunnr

*and bsik_{lifnr = kna1}lifnr

*where vbrk~bukrs IN s_bukrs.

SELECT VBRK~VBELN

KNA1~KUNNR

BSIK~BUKRS

into table itab1

from vbrk inner join bsik on vbrk_{vbeln = bsik}xblnr

inner join kna1 on vbrk_{kunrg = kna1}kunnr

and bsik_{lifnr = kna1}lifnr

where vbrk~bukrs IN s_bukrs.

CHECK SY-SUBRC EQ 0.

I checked with test program, please add fiedls according to your choice.

Internal table declaration check once.

->Break the query

-> Add fields your choice.

Regards

Mohinder

Re: select query

Former Member — Fri, 27 Feb 2009 05:25:16 GMT

Thank you sir but it's giving an error:

'where vbrk~bukrs IN s_bukrs.'

' The IN operator with "S_BUKRS" is followed neighter by an internal table nor by a value list.'

Re: select query

Former Member — Fri, 27 Feb 2009 05:41:13 GMT

Can u tell me the common field in

1) VBRK and KNA1

2) VBRK and BSIK

Re: select query

Former Member — Fri, 27 Feb 2009 05:54:25 GMT

Common fields between :

VBRK and KNA1 :

KUNRG-----KUNNR.

VBRK and BSIK:

VBELN------XBLNR

BSIK and KAN1:

LIFNR------LIFNR.

Thanks

Re: select query

Former Member — Fri, 27 Feb 2009 05:59:47 GMT

I think U need to inner join the tables two times together.

Don't use itab1 itab2 itab3 etc.

Use proper naming conversions like gt_vbrk gt_vbsk gt_kna1

Use types instead of data and declare data after the declarations.

Re: select query

Former Member — Fri, 27 Feb 2009 06:08:05 GMT

Sirji error is solved

Thank U