topic Re: inner join condition help in Application Development and Automation Discussions

inner join condition help

Former Member — Fri, 05 Dec 2008 02:44:40 GMT

i want to join two table where project number is equal to project no with concantate number 999.

for eg.

select * from proj as a

inner join Prsp as b

on b_{posid = a}posid+'999'

into table ittab where invl=inventoryno

any suggestion.

Re: inner join condition help

Former Member — Fri, 05 Dec 2008 02:50:36 GMT

First select data from PROJ and collect in internal table(Itab) than do concatenate ITAB-POSID with 999 and store in internal table in different field(temp) than use 'for all entries' with itab-tamp while selecting data from PRSP with condition posid = itab-temp.

Re: inner join condition help

Former Member — Fri, 05 Dec 2008 03:44:58 GMT

Well, I'm not sure, Would you please provide an example.

two table only matching project from one table in another table with value concantate 999.

Re: inner join condition help

Former Member — Fri, 05 Dec 2008 03:50:09 GMT

hi,

You can first select the data from proj into 1 internal table and then concatenate proj number with 999 into a variable and then select data from prps by using for all entries for first itab.

thanks

Re: inner join condition help

Former Member — Fri, 05 Dec 2008 05:14:21 GMT

Try using

Select * from PROJ into table it_tab1.

loop at it_tab1 into wa_tab1.

CONCATENATE wa_tab1-posid '999' into wa_tab1-posid.

modify it_tab1 from wa_tab1.

endloop.

select * from PRPS into table it_tab2

for all entries in it_tab1

where posid = it_tab1-posid.

Regards,

Harshada

Re: inner join condition help

Former Member — Fri, 05 Dec 2008 05:43:09 GMT


> i want to join two table where project number is equal to project no with concantate number 999.
> 
> for eg.
> 
> select * from proj as a
>      inner join Prsp as b
>     on b_{posid = a}posid+'999' <===
> into table ittab where invl=inventoryno
> 
> any suggestion.

Hi Prabha,

Let me tell you one thing...

PROJ table is not having the field POSID itself...

> select * from proj as a

> inner join Prsp as b

> on b_{posid = a}posid+'999' <=== Here what I can see is There is No POSID field in table PROJ

> into table ittab where invl=inventoryno

If you want to Join PROJ and PRPS u can link it through fields PROJ-PSPNR and PRPS-PSPHI

Please have a look at the tables and fileds and the records(DATA) once more...

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

Re: inner join condition help

Former Member — Fri, 05 Dec 2008 05:50:24 GMT

see this folloiwng example herer two table KNA1 AND KNVP AND FETCH ALL RECORD WHERE BOTH KUNNR IS SAME

select single name1 a~kunn2

into (itab-agent , itab-pcode)

from knvp as a

inner join kna1 as b

on a_{kunn2 = b}kunnr

where a~kunnr = itab-kunag and

a~vkorg = itab-vkorg and

a~vtweg = itab-vtweg and

a~spart = itab-spart and

a~parvw = 'PC'.