topic Re: INNER JOIN problem in Funtion Module in Application Development and Automation Discussions

INNER JOIN problem in Funtion Module

Former Member — Thu, 02 Jun 2005 13:25:17 GMT

Hi guys,

I am implementing a Function Module for creating a Generic DataSource. I am trying to extract data from 2 tables. One is a Transaparent table and the other is a pooled table. The problem is, i cannot perform a JOIN on Trasnparent and pooled tables. Can someone tell me any alternative way of extracting data by JOINING both these tables within the FM.

Thanks a lot.

Re: INNER JOIN problem in Funtion Module

Former Member — Thu, 02 Jun 2005 13:43:17 GMT

Hi,

As far as I know you can not use pooled tables or clusters in joins. For the tables the fields you see in the dictionary do not exist in the database.

Try to fetch the data from table 1 into an internal table and than make a selection on the second table.

Regards,

Arjan Aalbers

Re: INNER JOIN problem in Funtion Module

Former Member — Thu, 02 Jun 2005 14:25:37 GMT

Hi,

Arjan is right , use an internal table to fetch records from the first DB table then use for all entries on thisn internal table and the 2nd DB table to fetch records into the final internal table where you intended to

Regards

Seshi

Re: INNER JOIN problem in Funtion Module

Former Member — Thu, 02 Jun 2005 15:22:05 GMT

Thanks Arjan and Seshi,

I will try and let you know.

Re: INNER JOIN problem in Funtion Module

Former Member — Thu, 02 Jun 2005 20:27:24 GMT

Hi Arjan and Seshi,

I tried doing the same way. My code is:

SELECT * FROM trans_tab APPENDING CORRESPONDING FIELDS OF TABLE itab_trans.

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT * FROM pooled_tab APPENDING CORRESPONDING FIELDS OF TABLE itab_pooled FOR ALL ENTRIES IN itab_trans

WHERE field1 EQ itab_trans-field1.

The error is:

The INTO clause must be specified with "FETCH NEXT CURSOR". It is not allowed with "OPEN CURSOR".

I need the cursor because i am implementing it in the Function Module. I don't understand the error. Could you tell me the reason.

Thanks.

Re: INNER JOIN problem in Funtion Module

jayanthi_jayaraman — Fri, 03 Jun 2005 04:14:36 GMT

Hi Govind,

Try this.

select * from trans_tab into corresponding fields of table itab_trans.

if sy-subrc ne 0.

select * from pooled_tab into table itab_pooled for all entries in itab_trans where field1 eq itab_trans-field1.

endif.

PS:you need to append the records only if the internal table is having exising records already.

Hope this helps.

Re: INNER JOIN problem in Funtion Module

Former Member — Mon, 06 Jun 2005 14:30:26 GMT

Hi Jayanthi,

I tried the way you said.

SELECT Field1 FROM trans_table INTO CORRESPONDING FIELDS OF TABLE itab_trans WHERE Field1 IN S_Field1.

OPEN CURSOR WITH HOLD S_CURSOR FOR

SELECT Field2 FROM pooled_table INTO CORRESPONDING FIELDS OF TABLE itab_pool FOR ALL ENTRIES IN itab_trans

WHERE Field2 EQ itab_trans-Field1 AND

Field2 IN S_Field2.

But, i still get the same error.

The INTO clause must be specified with "FETCH NEXT CURSOR". It is not allowed with "OPEN CURSOR".

Any suggestions will be appreciated.

Thanks.

Re: INNER JOIN problem in Funtion Module

ChristianFi — Mon, 06 Jun 2005 18:53:38 GMT

Acutally the above mentioned example did not say anything from open cursor. Is it a requirement to use open cursor in this case?

If so....

I am not sure if it can be used with FOR ALL ENTRIES

but in this case.

But the syntax (from memory)

open cursor l_cursor for select * from ...for all entries......

and

do.

fetch next cursor l_cursor into corresponding fields of table.

if not sy-subrc is initial.

close cursor l_cursor.

exit.

enddo.

.....

Christian

Re: INNER JOIN problem in Funtion Module

jayanthi_jayaraman — Tue, 07 Jun 2005 04:21:30 GMT

Hi Govind,

I am not sure why you are using OPEN cursor.

Just try like this without that open cursor statement.

SELECT Field1 FROM trans_table INTO CORRESPONDING FIELDS OF TABLE itab_trans WHERE Field1 IN S_Field1.

SELECT Field2 FROM pooled_table INTO CORRESPONDING FIELDS OF TABLE itab_pool FOR ALL ENTRIES IN itab_trans

WHERE Field2 EQ itab_trans-Field1 AND

Field2 IN S_Field2.