topic Re: Syntax Error in SELECT statement in Application Development and Automation Discussions

Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:00:19 GMT

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,

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:02:00 GMT

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.

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:04:01 GMT

still the same error

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:04:12 GMT

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

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:06:22 GMT

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

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:12:54 GMT

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

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:13:09 GMT

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

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:13:31 GMT

Hi CD,

Where clause is mandatory when you are using Inner Join.

Re: Syntax Error in SELECT statement

RaymondGiuseppi — Thu, 11 Dec 2008 15:15:01 GMT

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

Re: Syntax Error in SELECT statement

ThomasZloch — Thu, 11 Dec 2008 15:16:51 GMT

> 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

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:17:27 GMT


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

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

Rob

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:18:41 GMT

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.

Re: Syntax Error in SELECT statement

ThomasZloch — Thu, 11 Dec 2008 15:25:12 GMT

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

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:25:39 GMT

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

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:33:39 GMT

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.

Re: Syntax Error in SELECT statement

Former Member — Thu, 11 Dec 2008 15:39:19 GMT


> 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