Please help me with my select statement logic...
I am currently having a problem on how to go about this. Here is the scenario of the problem:
I am looping at internal table named it_equz. Now, I would get ANLNR from table ITOB where equnr EQ it_equz-EQUNR. after getting the asset which is ANLNR, I moved it to itab it_finaltab-asset. Now I need to get the values of certain fields in table ANLC where its ANLN1 eq it_finaltab-asset. But the values which I must get must be the latest and is not DEACTIVATED. This is where the problem lies, to know if the values that I get is not deactivated I must check the field DEAKT in table ANLA. Anyway, below is my code that I did:
loop at it_equz.
SELECT SINGLE anlnr FROM itob
WHERE equnr = it_equz-equnr "first step
MOVE it_itob-anlnr TO it_finaltab-asset.
SELECT SINGLE bukrs anln1 gjahr afabe knafa
kansw answl kaafa nafal
nafap aafap nafav nafag
INTO (it_anlc-bukrs, it_anlc-anln1, it_anlc-gjahr,
it_anlc-afabe, it_anlc-knafa, it_anlc-kansw,
it_anlc-answl, it_anlc-kaafa, it_anlc-nafal,
it_anlc-aafav, it_anlc-aafag, it_anlc-nafap,
it_anlc-aafap, it_anlc-nafav, it_anlc-nafag)
WHERE anln1 EQ it_finaltab-asset
AND bukrs IN r_iwerk
AND afabe EQ '01'.
So, how can I know if the values that I am getting at ANLC is not deactivated since the indicator is in the table ANLA which is the field DEAKT.
Again, thank you guys and have a nice day!
Rich Heilman replied
I would suggest using an inner join here. I'm don't know the key of the tables since I'm not in front of an R/3 system.
SELECT SINGLE anlc~bukrs anlc~anln1 anlc~gjahr anlc~afabe anlc~knafa anlc~kansw anlc~answl anlc~kaafa anlc~nafal anlc~aafav anlc~aafag anlc~nafap anlc~aafap anlc~nafav anlc~nafag FROM anlc <b> inner join ANLA on anlc~key1 = anla~key1 and anlc~key2 = anla~key2</b> INTO (it_anlc-bukrs, it_anlc-anln1, it_anlc-gjahr, it_anlc-afabe, it_anlc-knafa, it_anlc-kansw, it_anlc-answl, it_anlc-kaafa, it_anlc-nafal, it_anlc-aafav, it_anlc-aafag, it_anlc-nafap, it_anlc-aafap, it_anlc-nafav, it_anlc-nafag) WHERE anlc~anln1 EQ it_finaltab-asset AND anlc~bukrs IN r_iwerk AND anlc~afabe EQ '01' <b>and anla~deakt = space.</b>