topic Re: Query regarding inner join in Application Development and Automation Discussions

Query regarding inner join

Former Member — Wed, 20 Dec 2006 06:31:27 GMT

Can anyone explain me the error in this select query and give me the correct answer.

select 1_{begda 1}endda 2_{kostl 2}persg 2_{anvsh 3}pernr into

corresponding fields of table i_emp from

( ( pa0000 as 1 innerjoin pa0001 as 2 on 1_{pernr = 2}pernr )

join pa0003 as 3 on 3_{pernr = pa0002}pernr )

for all entries in pa0002

where 2~pernr = pa0002-pernr.

Requirement is :

retreive data from 4 tables :PA0000,PA0001,PA0002,PA0003 based on pernr field.

From PA0000 we need begda and endda.

From PA0001 we need kostl and persg and anvsh.

From PA0002 we need to concatenate VORNA and NACHN fieldsand put this in display_name field of i_emp.How to do this in innerjoin.

From PA0003 we need pernr field.

Structure of t_emp is:

types:begin of t_emp,

start_date like pa0000-begda,

end_date like pa0000-endda,

cost_centre like pa0001-kostl,

active_status like pa0001-persg,

display_name(80) type c,

perno like pa0003-pernr,

activity_type like zpm_activity_typ-lstar,

contract_id like pa0001-anvsh,

end of t_emp.

data:i_emp type standard table of t_emp.

Please tell me the exact select query.The perfect one.

Points will definitely be given.This is really urgent.

Please help me.

Thanking you

chandrika.

Re: Query regarding inner join

Former Member — Wed, 20 Dec 2006 06:35:30 GMT

Change your select query to

select 1_{begda 1}endda 2_{kostl 2}persg 2_{anvsh 3}pernr into

corresponding fields of table i_emp from

( ( pa0000 as 1 innerjoin pa0001 as 2 on 1_{pernr = 2}pernr )

<b>join pa0003 as 3 on 3_{pernr = 2}pernr )</b>

for all entries in pa0002

where 2~pernr = pa0002-pernr.

Hope this helps.

Regards,

Manoj

Re: Query regarding inner join

Former Member — Wed, 20 Dec 2006 06:40:11 GMT


select 1~begda 
1~endda 
2~kostl 
2~persg 
2~anvsh 
3~pernr 
into corresponding fields of table i_emp from
pa0000 as 1 innerjoin pa0001 as 2 on 1~pernr = 2~pernr
innerjoin pa0003 as 3 on 3~pernr = pa0002~pernr


select pernr VORNA NACHN from pa0002 
into table i_displayname
for all entries in i_emp 
where pernr = i_emp-pernr.

loop at i_emp assigning <fieldsymbol>.
read table i_displayname with key pernr = <fieldsymbol>-pernr.
if sy-subrc eq 0.
concatenate <fieldsymbol>-vorna <fieldsymbol>-nachn into i_emp-display_name separated by space.
endif.
endloop.

hope this works

thx

pavan

Re: Query regarding inner join

jayanthi_jayaraman — Wed, 20 Dec 2006 06:41:44 GMT

Hi,

Instead of using inner join,use logical database PNP in the attributes of the report.

infotypes : p0000, p0001, p0002, p0003.

start-of-selection.

get pernr.

rp_provide_from_last p0000 space pn-begda pn-endda.

if pnp-sw-found = 1.

w_emp-begda = p0000-begda.

w_emp-enda = p0000-endda.

endif.

rp_provide_from_last p0001 space pn-begda pn-endda.

if pnp-sw-found = 1.

w_emp-kostl = p0001-kostl.

....

endif.

...

append w_emp to i_emp.

end-of-selection.

Re: Query regarding inner join

Former Member — Wed, 20 Dec 2006 06:48:51 GMT

Maybe you should try something like below:

select a~begda a~endda b~kostl b~persg "b~anvsh c~pernr
       into corresponding fields of table i_emp
       from pa0000 as a
       inner join pa0001 as b
       on a~pernr = b~pernr
       inner join pa0003 as c
       on a~pernr = c~pernr
       for all entries in pa0002
       where b~pernr = pa0002-pernr.

Please make sure that you are declaring PA0002 as internal table and you are checking the internal table is not initial.

Kind Regards

Eswar

Re: Query regarding inner join

Former Member — Wed, 20 Dec 2006 06:59:03 GMT

Make use of Logical databse PNP (mention in attributes of prgram) and copy this code below ,it will work and dont forget to award me points -

&----

*& Report ZGILL_TEST11 *

*& *

&----

*& *

&----

REPORT ZGILL_TEST11 message-id rp

line-size 250

line-count 65 .

tables: pernr.

infotypes: 0000,

0001,

0002,

0003.

constants: c_1(1) type c value '1'.

Internal Table for Output

data: begin of t_output occurs 0 ,

pernr like pernr-pernr ,

name(80),

begda like p0000-begda ,

endda like p0000-endda ,

kostl like p0001-kostl,

persg like p0001-persg,

ansvh like p0001-ansvh.

data: end of t_output .

Start-of-selection.

get pernr.

clear t_output.

Read Infotype 0

rp-provide-from-last p0000 space pn-begda pn-endda.

check pnp-sw-found eq c_1.

Read Infotype 1

rp-provide-from-last p0001 space pn-begda pn-endda.

check pnp-sw-found eq c_1.

Read Infotype 2

rp-provide-from-last p0002 space pn-begda pn-endda.

check pnp-sw-found eq c_1.

rp-provide-from-last p0003 space pn-begda pn-endda.

check pnp-sw-found eq c_1.

Gather all the required information related to the emp

move: pernr-pernr to t_output-pernr,

p0000-begda to t_output-begda,

p0000-endda to t_output-endda,

p0001-kostl to t_output-kostl,

p0001-persg to t_output-persg,

p0001-ansvh to t_output-ansvh.

concatenate p0002-vorna p0002-nachn into t_output-name separated by space.

append t_output.

end-of-selection.

perform print_report.

Re: Query regarding inner join

Former Member — Wed, 20 Dec 2006 08:24:43 GMT

it gives an error "(" has no closing ")" for the query which u gave

Re: Query regarding inner join

Former Member — Mon, 05 Sep 2011 05:18:02 GMT

solved

Moderator message : Warning !! Do not put irrelevant comments while closing old threads.

Edited by: Vinod Kumar on Sep 5, 2011 11:04 AM