How to use INNER JOIN in such case

[Former Member]

when i program as below[CASE1]. the code is able to active.

CASE1:

==========================

DATA: WK_BUKRS LIKE T001-BUKRS,

WK_BUTXT LIKE T001-BUTXT,

WK_TABLE(4) TYPE C VALUE 'T001'.

START-OF-SELECTION.

WK_BUKRS = 'DECN'.

PERFORM GET_BUTXT USING WK_BUKRS WK_TABLE

CHANGING WK_BUTXT.

WRITE: WK_BUTXT.

FORM GET_BUTXT USING I_BUKRS LIKE T001-BUKRS

I_TABLE

CHANGING O_BUTXT LIKE T001-BUTXT.

SELECT SINGLE BUTXT

INTO O_BUTXT

FROM (I_TABLE)

WHERE BUKRS = I_BUKRS.

ENDFORM.

===========================

but when I need to INNER JOIN another table [CASE2]

CASE2:

=======================

DATA: WK_BUKRS LIKE T001-BUKRS,

WK_BUTXT LIKE T001-BUTXT,

WK_TABLE(4) TYPE C VALUE 'T001'.

START-OF-SELECTION.

WK_BUKRS = 'DECN'.

PERFORM GET_BUTXT USING WK_BUKRS WK_TABLE

CHANGING WK_BUTXT.

WRITE: WK_BUTXT.

FORM GET_BUTXT USING I_BUKRS LIKE T001-BUKRS

I_TABLE

CHANGING O_BUTXT LIKE T001-BUTXT.

SELECT SINGLE BUTXT

INTO O_BUTXT

FROM (I_TABLE) AS G INNER JOIN BKPF AS H

ON G_{BUKRS = H}BUKRS

WHERE G~BUKRS = I_BUKRS.

ENDFORM.

=================================

Syntax error:

Wrong expression "INNER" in FROM clause. WHERE condition.

Can anybody help me to solve the problem.

My requirement is to use INNER JOIN with variable table

[anversha_s]

hi slam,

chk this sample code.

hi,

table emp

empno name

a sasi

b xxx

c yyy

table sal

empno salary

a 1000

b 2000

Inner join

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

select e_{empno e}name

s~sal

into table int_table

from emp as e

inner join sal as s

on

e_{empno = s}empno.

if you made inner join between table a and b by emp no

the selection retrives only if the condition satisfy the output will be

a sasi 1000

b xxx 2000

rgds

anver

if hlped mark points

Message was edited by: Anversha s

[Former Member]

Hi,

INNER JOIN can not be possible with an internal table, SELECT statement is used for database table. kindly use a database table instead internal table

regards,

Rahul

[christian_wohlfahrt]

Hi Slam!

You have to code the statement more dynamical, have a look at the example:

PARAMETERS: p_cityfr TYPE spfli-cityfrom, 
            p_cityto TYPE spfli-cityto. 

DATA: BEGIN OF wa, 
         fldate   TYPE sflight-fldate, 
         carrname TYPE scarr-carrname, 
         connid   TYPE spfli-connid, 
       END OF wa. 

DATA itab LIKE SORTED TABLE OF wa 
               WITH UNIQUE KEY fldate carrname connid. 
DATA: column_syntax TYPE string, 
      dbtab_syntax TYPE string. 

column_syntax = `c~carrname p~connid f~fldate`. 

dbtab_syntax = `( ( scarr AS c ` 
  & ` INNER JOIN spfli AS p ON p~carrid  = c~carrid` 
  & ` AND p~cityfrom = p_cityfr` 
  & ` AND p~cityto   = p_cityto )` 
  & ` INNER JOIN sflight AS f ON f~carrid = p~carrid ` 
  & ` AND f~connid = p~connid )`. 

SELECT (column_syntax) 
       FROM (dbtab_syntax) 
       INTO CORRESPONDING FIELDS OF TABLE itab. 

LOOP AT itab INTO wa. 
  WRITE: / wa-fldate, wa-carrname, wa-connid. 
ENDLOOP. 

Regards,

Christian

[Former Member]

Hi Christian Wohlfahrt

I'm sorry to say your code catchs a syntax error with

"DBTAB_SYNTAX" can't be a table , a reference, a string, or contain any of these objects.

[christian_wohlfahrt]

Hi,

then it's time for an upgrade

This should work on releases 6.10 and higher, in my ECC6.0 it's no problem to generate the example.

So if you have an older release, then the answer to your problem is unfortunately: not possible.

Use instead 'for all entries', then loop over one of the result tables, check if an entry is also in the 2nd result and take this as 'final' result.

Regards,

Christian

[Former Member]

HI,Christian Wohlfahrt

thank u for your advice...I'm 4.7Ep ,unable to generate this case unfortunately.

anyway,tks very much!

[anversha_s]

hi,

here I_TABLE is getting the value of WK_TABLE.

so how can we write the inner join without a data base table.

so passs the correct data base table to I_TABLE.

rgds

anver

if hlped mark points

[Former Member]

My requrement is how to pass the table name into the selection. Before selection some conditions decide which table to be selected. But the selection sentence are the same excepts the table. So my idea is to write a Form to pass the table name into the selection sentence.

[Former Member]

I think the error is because you cannot specify inner join with dynamic tables. Since the system would only be able to get the table name at runtime and hence the issue.

Though I not sure of this 100% but I feel very strongly that it is the reason.

[Former Member]

Dear Christian Wohlfahrt

I think you suggestion will be able to solve my problem.

Let me try first.

thanks all

[Former Member]

Hi,

YOU SHOULD USE ONLY DATABASE TABLE WHEN U USING JOINS.

IE FROM DBTABLENAME

INTO INTERNAL TABLE.

BUT U R USING FROM INTERNAL TABLE .

PRAMEELA

[Former Member]

Hi all,

I have to write if....else... to select the table A and B

although these two tables are the same structure.

thanks a lot whatever!