Fetch current date records based on timestamp fiel...

Former Member · ‎2019 Jan 29

Hi Experts,

I am trying to fetch data based on timestamp filed in CDS View, where I want to fetch records created with today timestamp.

TSTMP_CURRENT_UTCTIMESTAMP() function giving an error in WHERE condition as "Random expressions are not allowed in a condition". Is there anyway to pass Today Starting Timestamp and current timestamp in where condition in ABAP CDS Views.

maheshpalavalli · ‎2019 Jan 29

Hi Amarnadh,

Unfortunately you cannot use the function in the where condition. You can use only to format data in the column.

For this, you need to use the environment information, check the below:

https://blogs.sap.com/2015/11/25/abap-news-for-release-750-environment-information-in-abap-cds/

BR,

Mahesh

Former Member · ‎2019 Feb 19

Hi Mahesh,

Yes. But unfortunately I cannot use CDS with parameters which will be easier to pass the timestamp. I am able to acheive to get the current timestamp but not able to create a custom timestamp (like 20190214000000 with DEC type). Using Concatenate function, I can get CHAR type with starting timestamp but this will lead to type conflict issue when I pass the parameter to timestamp field).

Is there any other possibility where I can achieve the fetching of today records based on timestamp..!

maheshpalavalli · ‎2019 Feb 19

Got it.. can you tell me how you access the CDS view?

Former Member · ‎2019 Feb 20

Yes..it should be called dynamically where the CDS View name only can be maintained with no WHERE Condition

maheshpalavalli · ‎2019 Feb 20

As far as i know you need to send it from outside as a filter.. If you using it in odata service then we can pass as fiters in dpc_ext (we will not call the view, framework will but we will only pass additional filter values).

If possbile tell me the exact scearnio how you execute this cds view.

BR,
Mahesh

Former Member · ‎2019 Feb 20

This CDS View will be configured in a seperate table. This list will be displayed in a dropdown for user where the selection will be dynamic(User can select CDS View fields from the report ).So only the fields selected will be passed dynamically to the selected CDS View and display output. Here I cannot pass seperate parameters for the CDS view.

jrodriguezferna · ‎2019 Feb 22

An idea:

- Create a new transparent table with 2 colums (Z_AUXILIAR_TABLE):

--> Column 1, as a Key, type UUID (GUID)

-> Column 2, as normal attribute, type timestamps

- Everytime, and before use your CDS, create a new UUID, and insert in your new table Z_AUXILIAR_TABLE with the timestamp parameter

- Do an inner join in your ABAP code like this:

Select *
    from Z_MY_CDS 
       inner join Z_AUXILIAR_TABLE
          on Z_MY_CDS~my_timestamp_field = Z_AUXILIAR_TABLE~my_timestamp_field
         and Z_AUXILIAR_TABLE~Column1 = 'MY_NEW_UUID'
    where ........

- Last, delete the new entrie from Z_AUXILIAR_TABLE

I know that is not the best solution, but could be solved your problem.

Former Member · ‎2019 Feb 26

Hi Juan,

I think this will not resolve the issue where the custom timestamp cannot be created.

I am wondering whether this feature is not available in CDS or am I missing something on this topic..!
Also, for the suggestion you mentioned, with AUX Table I can not fetch the data for today records like data between 20190226000000(Starting Timestamp) To 201902261300204(Current Timestamp)

jrodriguezferna · ‎2019 Feb 26

You can create the Z-table with two timestamps columns, like timestamsp-begin and timestamsp-end, and then:

on Z_MY_CDS~my_timestamp_field between Z_AUXILIAR_TABLE~my_timestamp_field_begin and Z_AUXILIAR_TABLE~my_timestamp_field_end

jrodriguezferna · ‎2019 Feb 26

Full solution:

report zdummy1.

start-of-selection.
  perform start_of_selection.

