topic Re: how to declare variable in inner join in Application Development and Automation Discussions

how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 05:56:29 GMT

HI,

Present program i s not working ,pleas help me in doing it,

Error is showing in "vbak~ernam+0(1) EQ CB ."

data : begin of itab occurs 0,

kunnr like kna1-kunnr,

matnr like mara-matnr,

end of itab.

data : pvbeln like vbak-vbeln.

data ikna1 like kna1 occurs 0 with header line.

select kunnr from kna1 into table ikna1 up to 10 rows.

constants : CB type c value 'B'.

select mara~matnr into table itab from

( ( mara inner join vbap on mara_{matnr = vbap}matnr )

inner join vbak on vbap_{vbeln = vbak}vbeln )

for all entries in ikna1

where vbak_{kunnr = ikna1-kunnr and vbak}ernam+0(1) EQ CB .

loop at itab.

read table ikna1 with key kunnr = '20'.

itab-kunnr = ikna1-kunnr.

modify itab.

write:/ itab-kunnr,itab-matnr.

endloop.

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 06:02:33 GMT

i don't think you can put comparison like this "vbak~ernam+0(1) EQ CB " in your select statemnet.

You can better remove this from you select statement and then in loop you can put the check.

Regards

Naresh

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 06:06:28 GMT

Madhavi,

You cannot use offsets in select statement where conditions. Your select using join should be changed.

Look at the example.

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,
        carrid TYPE spfli-carrid,
        connid TYPE spfli-connid,
        fldate TYPE sflight-fldate,
        bookid TYPE sbook-bookid,
      END OF wa,
      itab LIKE SORTED TABLE OF wa
                WITH UNIQUE KEY carrid connid fldate bookid.

SELECT  p~carrid p~connid f~fldate b~bookid
  INTO  CORRESPONDING FIELDS OF TABLE itab
  FROM  ( ( spfli AS p
            INNER JOIN sflight AS f ON p~carrid = f~carrid AND
                                       p~connid = f~connid    )
            INNER JOIN sbook   AS b ON b~carrid = f~carrid AND
                                       b~connid = f~connid AND
                                       b~fldate = f~fldate     )
  WHERE p~cityfrom = 'FRANKFURT' AND
        p~cityto   = 'NEW YORK'  AND
        f~seatsmax > f~seatsocc.

LOOP AT itab INTO wa.
  AT NEW fldate.
    WRITE: / wa-carrid, wa-connid, wa-fldate.
  ENDAT.
  WRITE / wa-bookid.
ENDLOOP.

Thanks,

Message was edited by: Naren Somen

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 06:08:02 GMT

Madhavi,

You cannot specify the offset of the column from the table, in the SELECT statement.

You will have to select the data and then do a filtering or use a like operator.

Regards,

Ravi

Re: how to declare variable in inner join

hymavathi_oruganti — Wed, 14 Jun 2006 06:10:21 GMT

hi,

not only with inner join, with any select statement u can compare the expression directly like that.

first u need to store tha in a variable and then proceed.

select mara~matnr into table itab from

( ( mara inner join vbap on mara_{matnr = vbap}matnr )

inner join vbak on vbap_{vbeln = vbak}vbeln )

for all entries in ikna1

where vbak_{kunnr = ikna1-kunnr and vbak}ernam+0(1) EQ CB .

u can write the above like below.

select a~matnr into table itab from

mara as a inner join vbap as b

on a_{matnr = b}matnr

inner join vbak as c on b_{vbeln = c}vbeln

for all entries in ikna1

where vbak~kunnr = ikna1-kunnr.

loop at itab.

var = itab-ernam+0(1).

if var <> 'CB'.

delete itab.

endif.

endloop.

Re: how to declare variable in inner join

christian_wohlfahrt — Wed, 14 Jun 2006 06:15:24 GMT

Hi,

just define a range for ERNAM and fill this range with 'B*' -> you can select even much more complex search patterns.

Regards,

Christian

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 06:18:29 GMT

Hi Varisety,

first wen u say vbak~ernam+0(1), it can hold only one variable..how can u make a comparision with CB...

also, instead of

where vbak_{kunnr = ikna1-kunnr and vbak}ernam+0(1) EQ CB .

use this in ur code

where vbak_{kunnr = ikna1-kunnr and vbak}ernam like 'CB%'.

hope it helps..

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 06:20:05 GMT

You can do one more thing,

Declare one more field into internal table itab i.e

DATA : BEGIN OF itab OCCURS 0,

kunnr LIKE kna1-kunnr,

matnr LIKE mara-matnr,

ernam like vbak-ernam,

END OF itab.

in Select statement, select ERNAM also.

And while looping you can give where condition:

LOOP AT itab where ernam+0(1) EQ cb .

Hope you find your solution here.

Reward if helpful

Rgds,

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 06:33:03 GMT

you cannot use offset in select but you can use it in delete stament.

so--

select mara~matnr into table itab from

( ( mara inner join vbap on mara_{matnr = vbap}matnr )

inner join vbak on vbap_{vbeln = vbak}vbeln )

for all entries in ikna1

where vbak~kunnr = ikna1-kunnr.

delete itab where ernam+0(1) ne cb.

you dont have to use loop at itab.

Message was edited by: Sharath kumar R

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 06:40:15 GMT

Hi Madhavi,

The statement vbak~ernam+0(1) cannot be used directly in a where condition.

Write the following select query


select mara~matnr into table itab from
( ( mara inner join vbap on mara~matnr = vbap~matnr )
inner join vbak on vbap~vbeln = vbak~vbeln )
for all entries in ikna1
where vbak~kunnr = ikna1-kunnr 
  and vbak~ernam like 'B%' .

This select will return only vbak~ernam starting with 'B'

Sameena

Re: how to declare variable in inner join

Former Member — Wed, 14 Jun 2006 14:00:20 GMT

Hi,

while u r selecting from databse tables this won't work vbak~ernam+0(1) EQ CB .

correct statement is- vbak~ernam like 'C%' (pls try this now).

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

-> vbak~ernam+0(1) EQ CB <- This will works in read statement only(if u r reading from table).

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

Regards,

Ramesh.