topic Re: SELECT statement causing error in Application Development and Automation Discussions

SELECT statement causing error

Former Member — Fri, 23 Dec 2005 02:25:41 GMT

Hi all, a particular errors keeps on popping up when i try to activate my program. Error message is as follows:


The field "C~NAME1" from the SELECT list is missing in the GROUP BY clause. addition INTO wa or into (g1,...,gn) is required. fields of type "" or "A~MANDT".

Any ideas on what this syntax error message means?

Look at the SELECT statement that is causing the error:


  SELECT A~MATNR A~WERKS MAX( A~EBELN ) MAX( A~EBELP ) A~BUKRS A~MENGE A~NETPR A~PEINH B~LIFNR B~BEDAT C~NAME1
    INTO (WTAB-MATNR, WTAB-WERKS, WTAB-EBELN, WTAB-EBELP, WTAB-BUKRS, WTAB-MENGE, WTAB-NETPR, WTAB-PEINH, WTAB-LIFNR,WTAB-BEDAT, WTAB-NAME1)
    FROM EKPO AS A
      INNER JOIN EKKO AS B
        ON A~EBELN = B~EBELN AND
           A~BUKRS = B~BUKRS
      INNER JOIN LFA1 AS C
        ON B~LIFNR = C~LIFNR
*    FOR ALL ENTRIES IN ITAB
    WHERE A~WERKS = I_WERKS AND
          A~BUKRS = I_BUKRS AND
          B~BSTYP = 'F' AND
          B~SPRAS = 'EN'.

Re: SELECT statement causing error

RichHeilman — Fri, 23 Dec 2005 02:45:01 GMT

I'm not sure what results you will get, but it passes the syntax check.



REPORT ZRICH_0003 .

tables: t001, t001w.

select-options: i_werks for t001w-werks,
                i_bukrs for t001-bukrs.

data: begin of wtab,
      matnr type mara-matnr,
      werks type marc-werks,
      ebeln type ekko-ebeln,
      ebelp type ekpo-ebelp,
      bukrs type t001-bukrs,
      menge type ekpo-menge,
      netpr type ekpo-netpr,
      peinh type ekpo-peinh,
      lifnr type lfa1-lifnr,
      bedat type ekko-bedat,
      name1 type lfa1-name1,
      end of wtab.


  SELECT<b> Single</b> A~MATNR A~WERKS max( A~EBELN ) max( A~EBELP ) A~BUKRS
        A~MENGE A~NETPR A~PEINH B~LIFNR B~bedat C~NAME1
    INTO (WTAB-MATNR, WTAB-WERKS, WTAB-EBELN, WTAB-EBELP, WTAB-BUKRS,
WTAB-MENGE, WTAB-NETPR, WTAB-PEINH, WTAB-LIFNR, WTAB-bedat, WTAB-NAME1)
    FROM EKPO AS A
      INNER JOIN EKKO AS B
        ON A~EBELN = B~EBELN AND
           A~BUKRS = B~BUKRS
      INNER JOIN LFA1 AS C
        ON B~LIFNR = C~LIFNR
*    FOR ALL ENTRIES IN ITAB
    WHERE A~WERKS = I_WERKS AND
          A~BUKRS = I_BUKRS AND
          B~BSTYP = 'F' AND
          B~SPRAS = 'EN'
          <b> group by A~MATNR A~WERKS A~EBELN  A~EBELP  A~BUKRS
                    A~MENGE A~NETPR A~PEINH B~LIFNR B~bedat C~NAME1.</b>

Regard,

Rich Heilman

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 02:49:39 GMT

Dear Benard,

The problem is that you have used Aggregate Functions of OPEN-Sql (MAX) with Single Line Selection.

You avoid it or try adding GROUP-BY clause.

Regards,

Deva.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 03:27:59 GMT