form start_of_selection.

  delete from z_main_table.

  commit work and wait.

  " do ... times. .... enddo. => load my main table with dummy data
  do 10 times.

    data(ls_z_main_table)
        = value z_main_table(
            mandt    = sy-mandt
            customer = sy-index
        ).

    get time stamp field ls_z_main_table-creation_time.

    insert z_main_table from ls_z_main_table.

    wait up to 1 seconds.

  enddo.

  commit work and wait.

  data(ls_z_aux_table)
      = value z_aux_table(
        mandt = sy-mandt
        uuid  = cl_uuid_factory=>create_system_uuid( )->create_uuid_x16( )
      ).

  get time stamp field ls_z_aux_table-tms_end.

  ls_z_aux_table-tms_begin = cl_abap_timestamp_util=>get_instance( )->tstmp_add_seconds(
        iv_timestamp               = ls_z_aux_table-tms_end
        iv_seconds                 = -6
  ).

  insert z_aux_table from ls_z_aux_table.

  commit work and wait.

  select z_main_cds~*
      from z_main_cds
          inner join z_aux_table
              on z_main_cds~creation_time 
                between z_aux_table~tms_begin and z_aux_table~tms_end
             and z_aux_table~uuid = @ls_z_aux_table-uuid
      into table @data(lt_data_out).

  delete from z_aux_table where uuid = ls_z_aux_table-uuid.

  cl_salv_table=>factory(
*    exporting
*      list_display   = IF_SALV_C_BOOL_SAP=>FALSE    " ALV Displayed in List Mode
*      r_container    =     " Abstract Container for GUI Controls
*      container_name =
    importing
      r_salv_table   = data(lr_salv_table)    " Basis Class Simple ALV Tables
    changing
      t_table        = lt_data_out
  ).

  lr_salv_table->display( ).

endform.

shilpabasavaraj · ‎2019 Feb 26

Hi Amarnadh,

I recommend you to use the concept of AMDP class and table function here. I also had a similar kind of requirement and i have implemented using AMDP class. Here you will be able to write your query in the Class using abap functions. Then you can use this table function in your CDS view to fetch required data

Step1: Create a Table function -> under Core Data Services - Data Definitions. A CDS view will be created. Delete the entire code and add the following

@AccessControl.authorizationCheck: #NOT_REQUIRED

@EndUserText.label: 'Fetch Data'

define table function ZWG_I_DATA returns

{ mandt: abap.clnt;

kunnr : numc10;

date_to : abap.dats;

curr_date : abap.dats; }

// the next section is a binding to CLASS and a METHOD for AMDP

implemented by method zcl_dcd_getdata=>ZM_GET_LATEST_DATA;

Step 2: Then create a class under source code library - Classes:

CLASS zcl_dcd_getdata DEFINITION

PUBLIC

FINAL

CREATE PUBLIC .

PUBLIC SECTION.

INTERFACES if_amdp_marker_hdb.

CLASS-METHODS ZM_GET_LATEST_DATA

* the following line is the binding to my CDS, so I do not need to define any types

FOR TABLE FUNCTION ZWG_I_DATA.

PROTECTED SECTION.

PRIVATE SECTION.

ENDCLASS.

CLASS zcl_dcd_getdata IMPLEMENTATION.

METHOD ZM_GET_LATEST_DATA

* this section is mandatory for HANA-AMDP's to work BY DATABASE FUNCTION FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY * end of the HANA-specific section

USING table_name.

RETURN

-- this part is an SQL script executed on HANA

SELECT mandt, kunnr, date_to, current_date as curr_date from table_name WHERE date_to > current_date GROUP by mandt, kunnr, date_to;

--Here i have used current_date function which cannot be implemented using CDS view.

-- end of the HANA script ENDMETHOD. ENDCLASS.

Step 3: Add a join in your basic CDS view using the table function name.

Hope this will resolve your issue. Also you can use CURRENT_UTCTIMESTAMP function

Fetch current date records based on timestamp field in CDS