2

I wanna get all elements of table EKKO by year , but I don't know how.

This is my query:

SELECT * FROM EKKO INTO TABLE @data(RESULT) WHERE BUKRS = @CO_Code AND WAERS = 'USD'.

I tried extract() and year(), like this :

SELECT * FROM EKKO INTO TABLE @data(RESULT) WHERE BUKRS = @CO_Code 
  AND WAERS = 'USD' AND YEAR (TO_DATE (AEDAT, 'YYYY-MM-DD') = 2017).

but it throws error

A Boolean expression is required in positions starting with YEAR.

What should I do next to complete?

Here are the contents of the table EKKO: enter image description here

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Hien Phan
  • 23
  • 1
  • 5
  • The functions `YEAR` and `TO_DATE` do not exist in ABAP SQL, they are not needed because the date type is normalized (`aedat LIKE '2017%'` as explained by Florian), eventually there are these few [date functions](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abensql_date_func.htm) since 7.51: DATS_IS_VALID, DATS_DAYS_BETWEEN, DATS_ADD_DAYS, DATS_ADD_MONTHS. – Sandra Rossi Jan 09 '20 at 11:07

3 Answers3

3

DATE functions are not supported in OpenSQL. I think they are either HANA(only) functions or implemented to AbapSQL (A later Hana-specific version of SQL) through later updates on S4/Hana systems. The one you tried to use with TO_DATE is definitely a HANA function.

SELECT * FROM EKKO  
  WHERE BUKRS = @CO_Code AND WAERS = 'USD' 
    AND substring( AEDAT, 1, 4 ) = 2017
  INTO TABLE @data(RESULT).

You can also use AEDAT like '2017%' or AEDAT like '2017____' (_ wildcard for 1 character). The more specific your criteria the faster your query will be.

Edit 1: OpenSQL Date functions are implemented with 7.51 they support calculations, but not extraction. SAP Hana SQL - Datetime functions lists the function you tried to use, but it's a SAP Hana SQL reference page.

Edit 2: To clarify, Abap SQL is just a later (starting ABAP 7.53 and higher) iteration of Open SQL. Rename to "Abap SQL" is directly related to transition to HANA database and cease of support of DB engines other than HANA DB. Backwards compatibility stays, but "some features" (likely meaning new features) will only be supported by HANA DB.

As a side note, latest versions of Abap (together with Hana DB) brings support to numeric date formats (as an option to use instead of current CHAR8 format). Extraction of years/months from numeric date is not as simple as substring, so I assume support for those kind of functions should appear in 'Abap SQL'.

Zero
  • 1,562
  • 1
  • 13
  • 29
  • like '2017%' work like a charm. Thanks you so much. – Hien Phan Jan 10 '20 at 09:26
  • 1
    Erratum: `ABAP SQL` is the new name of `Open SQL` since ABAP 7.53 – Sandra Rossi Jan 10 '20 at 09:59
  • 2
    @SandraRossi Ah yes, but transition to abap sql is directly related to transition to Hana DB. One of those versions between 7.51 and 7.53 stops supporting other databases, which is why new (hana-specific) SQL functions start being implemented. I figured that makes the difference between Open SQL and Abap SQL significant enough to separate them... I suppose I'll add this info, to avoid further confusion. – Zero Jan 10 '20 at 13:18
  • Oh I see. Some few information: [ABAP SQL](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/abennews-753-abap_sql.htm#!ABAP_MODIFICATION_1@1@): "This renaming reflects that some parts of ABAP SQL now only support certain database platforms, specifically SAP HANA database, and hence that it is no longer fully platform-independent." and [ABAP changes from 7.52 to 7.53](https://launchpad.support.sap.com/#/notes/2519921): "AS ABAP 7.53 is part of an ABAP system for the ABAP platform of SAP S/4 HANA Cloud Edition (CE) Release 1808 and SAP S/4 HANA On Premise (OP) Release 1809." – Sandra Rossi Jan 10 '20 at 14:04
  • `One of those versions between 7.51 and 7.53 stops supporting other databases` not fully, but only some features. Or maybe you know some roadmap insights? – Suncatcher Jan 11 '20 at 15:26
  • @Suncatcher You are right. I conflated `Abap 7.53` with `Abap SQL` and ended up writing about them as if they were the same thing. I was talking about Abap SQL. They officially support SAP R/3 until 2025. Since R/3 supports other DBs, as long as Abap 7.53 is compatible with R/3. that at very least guarantees backwards compatibility until 2025. With that said, It is likely all new features introduced after `Abap SQL` will only work on HANA DB, given how they said having to support multiple databases was a constraint in terms of SQL features they can implement. – Zero Jan 13 '20 at 08:09
1
SELECT * FROM ekko
  INTO TABLE @data(result)
  WHERE [...]
    AND aedat LIKE '2017%'.

The format you see in your screenshot is not what’s really in the database. SAP GUI formats values based on their data elements when displaying them.

Date values mostly have the data type DATS which encodes dates as CHAR(8) of the form YYYYMMDD. You can verify this by selecting a single AEDAT and using WRITE without any formatting options to output it on the screen in its raw format.

Thus, the simplest way to query by the year portion is with the condition LIKE '2017%' that matches every string that starts with "2017".

Florian
  • 4,821
  • 2
  • 19
  • 44
  • I tried but same error as YEAR. Sorry but I can't put image about error, I paste error right here : Boolean exception is required in positions starting with LEFT(AEDAT). Have you think another way ? – Hien Phan Jan 09 '20 at 07:38
0

This is not possible with abap sql. If you want to execute query then pass range date field for the particular year.

Following is the smaple code for retrieving data of year 2017.

DATA lr_aedat TYPE RANGE OF ekko-aedat. DATA ls_aedat LIKE LINE OF lr_aedat.

ls_aedat-low = '20170101'.
ls_aedat-high = '20171231'.
ls_aedat-sign = 'I'.
ls_aedat-option = 'BT'.
APPEND ls_aedat TO lr_aedat.

SELECT * FROM EKKO
  INTO TABLE @data(t_result)
  WHERE
*     WAERS = 'USD'
     AEDAT IN @lr_aedat.
Umar Abdullah
  • 1,282
  • 1
  • 19
  • 37