Thanks Rich, i tried out the codes you posted...it passed the syntax check and managed to activate it. However, no records seems to be queried...even when i leave all the input fields blank before i executed...Maybe it's something wrong with my first SELECT statement? The earlier one i just posted is the second SELECT statement...Have a look:


  SELECT A~MATNR A~MATKL A~MTART A~EXTWG C~WERKS
    INTO CORRESPONDING FIELDS OF ITAB
    FROM MARA AS A
      INNER JOIN MAKT AS B
        ON A~MATNR = B~MATNR
      INNER JOIN MARD AS C
        ON A~MATNR = C~MATNR
    WHERE A~MATNR IN I_MATNR AND
          A~MATKL IN I_MATKL AND
          A~MTART IN I_MTART AND
          A~EXTWG IN I_EXTWG AND
          C~WERKS = I_WERKS.
  SORT ITAB BY MATNR.

It should have worked, right?

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 03:32:08 GMT

Just in case, I_BUKRS and I_WERKS are PARAMETERS and the rest are SELECT-OPTIONS:


SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE TEXT-001.
PARAMETERS: I_BUKRS LIKE EKKO-BUKRS,
            I_WERKS LIKE MARD-WERKS.
SELECT-OPTIONS: I_MATNR FOR MARA-MATNR,
                I_MTART FOR MARA-MTART,
                I_MATKL FOR MARA-MATKL,
                I_EXTWG FOR MARA-EXTWG.
SELECTION-SCREEN END OF BLOCK 1.

Re: SELECT statement causing error

RichHeilman — Fri, 23 Dec 2005 03:32:09 GMT

I have to assume that ITAB is an internal table, right? But you are not fetching records into it, You are just fetching 1 record into the header line. You must add the word TABLE before ITAB.




  SELECT A~MATNR A~MATKL A~MTART A~EXTWG C~WERKS
    INTO CORRESPONDING FIELDS OF <b>TABLE</b> ITAB
    FROM MARA AS A
      INNER JOIN MAKT AS B
        ON A~MATNR = B~MATNR
      INNER JOIN MARD AS C
        ON A~MATNR = C~MATNR
    WHERE A~MATNR IN I_MATNR AND
          A~MATKL IN I_MATKL AND
          A~MTART IN I_MTART AND
          A~EXTWG IN I_EXTWG AND
          C~WERKS = I_WERKS.
  SORT ITAB BY MATNR.

Regards,

Rich Heilman

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 04:31:01 GMT

Rich, i need your advice on this. During the write statement, only the values retrieved during the first SELECT are part of the output. The values that are supposed to be retrieved during the second SELECT (INTO WTAB) and later MOVE into ITAB do not appear. Any ideas on this?


  LOOP AT WTAB.
    MOVE: WTAB-EBELN TO ITAB-EBELN,
          WTAB-EBELP TO ITAB-EBELP,
          WTAB-BUKRS TO ITAB-BUKRS,
          WTAB-MENGE TO ITAB-MENGE,
          WTAB-NETPR TO ITAB-NETPR,
          WTAB-PEINH TO ITAB-PEINH,
          WTAB-LIFNR TO ITAB-LIFNR,
          WTAB-BEDAT TO ITAB-BEDAT,
          WTAB-NAME1 TO ITAB-NAME1.
    MODIFY ITAB TRANSPORTING EBELN EBELP BUKRS MENGE NETPR PEINH LIFNR
BEDAT NAME1
      WHERE MATNR = WTAB-MATNR AND
            WERKS = WTAB-WERKS.
  ENDLOOP.

Re: SELECT statement causing error

jayanthi_jayaraman — Fri, 23 Dec 2005 04:45:00 GMT

Hi,

If you have used the following select st.,then you will get only one record during second select st.So if you are looping wtab,it will get get executed only once.

SELECT Single A_{MATNR A}WERKS max( A_{EBELN ) max( A}EBELP ) A~BUKRS

A_{MENGE A}NETPR A_{PEINH B}LIFNR B_{bedat C}NAME1

INTO (WTAB-MATNR, WTAB-WERKS, WTAB-EBELN, WTAB-EBELP, WTAB-BUKRS,

WTAB-MENGE, WTAB-NETPR, WTAB-PEINH, WTAB-LIFNR, WTAB-bedat, WTAB-NAME1)

FROM EKPO AS A

INNER JOIN EKKO AS B

ON A_{EBELN = B}EBELN AND

A_{BUKRS = B}BUKRS

INNER JOIN LFA1 AS C

ON B_{LIFNR = C}LIFNR

FOR ALL ENTRIES IN ITAB

WHERE A~WERKS = I_WERKS AND

