Problem with SELECT statements

[Former Member]

Hello,

I am working with 4 tables - lfa1, lfb1, bsik and bsak.

Initially i have to get 'LIFNR' from 'lfa1' based on some condition for 'BUKRS' in 'lfb1'.

Now, i should use these 'LIFNRs' and look up payments in 'bsik' and 'bsak'. I am stuck in the middle of writing the select statements. My code:

SELECT DISTINCT a~lifnr INTO itab-lifnr

FROM lfa1 AS a INNER JOIN lfb1 AS b ON a_{lifnr = b}lifnr

WHERE b~bukrs BETWEEN '100' AND '200'.

SELECT DISTINCT c_{lifnr c}dmbtr INTO CORRESPONDING FIELDS OF TABLE itab2

FROM bsik AS c INNER JOIN bsak AS d ON c_{lifnr = d}lifnr

WHERE c_{bukrs = d}bukrs AND

c~lifnr = itab-lifnr.

itab has field 'lifnr' and itab2 has fields 'lifnr' & 'dmbtr'. After executing these select statements, i am not getting expected output from the second select statement. Can someone tell me where am i going wrong.

Thanks.

[Former Member · Accepted solution]

In first select statement better use the ranges to fill get BUKRS value.

In second select use FOR ALLENTRIES. Before that check the driver table is empty or not.

Thanks

Eswar

[Former Member · Accepted solution]

Hi Eshwar,

Could you elaborate morre on how to use Ranges in 1st select statement.

I tried the FOR ALL ENTRIES in 2nd select statement. I still do not get any data for thid SELECT statement.

SELECT DISTINCT a~lifnr INTO itab-l_lifnr

FROM lfa1 AS a INNER JOIN lfb1 AS b ON a_{lifnr = b}lifnr

WHERE a~land1 NE 'US' AND

b~bukrs BETWEEN '1000' AND '5151'

ORDER BY a~lifnr.

SELECT DISTINCT c_{lifnr c}dmbtr FROM bsik AS c INTO TABLE itab2 FOR ALL ENTRIES IN itab

WHERE c~lifnr EQ itab-l_lifnr.

ENDSELECT.

Thanks.

[RichHeilman · Accepted solution]

Example of using ranges.

<b>ranges: r_bukrs for t001-bukrs.


r_bukrs-sign = 'I'.
r_bukrs-option = 'BT'.
r_bukrs-low = '1000'.
r_bukrs-high = '5151'.
append r_bukrs.</b>


select distinct a~lifnr into itab-l_lifnr
            from lfa1 as a 
                inner join lfb1 as b 
                     on a~lifnr = b~lifnr
                        where a~land1 ne 'US' 
                         <b> and b~bukrs in r_bukrs</b>
                                     order by a~lifnr.

Regards,

Rich Heilman

[RichHeilman · Accepted solution]

I think this may be pretty close to what you want.

ranges: r_bukrs for t001-bukrs.

<b>
data: begin of itab occurs 0,
      lifnr type lfa1-lifnr,
      end of itab.
data: begin of itab2 occurs 0,
      lifnr type lfa1-lifnr,
      dmbtr type bsik-dmbtr,
      end of itab2.</b>


r_bukrs-sign = 'I'.
r_bukrs-option = 'BT'.
r_bukrs-low = '1000'.
r_bukrs-high = '5151'.
append r_bukrs.



select distinct a~lifnr <b>into corresponding fields of table itab</b>            from lfa1 as a
                inner join lfb1 as b
                     on a~lifnr = b~lifnr
                        where a~land1 ne 'US'
                          and b~bukrs in r_bukrs
                                    order by a~lifnr.

check not itab[] is initial.
sort itab ascending by l_lifnr.

select distinct c~lifnr c~dmbtr from bsik as c into table itab2
           for all entries in itab
                    where c~lifnr eq itab-lifnr.

Regards,

Rich Heilman

Modified the field name in ITAB

Message was edited by: Rich Heilman

[Former Member · Accepted solution]

Hi Rob and Rich,

As i mentioned in my question, i want to lookup for payments from 'BSIK' and 'BSAK' based on the 'LIFNR' values i get from my 1st select statement.

Rob, i have ENDSELECT statement only for 2nd SELECT.

Please let me know how to retrieve values from the 2nd SELECT based on LIFNR from 1st SELECT.

Thanks.

[Former Member · Accepted solution]

Hi Sachin,

Use <b>For all entries in table (first internal table)</b> in the 2nd select.

[RichHeilman · Accepted solution]

This program is working good for me.

report zrich_0002.

ranges: r_bukrs for t001-bukrs.


data: begin of itab occurs 0,
      lifnr type lfa1-lifnr,
      end of itab.
