Syntax Error in SELECT statement

[Former Member]

Hi,

My ABAP statement is:

select b1~fa a1~fb 
INTO CORRESPONDING FIELDS OF TABLE it_1 
from (table_nm) as a1 inner join db_tab2 as b1 
on a1~fb = b1~fb.

For this statement it gives me following error in syntax check:

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

Thanks,

CD

[Former Member]

HI

Check this

select b1_{fa a1}fb

INTO CORRESPONDING FIELDS OF TABLE it_1

from (table_nm) as a1 inner join on db_tab2 as b1

Where a1_{fb = b1}fb.

[Former Member]

still the same error

[Former Member]

SELECT VBRK~VBELN AS VBELN

VBRK~VKORG AS VKORG

VBRK~VTWEG AS VTWEG

VBRP~PRODH AS PRODH

VBRK~FKDAT AS FKDAT

SUM( VBRP~FKLMG ) AS FKLMG

SUM( VBRP~NETWR ) AS NETWR

INTO CORRESPONDING FIELDS OF WA_TAB FROM VBRK AS VBRK JOIN VBRP AS VBRP

ON VBRK_{VBELN = VBRP}VBELN

WHERE VBRP~MATNR = WA_TAB-MATNR

AND VBRP~WERKS = WA_TAB-WERKS

AND VBRK~FKDAT BETWEEN S_DATE-LOW AND S_DATE-HIGH

AND VBRK~VKORG IN S_VKORG

AND VBRK~VTWEG IN S_VTWEG

AND VBRK~FKSTO NE 'X'

AND VBRK~FKART EQ 'ZBI1'

AND ( VBRK_{VBTYP EQ'M' OR VBRK}VBTYP EQ 'P' OR VBRK_{VBTYP EQ 'O' OR VBRK}VBTYP EQ '6'

OR VBRK~VBTYP EQ '5' )

GROUP BY VBRK_{VBELN VBRK}VKORG VBRK_{VTWEG VBRP}PRODH VBRK_{FKDAT VBRP}FKLMG.

WA_TAB-VAR0 = ( ( WA_TAB-VAR0 + WA_TAB-FKLMG ) ).

WA_TAB-VAR34 = ( WA_TAB-VAR34 + WA_TAB-NETWR ).

ENDSELECT.

this is how you join statements

[Former Member]

Is the use of where clause mandatory.

I am following the syntax exactly as here:

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

but still get the error

[Former Member]

yes its mandatory as you need to provide a contion for it

[Former Member]

Hi

I think the problem it can't do a INNER JOIN dynamic, u need to insert the name of all tables used in the select.

So u can write:

tables: vbak, vbap.

data: vbeln like vbak-vbeln, posnr like vbap-posnr.

select A~VBELN B~POSNR into (vbeln, posnr)
   from VBAK as A inner join VBAP as B
     on A~VBELN = B~VBELN
      where A~vbeln > '10000000000'.
endselect.

but u don't write:

tables: vbak, vbap.

data: vbeln like vbak-vbeln, posnr like vbap-posnr.

data: table(100).

select A~VBELN B~POSNR into (vbeln, posnr)
   from (TABLE) as A inner join VBAP as B
     on A~VBELN = B~VBELN
      where A~vbeln > '10000000000'.
endselect.

Max

[Former Member]

Hi CD,

Where clause is mandatory when you are using Inner Join.

[ThomasZloch]

> Where clause is mandatory when you are using Inner Join.

Not correct. All joined tables and on-conditions need to be included in the dynamic (dbtab_syntax) expression, see F1 help for SELECT - source.

Thomas

[Former Member]

>

> yes its mandatory as you need to provide a contion for it

Incorrect. It's good to have a where, but not "mandatory".

Rob

[Former Member]

thanks for the hint, the db name from variable was the culprit. I replaced it with a fixed DB name and it worked.

the problem here is that this table name will be different for me in all the 3 systems in the landscape (dev, qa and prod).

what approach should i take in this case.

[Former Member]

Hi

Try something like this:

DATA: BEGIN OF ITAB OCCURS 0,
        A,
        B,
      END   OF ITAB.


DATA: TABLENAME(30).

DATA: WA_FTAB(72) TYPE C,
      FTAB        LIKE TABLE OF WA_FTAB.


WA_FTAB = 'A'.   APPEND WA_FTAB TO FTAB.

SELECT (FTAB) FROM (TABLENAME) INTO TABLE ITAB.

LOOP AT ITAB.
  SELECT SINGLE A FRO <TABLE 2> 
    INTO ITAB-B
    WHERE A = ITAB-A.
  MODIFY ITAB.
ENDLOOP.

See this sample:

DATA: BEGIN OF ITAB OCCURS 0,
        VBELN LIKE VBAK-VBELN,
        POSNR LIKE VBAP-POSNR,
        VKORG LIKE VBAK-VKORG,
      END   OF ITAB.


DATA: TABLENAME(30) VALUE 'VBAP'.

DATA: WA_FTAB(72) TYPE C,
      FTAB        LIKE TABLE OF WA_FTAB.


WA_FTAB = 'VBELN'.   APPEND WA_FTAB TO FTAB.
WA_FTAB = 'POSNR'.   APPEND WA_FTAB TO FTAB.

SELECT (FTAB) UP TO 10 ROWS FROM (TABLENAME) INTO TABLE ITAB.

LOOP AT ITAB.
  SELECT SINGLE VKORG FROM VBAK INTO ITAB-VKORG
    WHERE VBELN = ITAB-VBELN.
  MODIFY ITAB.
ENDLOOP.

[Former Member]

>

> the problem here is that this table name will be different for me in all the 3 systems in the landscape (dev, qa and prod). 
> 
> what approach should i take in this case.

You could create views in each of the instances and determine the one to use dynamically.

Rob

[RaymondGiuseppi]

I don't think we are allowed to use a dynamic table name in the JOIN expression of the SELECT statement in old version.

Look under SE38 at the SELECT online help on YOUR system, in the JOIN table names must have the same form as in variant 1 (constants) or are themselves join expressions, not as variable which is variant 2.

Perform first a dynamic selection with the variable table name, and then perform a SELECT with the clause FOR ALL ENTRIES from the second table.

On which EXACT version do you run?

Regards

[Former Member]

raymond, i initially took the same approach as u suggested, but i am running into performance issues as both tables are very large...

[ThomasZloch]

Try:

concatenate table_nm ' as a1 inner join db_tab2 as b1 on a1~fb = b1~fb' into dbtab_syntax.
select b1~fa a1~fb 
from (dbtab_syntax)
INTO CORRESPONDING FIELDS OF TABLE it_1.

Thomas