A~BUKRS = I_BUKRS AND

B~BSTYP = 'F' AND

B~SPRAS = 'EN'

group by A_{MATNR A}WERKS A_{EBELN A}EBELP A~BUKRS

A_{MENGE A}NETPR A_{PEINH B}LIFNR B_{bedat C}NAME1.

So if you want to get all the records,use the table with select st.

SELECT A_{MATNR A}WERKS max( A_{EBELN ) max( A}EBELP ) A~BUKRS

A_{MENGE A}NETPR A_{PEINH B}LIFNR B_{bedat C}NAME1

INTO corresponding fields of table wtab

FROM EKPO AS A

INNER JOIN EKKO AS B

ON A_{EBELN = B}EBELN AND

A_{BUKRS = B}BUKRS

INNER JOIN LFA1 AS C

ON B_{LIFNR = C}LIFNR

WHERE A~WERKS = I_WERKS AND

A~BUKRS = I_BUKRS AND

B~BSTYP = 'F' AND

B~SPRAS = 'EN'

group by A_{MATNR A}WERKS A_{EBELN A}EBELP A~BUKRS

A_{MENGE A}NETPR A_{PEINH B}LIFNR B_{bedat C}NAME1.

Kindly reward points if it helps.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 04:53:48 GMT

it's not working...i'll try using Debugger Mode

Re: SELECT statement causing error

jayanthi_jayaraman — Fri, 23 Dec 2005 05:31:00 GMT

Hi,

data: begin of wtab occurs 0,

matnr type mara-matnr,

werks type marc-werks,

ebeln type ekko-ebeln,

ebelp type ekpo-ebelp,

bukrs type t001-bukrs,

menge type ekpo-menge,

netpr type ekpo-netpr,

peinh type ekpo-peinh,

lifnr type lfa1-lifnr,

bedat type ekko-bedat,

name1 type lfa1-name1,

end of wtab.

SELECT A_{MATNR A}WERKS max( A_{EBELN ) max( A}EBELP ) A~BUKRS

A_{MENGE A}NETPR A_{PEINH B}LIFNR B_{bedat C}NAME1

INTO corresponding fields of table wtab

FROM EKPO AS A

INNER JOIN EKKO AS B

ON A_{EBELN = B}EBELN AND

A_{BUKRS = B}BUKRS

INNER JOIN LFA1 AS C

ON B_{LIFNR = C}LIFNR

WHERE A~WERKS = I_WERKS AND

A~BUKRS = I_BUKRS AND

B~BSTYP = 'F' AND

B~SPRAS = 'EN'

group by A_{MATNR A}WERKS A~BUKRS

A_{MENGE A}NETPR A_{PEINH B}LIFNR B_{bedat C}NAME1.

If you are not getting values in wtab after this select statement,then try to use for all entries instead of inner join and do the calculations using loop.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 07:39:32 GMT

i managed to solve it using another way...the thing is now that when i key in values in the search criteria for I_BUKRS, I_WERKS, I_MATKL, I_MTART or I_EXTWG, no records are returned. If i key in a value in I_WERKS, all records of that Plant are returned (so i know there is nothing wrong with it) and if i also key in a value in I_MATNR, the exact material (of that material number) from that plant will appear?

Should it be when i key in a value in only 1 selection criteria, all records that fulfill that criteria appear?

Let's say i key in 'ELE' value in Material Group (I_MATKL), it should retrieve and output all material of that Material Group, but it doesn't...something wrong with my SELECT statements again?

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 07:45:14 GMT

Hi Bernard,

I may be repeating , but make sure :

1. For parameters : the query should be =

For select-options : the query should be IN

2. Can u paste the code for selection screen

and ur sql also.

Regards,

Amit M.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 08:08:44 GMT

sure, here's the update codes:


*&---------------------------------------------------------------------*
*&    SELECTION-SCREEN                                                 *
*&---------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE TEXT-001.
PARAMETERS: I_BUKRS LIKE EKKO-BUKRS,
            I_WERKS LIKE MARD-WERKS.
SELECT-OPTIONS: I_MATNR FOR MARA-MATNR,
                I_MTART FOR MARA-MTART,
                I_MATKL FOR MARA-MATKL,
                I_EXTWG FOR MARA-EXTWG.