data: begin of itab2 occurs 0,
      lifnr type lfa1-lifnr,
      dmbtr type bsik-dmbtr,
      end of itab2.
data: itab3 type table of bsak with header line.

r_bukrs-sign = 'I'.
r_bukrs-option = 'BT'.
r_bukrs-low = '0010'.
r_bukrs-high = '5151'.
append r_bukrs.



select distinct a~lifnr into corresponding fields of table itab
            from lfa1 as a
                inner join lfb1 as b
                     on a~lifnr = b~lifnr
                        where a~land1 = 'US' and
                          b~bukrs in r_bukrs
                                    order by a~lifnr.


select distinct c~lifnr c~dmbtr from bsik as c into table itab2
           for all entries in itab
                    where c~lifnr eq itab-lifnr.


select distinct * from bsak as c into table itab3
           for all entries in itab
                    where c~lifnr eq itab-lifnr.

check sy-subrc  = 0.

Regards,

Rich Heilman

[Former Member · Accepted solution]

Hi Rich,

This is what i have.

DATA: BEGIN OF itab OCCURS 0,

l_lifnr LIKE lfa1-lifnr,

END OF itab.

DATA: BEGIN OF itab2 OCCURS 0,

lifnr LIKE lfa1-lifnr,

dmbtr LIKE bsik-dmbtr,

END OF itab2.

SELECT DISTINCT a~lifnr INTO itab-l_lifnr

FROM lfa1 AS a INNER JOIN lfb1 AS b ON a_{lifnr = b}lifnr

WHERE a~land1 NE 'XX' AND

b~bukrs BETWEEN '1000' AND '5151'

ORDER BY a~lifnr.

WRITE: / itab-l_lifnr.

check not itab[] is initial.

sort itab ascending by l_lifnr.

SELECT DISTINCT c_{lifnr c}dmbtr FROM bsik AS c INTO TABLE itab2 FOR ALL ENTRIES IN itab

WHERE c~lifnr EQ itab-l_lifnr.

WRITE: / itab2-lifnr, itab2-dmbtr.

ENDSELECT. "This 'ENDSELECT' is for 2nd select.

For 1st WRITE statement, i am getting values. But, for the second WRITE, i am not gettingany output. I checked 'BSIK' contents by giving the same selection criteria. I am getting some records in the table but no records for the 2nd SELECT.

Please tell me where am in going wrong.

[Former Member · Accepted solution]

You may want the endselet to be for the second select, but it's actually for the first. The first select is into a work area and requires an endselect. The second one is into an internal table and odesn't need one. Re-formatting your code and doing a pretty print gives:

DATA: BEGIN OF itab OCCURS 0,
l_lifnr LIKE lfa1-lifnr,
END OF itab.

DATA: BEGIN OF itab2 OCCURS 0,
lifnr LIKE lfa1-lifnr,
dmbtr LIKE bsik-dmbtr,
END OF itab2.

SELECT DISTINCT a~lifnr INTO itab-l_lifnr
  FROM lfa1 AS a INNER JOIN lfb1 AS b ON a~lifnr = b~lifnr
  WHERE a~land1 NE 'XX' AND
  b~bukrs BETWEEN '1000' and '5151'
  ORDER BY a~lifnr.

  WRITE: / itab-l_lifnr.

  CHECK NOT itab[] IS INITIAL.
  SORT itab ASCENDING BY l_lifnr.

  SELECT DISTINCT c~lifnr c~dmbtr
     FROM bsik AS c INTO TABLE itab2
     FOR ALL ENTRIES IN itab
      WHERE c~lifnr EQ itab-l_lifnr.

  WRITE: / itab2-lifnr, itab2-dmbtr.

ENDSELECT. "This 'ENDSELECT' is for 1st select.

I haven't looked at Rich's code, but my advice would be to try it.

Rob

[RichHeilman · Accepted solution]

I have modified your code. the ENDSELECT is not for your second select statement it is for the first. The reason is that the second select statement is an array fetch(INTO TABLE...) Here is your modified code.

report zrich_0002.


data: begin of itab occurs 0,
l_lifnr like lfa1-lifnr,
end of itab.

data: begin of itab2 occurs 0,
lifnr like lfa1-lifnr,
dmbtr like bsik-dmbtr,
end of itab2.

select distinct a~lifnr
<b>*  into itab-l_lifnr
   into table itab</b>
from lfa1 as a inner join lfb1 as b on a~lifnr = b~lifnr
where a~land1 ne 'XX' and
b~bukrs between '1000' and '5151'
order by a~lifnr.

<b>*write: / itab-l_lifnr.</b>

check not itab[] is initial.
sort itab ascending by l_lifnr.

select distinct c~lifnr c~dmbtr from bsik as c into table itab2 for all
  entries in itab
  where c~lifnr eq itab-l_lifnr.


