topic Re: I need help with the code... in Application Development and Automation Discussions

I need help with the code...

Former Member — Wed, 19 Aug 2009 13:06:46 GMT

Hi,

I conected two tables with left outer join, but now I need some additional data fro othher table... How I can put it togeather?

This sample doesn't put the additional data from ADRC table into int table...

Thanks for your help...

select *

into corresponding fields of table int

from IFLO as a

LEFT OUTER JOIN

AUSP as b

on a_{TPLNR = b}OBJEK

where ( TPLNR IN TPlNR ) and

( TPLMA IN TPLMA ) AND

( FLTYP IN FLTYP ) AND

( erdat IN erdat ) AND

( aedat IN aedat ) AND

( spras = 'E' ).

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

SELECT SINGLE * from ILOA WHERE ( TPLNR = iflo-TPLNR ) .

SELECT SINGLE * from ADRC WHERE ( addrnumber = iloa-adrnr ) .

IF sy-subrc = 0.

MOVE-CORRESPONDING adrc TO int.

ENDIF.

APPEND int.

Re: I need help with the code...

Former Member — Wed, 19 Aug 2009 13:37:46 GMT

Hi,

Try like this,


select *
into corresponding fields of table int
from IFLO as a
LEFT OUTER JOIN
AUSP as b
(on a~TPLNR = b~OBJEK)
LEFT OUTER JOIN 
IOLA as c
(ON a~TPLNR = c~TPLNR)
LEFT OUTER JOIN
ADRC as d
(ON c~adrnr = d~addrnumber)
where ( TPLNR IN TPlNR ) and
(a~TPLMA IN TPLMA ) AND
( a~FLTYP IN FLTYP ) AND
( a~erdat IN erdat ) AND
( c~aedat IN aedat ) AND
( c~spras = 'E' ).

Regards,

Edited by: vikred on Aug 19, 2009 7:10 PM

Re: I need help with the code...

Former Member — Wed, 19 Aug 2009 14:19:56 GMT

I got an error message: The column name "TPLNR" is unclear .

Re: I need help with the code...

Former Member — Wed, 19 Aug 2009 14:23:33 GMT

In my SAP system i dint find any tables such as IFLO and IOLA. So just based the select query you posted i suggested my solution. Check which fields correspond to which table and assign them correctly in the select join statement. It should work.

Regards,

Vik

Re: I need help with the code...

Former Member — Thu, 20 Aug 2009 06:04:55 GMT

Thanks Vid, please help me a little bit more... i will explain more in details....

i need to connect IFLO (standard table for Functional Location (View)) with the AUSP (Characteristic Values) as left outer join, then I need to connect IFLO and ILOA (with the key TPLNR) as inner join and check from ADRC table if there address exist and fill up the internal table also with adresses....

Example:

select *

into corresponding fields of table int

from IFLO as a

LEFT OUTER JOIN

AUSP as b

on a_{TPLNR = b}OBJEK

where ( TPLNR IN TPlNR ) and

( TPLMA IN TPLMA ) AND

( FLTYP IN FLTYP ) AND

( erdat IN erdat ) AND

( aedat IN aedat ) AND

( spras = 'E' ).

This is ok

*Here I want to conect IFLO-ILOA and ADRC and fill in the internal table with adress data....

SELECT SINGLE * from ILOA WHERE ( TPLNR = iflo-TPLNR ) .

SELECT SINGLE * from ADRC WHERE ( addrnumber = iloa-adrnr ) .

IF sy-subrc = 0.

MOVE-CORRESPONDING adrc TO int.

ENDIF.

APPEND int.

thanks a lot and nice day

Saso

Re: I need help with the code...

Former Member — Thu, 20 Aug 2009 06:23:44 GMT

Hi Saso,

Well then try something like this,

Have a seperate internal table for ADRC table and do the following


select * 
into corresponding fields of table int 
from IFLO as a 
LEFT OUTER JOIN 
AUSP as b 
( on a~TPLNR = b~OBJEK )
INNER JOIN
ILOA as c
( ON a~TPLNR = c~TPLNR)         "----------------> Add the inner join statement 
where ( TPLNR IN TPlNR ) and 
( TPLMA IN TPLMA ) AND 
( FLTYP IN FLTYP ) AND 
( erdat IN erdat ) AND 
( aedat IN aedat ) AND 
( spras = 'E' ). 


select * 
from adrc 
into table it_adrc 
for all entries in it
where addrnumber = it-adrnr.

sort it by adrnr.

loop at it_adrc.
read table it with key adrnr = it_adrc-addrnumber.
if sy-subrc = 0.
move-corresponding it_adrc to it.
modify it index sy-index.
endif.
endloop.

Regards,

Vik

Re: I need help with the code...

Former Member — Thu, 20 Aug 2009 08:14:39 GMT

Hi Vik,

Still the same error (The column name "TPLNR" is unclear) , i think the problem starts here:

where ( TPLNR IN TPlNR ) and

( TPLMA IN TPLMA ) AND

( FLTYP IN FLTYP ) AND

( erdat IN erdat ) AND

( aedat IN aedat ) AND

( spras = 'E' ).

this is related to IFLO table... without the new connection, sample is down, it works fine

select

into corresponding fields of table int

from IFLO as a

LEFT OUTER JOIN

AUSP as b

on a_{TPLNR = b}OBJEK

where ( TPLNR IN TPlNR ) and

( TPLMA IN TPLMA ) AND

( FLTYP IN FLTYP ) AND

( erdat IN erdat ) AND

( aedat IN aedat ) AND

( spras = 'E' ).

Sorry for my "ABAP blondness"

Saso

Re: I need help with the code...

Former Member — Thu, 20 Aug 2009 08:29:56 GMT

Hi,

The problem lies in the where condition. Even in the where conditions you have to use the alias name of the fields which correspond to the respective tables.

eg,


select * 
into corresponding fields of table int 
from IFLO as a 
LEFT OUTER JOIN 
AUSP as b 
( on a~TPLNR = b~OBJEK )
INNER JOIN
ILOA as c
( ON a~TPLNR = c~TPLNR)   
where a~TPLNR IN TPlNR and
a~TPLMA IN TPLMA  AND
a~FLTYP IN FLTYP  AND
 c~erdat IN erdat )AND
c~aedat IN aedat AND
c~spras = 'E' .

where a~ * fields refer to the fields of IFLO

and c~ * fields refer to the fields of ILOA.

I have just given a sample am not sure which field corresponds to which table. So assign the alias name to the fields in the where conditions and check if its working

Regards,

vik