SELECTION-SCREEN END OF BLOCK 1.

*&---------------------------------------------------------------------*
*&    FORM SELECT_DATA                                                 *
*&---------------------------------------------------------------------*
FORM SELECT_DATA.

  SELECT A~MATNR A~MATKL A~MTART A~EXTWG C~WERKS
    INTO TABLE ITAB
    FROM MARA AS A
      INNER JOIN MAKT AS B
        ON A~MATNR = B~MATNR AND
           B~SPRAS = 'EN'
      INNER JOIN MARD AS C
        ON A~MATNR = C~MATNR
    WHERE A~MATNR IN I_MATNR AND
          A~MATKL IN I_MATKL AND
          A~MTART IN I_MTART AND
          A~EXTWG IN I_EXTWG AND
          C~WERKS = I_WERKS.
  SORT ITAB BY MATNR.

*  CHECK ITAB-MATNR IN I_MATNR.
*  CHECK ITAB-MATKL IN I_MATKL.
*  CHECK ITAB-MTART IN I_MTART.
*  CHECK ITAB-EXTWG IN I_EXTWG.
*  CHECK ITAB-WERKS = I_WERKS.

  CHECK NOT ITAB[] IS INITIAL.

  SELECT A~MATNR A~WERKS MAX( A~EBELN ) MAX( A~EBELP ) A~BUKRS
A~MENGE A~NETPR A~PEINH B~LIFNR B~BEDAT C~NAME1
  INTO CORRESPONDING FIELDS OF TABLE WTAB
*    INTO (WTAB-MATNR, WTAB-WERKS, WTAB-EBELN, WTAB-EBELP, WTAB-BUKRS,
*WTAB-MENGE, WTAB-NETPR, WTAB-PEINH, WTAB-LIFNR,WTAB-BEDAT, WTAB-NAME1)
    FROM EKPO AS A
      INNER JOIN EKKO AS B
        ON A~EBELN = B~EBELN AND
           A~BUKRS = B~BUKRS AND
           B~BSTYP = 'F'
      INNER JOIN LFA1 AS C
        ON B~LIFNR = C~LIFNR
*    FOR ALL ENTRIES IN ITAB
    WHERE A~MATNR IN I_MATNR AND
          A~MATKL IN I_MATKL AND
          A~WERKS = I_WERKS AND
          A~BUKRS = I_BUKRS AND
          A~MTART IN I_MTART AND
          A~LOEKZ = ' '
    GROUP BY A~MATNR A~WERKS A~EBELN A~EBELP A~BUKRS A~MENGE A~NETPR
A~PEINH B~LIFNR B~BEDAT C~NAME1.

*CHECK ITAB-BUKRS = I_BUKRS.
*CHECK EKKO-BSTYP = 'F'.
*CHECK EKKO-SPRAS = 'EN'.

  LOOP AT WTAB.
    MOVE: WTAB-EBELN TO ITAB-EBELN,
          WTAB-EBELP TO ITAB-EBELP,
          WTAB-BUKRS TO ITAB-BUKRS,
          WTAB-MENGE TO ITAB-MENGE,
          WTAB-NETPR TO ITAB-NETPR,
          WTAB-PEINH TO ITAB-PEINH,
          WTAB-LIFNR TO ITAB-LIFNR,
          WTAB-BEDAT TO ITAB-BEDAT,
          WTAB-NAME1 TO ITAB-NAME1.
    MODIFY ITAB TRANSPORTING EBELN EBELP BUKRS MENGE NETPR PEINH LIFNR
BEDAT NAME1
      WHERE MATNR = WTAB-MATNR.
*      WERKS = WTAB-WERKS.
  ENDLOOP.

  LINE_CHECK = 0.

ENDFORM.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 08:19:49 GMT

Hi Bernard,

1. The parameters I_BUKRS and I_WERKS

should always have some value (OBLIGATORY)

2. Bcos we have used = in sql.

If they are blank, no values will be returned.

3. I tried ur code (till the first SELECT)

at my end, it works fantastic.

4. try this code (just copy paste)

and see the ITAB after select statement at break-point.

5. REPORT abc.

*&----

**& SELECTION-SCREEN

