Help with Internal table - Urgent Please

[Former Member]

I had an internal table declared as below:

DATA: BEGIN OF i_results OCCURS 0,

pernr like p0014-pernr,

begda like zhrccroll-begda, "Cost center roll out begin date

endda like zhrccroll-endda, "Cost center roll out end date

aedtm like p0014-aedtm,

uname like p0014-uname,

kostl like p0001-kostl,

ittype(7),

END OF i_results.

And my SQL to populate the table based on some other results:

select t1_{pernr t3}begda t3_{endda t1}aedtm t1_{uname t2}kostl

appending corresponding fields of table i_results

from pa0014 as t1

inner join pa0001 as t2

on t1_{pernr = t2}pernr

inner join zhrccroll as t3

on t2_{kostl = t3}kostl

for all entries in i_it01

where t1~pernr = i_it01-pernr

and t2~kostl = i_it01-kostl

and t3~kostl = i_it01-kostl

and t3~syst = 'STAFF'

and t1~aedtm = p_date

and t1~uname in so_name.

Everything was working great and now user wants to see additional dates on the report so I have to add couple of dates to internal table but unfortunately they have same names (begda, endda), Now I need internal table like this:

DATA: BEGIN OF i_results OCCURS 0,

pernr like p0014-pernr,

begda like zhrccroll-begda, "Cost center roll out begin date

endda like zhrccroll-endda, "Cost center roll out end date

aedtm like p0014-aedtm,

uname like p0014-uname,

kostl like p0001-kostl,

begda like p0014-begda, "infotype begin date

endda like p0014-endda, "infotype end date

ittype(7),

END OF i_results.

I cannot have duplicate declarations in internal table and also my SQL would not work. The new SQL would be:

select t1_{pernr t3}begda t3_{endda t1}aedtm t1_{uname t2}kostl

t1_{begda t1}endda

appending corresponding fields of table i_results

from pa0014 as t1

inner join pa0001 as t2

on t1_{pernr = t2}pernr

inner join zhrccroll as t3

on t2_{kostl = t3}kostl

for all entries in i_it01

where t1~pernr = i_it01-pernr

and t2~kostl = i_it01-kostl

and t3~kostl = i_it01-kostl

and t3~syst = 'STAFF'

and t1~aedtm = p_date

and t1~uname in so_name.

This is not working either.

Could comeone please help me how to acheive the desired result. I am trying to get all the data in one shot, and that is why I have the SQL above. Any ideas you could provide would be greatly appreciated.

Thanks in advance.

Mithun

[Former Member]

One SELECT with joins unfortunately cannot achieve this as APPENDING CORRESPONDING FIEDLS will match the fields by field name.

So you have to use a diff. strategy. Select from first table into internal table and used select on second table with FOR ALL ENTRIES in internal table.

<b>CORRECTION!!!</b>

I think there is a solution for this

SELECT t1_{begda AS my_begda1 t2}begda AS my_begda2

here my_begda1 and my_begda2 are your internal table fiedls..

Solved!!!

[Former Member]

HI Mithun,

To add to vishnu do not use "appending corresponding fields of"..

1) if you are reading this data again and again then use

"appending table itab"

2) if it is triggered just once then use

"into table itab"

And also define ur itab as:

DATA: BEGIN OF i_results OCCURS 0,

pernr like p0014-pernr,

ccbegda like zhrccroll-begda, "Cost center roll out begin date

ccendda like zhrccroll-endda, "Cost center roll out end date

aedtm like p0014-aedtm,

uname like p0014-uname,

kostl like p0001-kostl,

begda like p0014-begda, "infotype begin date

endda like p0014-endda, "infotype end date

ittype(7),

END OF i_results.

and your select statement as:

select t1_{pernr t3}begda as ccbegda t3_{endda as ccenda t1}aedtm t1~uname

t2_{kostl t1}begda t1~endda into table i_results

from pa0014 as t1

inner join pa0001 as t2

on t1_{pernr = t2}pernr

inner join zhrccroll as t3

on t2_{kostl = t3}kostl

for all entries in i_it01

where t1~pernr = i_it01-pernr

and t2~kostl = i_it01-kostl

and t3~kostl = i_it01-kostl

and t3~syst = 'STAFF'

and t1~aedtm = p_date

and t1~uname in so_name

I am sure you need to tweak and twist the above statements a bit..

Hope this helps..

BR

Rakesh

PS: Please close the thread if your problem is solved..

[Clemenss]

Hi Rakesh,

- select the new fields with alias, i.e.

<fisrttable>_{begdat <second>}begdat as begdat_2 ...

define the field names accordingly

- do not use <table1> as <alias> but write as above (less confusing and no need for!)

- use into corresponding fields of because this is resolved at compile time an will not cost you any performance. It is an urban legend that into corresponding fields of will influence the performance. Nobody ever made a significant measurement proofing it.

Regards,

Clemens

[Former Member]

Thanks everyone for the time. Vishnu's solution solved my problem. I awarded point to everyone.

Mithun.