<b>loop at itab.

  write: / itab-l_lifnr.

  loop at itab2 where lifnr = itab-l_lifnr.
    write: / itab2-lifnr, itab2-dmbtr.
  endloop.

endloop.</b>

<b>*endselect. "This 'ENDSELECT' is for 2nd select.</b>

Regards,

Rich HEilman

[Former Member · Accepted solution]

Hi Rich,

I used the same code as you mentioned in your earlier post. I got an error when i had 'ENDSELECT' (Only one ENDSELECT and that was for second SELECT). The error stated that i do not have an OPEN SELECT for the ENDSELECT. So, i removed the ENDSELECT and the code got executed successfully. I still need to tweak it a little. I will confirm everything and will assign you full points.

Thanks.

[RichHeilman · Accepted solution]

Good work. In my code examples, I am using array fetchs(INTO TABLE.....) I never use SELECT ENDSELECT under any circumstances.

Regards,

Rich heilman

[former_member184495]

hi sachin,

if you are using lfa1, lfb1 and bsik.

try the following

*----

---

*itab1 internal table .

data: begin of itab1 occurs 0,

lifnr like lfa1-lifnr,

end of itab1.

*i_bsik internal table for bsik.

data: begin of i_bsik occurs 0,

lifnr like bsik-lifnr,

dmbtr like bsik-dmbtr,

end of i_bsik.

• first condition for lifnr from tables lfa1 & lfb1.

select distinct a~lifnr into itab1-lifnr

from lfa1 as a

inner join lfb1 as b on a_{lifnr = b}lifnr

where a_{land1 ne 'xx' and b}bukrs between '1000' and '5151'

order by a~lifnr.

*write:/ itab1.

endselect.

select lifnr dmbtr from bsik

into corresponding fields of table i_bsik

for all entries in itab1

where lifnr = itab1-lifnr.

loop at i_bsik.

write:/ i_bsik-lifnr, i_bsik-dmbtr.

endloop.

*----

---

if this solves your query, reward me with points

cheers,

<b>Aditya</b>.

[jayanthi_jayaraman]

Hi,

In the second select statement,if you look at the tables bsik and bsak,you can find more common key fields between those tables.But in your select statement you mentioned only lifnr for join condition.

That may be wrong.

Check that.

[Former Member]

If your internal table itab2 has only LIFNR and DMBTR, then I am afraid your INTO TABLE itab2 will not work. If there are multiple records for a given LIFNR, then your itab2 will only have one record. It is by design that INTO TABLE will only have distinct records fetched by your query. Instead of having just LIFNR and DMBTR, include also the key fields of BSIK and/or BSAK. This way you can get all the records that match your criteria. But if you cannot change your internal table structure, then you will have to loop at your itab and append each selected record to itab2. Try APPENDING CORRESPONDING FIELDS OF, instead of INTO CORRESPONDING FIELDS OF.

Srinivas

[Former Member]

Try this:

select a_{lifnr c}shkzg c~dmbtr

from ( ( lfa1 as a inner join lfb1 as b on a_{lifnr = b}lifnr ) inner join bsak as c on c_{lifnr = b}lifnr )

into table itab

where b~bukrs between '100' and '200'.

The shkzg is the debit/credit indicator.

Another thing, i dont see the reason why you have to use the lfa1 table at all. If vendor numbers are all you are after then all you need is to get them from lfb1 and use it to get line items from bsak.

[Former Member]

Feroz & Srinivas,

I will try your options and let you know.

Thanks for your help.

Message was edited by: Sachin Guptha

[Former Member]

Your second select statement does not make sence because there can be more than one line item in bsak for each vendor, hence doing a select distinct would not get the expected results.

I have done amd am currently doing a lot of ABAPing in RE,REFX and FI. Please explain clearly what you are after and i am sure i will be able to help you.

[Former Member]

Hi Feroz,

As i mentioned in my problem statement, for all vendors obtained from a join of lfa1 and lfb1 (with conditions on for land1 and bukrs in resp. tables), i need to look up their payments in BSIK and BSAK. I think i understood what you meant. I see duplicate records for few of the vendors in both tables. If i use SELECT DISTINCT, i might miss few of them.

I have a question about the 'SELECT' statements. As you can see from the code above, i have used 2 SELECT statements for joining 3 tables. For joining BSAK table, i have used another SELECT statement. Are there no nested SELECT statements in ABAP?

Kindly let me know.

Thanks.

[Former Member]

I think you've left out an endselect in your posted code for the first select. Where is it?

What do you want from the second select? Why do you need to join those two tables this way.

Rob

[Former Member]

You can try the option suggested by Eswar. The problem is you are using the itab-lifnr with out using the all entries option in which case the where condition checks the last available value for lifnr in the workarea of itab.

Hope this answers your query.