**&----

TABLES : mara.

DATA : itab LIKE TABLE OF mara WITH HEADER LINE.

SELECTION-SCREEN BEGIN OF BLOCK 1 WITH FRAME TITLE text-001.PARAMETERS:

i_bukrs LIKE ekko-bukrs obligatory,

i_werks LIKE mard-werks obligatory.

SELECT-OPTIONS: i_matnr FOR mara-matnr,

i_mtart FOR mara-mtart,

i_matkl FOR mara-matkl, i_extwg FOR mara-extwg.

SELECTION-SCREEN END OF BLOCK 1.

START-OF-SELECTION.

PERFORM select_data.

*&----

*& FORM SELECT_DATA *

&----

FORM select_data.

select *

into corresponding fields of table itab

from mara as a

inner join makt as b

on a_{matnr = b}matnr and

b~spras = 'EN'

inner join mard as c

on a_{matnr = c}matnr

where a~matnr in i_matnr and

a~matkl in i_matkl and

a~mtart in i_mtart and

a~extwg in i_extwg and

c~werks = i_werks.

break-point.

endform.

I hope it helps.

Regards,

Amit M.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 08:41:43 GMT

do i need to paste it over the 1st and 2nd select?

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 09:08:24 GMT

Hi again,

1. For trial purpose,

don't overwrite any of your code.

2. Just make another program Y/z.

and copy my code

to see that it works fantastic,

( It is your code only,

i have made only some variable declaration)

regards,

amit m.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 11:24:00 GMT

Hi again,

1. did ur problem get resolved ?

regards,

amit m.

Re: SELECT statement causing error

Former Member — Fri, 23 Dec 2005 11:24:19 GMT

Hi again,

1. did ur problem get resolved ?

regards,

amit m.

Re: SELECT statement causing error

Former Member — Tue, 27 Dec 2005 04:45:35 GMT

Sorry abt the late reply...Christmas and was busy with other functional stuff...

Well, i did try your solution and it works...but i'm manage to also find a solution to it on my own....

However, right now, i noticed another problem with the second SELECT statement for WTAB.


  SELECT A~MATNR A~MATKL A~WERKS MAX( A~EBELN ) MAX( A~EBELP ) A~BUKRS
A~MENGE A~NETPR A~PEINH A~MTART B~LIFNR B~BEDAT C~NAME1
  INTO CORRESPONDING FIELDS OF TABLE WTAB
*    INTO (WTAB-MATNR, WTAB-WERKS, WTAB-EBELN, WTAB-EBELP, WTAB-BUKRS,
*WTAB-MENGE, WTAB-NETPR, WTAB-PEINH, WTAB-LIFNR,WTAB-BEDAT, WTAB-NAME1)
    FROM EKPO AS A
      INNER JOIN EKKO AS B
        ON A~EBELN = B~EBELN AND
           A~BUKRS = B~BUKRS AND
           B~BSTYP = 'F'
      INNER JOIN LFA1 AS C
        ON B~LIFNR = C~LIFNR
    WHERE A~MATNR IN I_MATNR AND
          A~MATKL IN I_MATKL AND
          A~WERKS = I_WERKS AND
          A~BUKRS = I_BUKRS AND
          A~MTART IN I_MTART AND
          A~LOEKZ = ' '
    GROUP BY A~MATNR A~MATKL A~WERKS A~EBELN A~EBELP A~BUKRS A~MENGE
A~NETPR A~PEINH A~MTART B~LIFNR B~BEDAT C~NAME1.

For some reason, it is not extracting the values for EBELN and EBELP...when i went through debug mode, i noticed that in each record, EBELN would appear as a blank and EBELP would appear in zeros...

Any ideas?

Re: SELECT statement causing error

Former Member — Tue, 27 Dec 2005 04:52:57 GMT

What is your definition of WTAB? Looks like you are selecting MATNR, MATKL, WERKS.... from the tables, but your WTAB field for MATKL might be missing.

The fields you select should be there in your WTAB with the same name if you are using INTO CORRESPONDING FIELDS OF option.

If you are using INTO (fld1, fld2, fld3...) then the order of the fields you select should match the order of the fields you listed in the INTO list. Here the names need not match but they should have the same types.

Srinivas