topic Re: Problem with SQL Statement in Application Development and Automation Discussions

Problem with SQL Statement

Former Member — Wed, 06 Jun 2007 09:24:49 GMT

I currently have the following code which returns no data:

TYPES: BEGIN OF i_test,
          vbeln LIKE vbak-vbeln,
       END OF i_test.

DATA: test TYPE STANDARD TABLE OF i_test
                WITH HEADER LINE
                WITH NON-UNIQUE KEY vbeln.

SELECT DISTINCT vbeln
  INTO CORRESPONDING FIELDS OF test
  FROM vbak
  WHERE bstnk LIKE '05.06.2007%'.
ENDSELECT.

But when I create an SQVI query with the same criteria I get 12 sale documents (I enter 05.06.2007* into the bstnk field).

Does anyone know what I am doing wrong?

Re: Problem with SQL Statement

Former Member — Wed, 06 Jun 2007 09:30:32 GMT

HI,

To read a several entries from the database, use the following:

SELECT [DISTINCT] <cols> ... WHERE ...

If you do not use DISTINCT (<lines> is then empty), the system reads all of the lines that satisfy the WHERE condition. If you use DISTINCT, the system excludes duplicate entries.

The result of the selection is a table. The target area of the INTO clause can be an internal table with a line type appropriate for <cols>. If the target area is not an internal table, but a flat structure, you must include an ENDSELECT statement after the SELECT statement:

SELECT DISTINCT (vbeln)

INTO CORRESPONDING FIELDS OF test

FROM vbak

WHERE bstnk LIKE '05.06.2007%'.

ENDSELECT.

Re: Problem with SQL Statement

Former Member — Wed, 06 Jun 2007 09:35:31 GMT

I am confused by what you have told me as I am already using SELECT DISTINCT...ENDSELECT.

Re: Problem with SQL Statement

Former Member — Wed, 06 Jun 2007 09:42:05 GMT

Hi,

Try

SELECT DISTINCT vbeln

INTO <i>TABLE</i> test

FROM vbak

WHERE bstnk LIKE '05.06.2007%'.

As written your code is filling the internal table header, but not moving the data to the table body.

Re: Problem with SQL Statement

Former Member — Wed, 06 Jun 2007 09:42:33 GMT

HI,

In the where condition you specied '05.06.2007%'. in the selects % means it will take all the charecters after the 05.06.2007.

lets say, if there is any entry with 05.06.2007A, then it will consider as a entry in the select statment,

and also use the () with the DESTICT statment

Regards

Sudheer