Join tables by timestamp and date columns?

Question

I should retrieve data from two log tables (BALHDR and ZIF_LOG_XML_CONTENT). My problem is that the only commonality between the log tables is the time when the entries were created. The query has to work for a PERIOD and not for a TIME POINT.

However, the time for the entries is not stored in the same format in the two tables. In ZIF_LOG_XML_CONTENT it is stored in one column TIMESTAMP in the other log table in the BALHDR it is stored in two columns, where DATE and TIME are stored separately.

I tried to transform all the times to STRING, but still not working...

What am I doing wrong?

DATA: GV_DATEANDTIMETO TYPE STRING,
      GV_DATETO     TYPE STRING,
      GV_TIMETO     TYPE STRING,
      GV_DATEANDTIMEFROM TYPE STRING,
      GV_DATEFROM   TYPE STRING,
      GV_TIMEFROM   TYPE STRING,
      GV_DATUM      TYPE STRING.

SELECT * FROM BALHDR INTO @GS_MSG_STRUKT WHERE
        EXTNUMBER = @P_EXTID AND
        OBJECT    = @P_OBJ AND
        SUBOBJECT = @P_SUBOBJ AND
        ALUSER    = @P_USER AND
        ( ALDATE_BALHDR >= @GV_INPUT_DATETO AND ALTIME_BALHDR >= @GV__INPUT_TIMETO ) AND
        ( ALDATE_BALHDR <= @GV_INPUT_DATEFROM AND ALTIME_BALHDR <= @GV__INPUT_TIMEFROM ) AND
        MSG_CNT_E >= 1 OR MSG_CNT_AL IS ZERO.

     concatenate GS_MSGTABLE-DATE GS_MSGTABLE-TIME into GV_DATUM.

     SELECT RES_CONTENT, REQ_CONTENT 
        FROM zif_log_content 
        INTO @GS_MSG_STRUKT 
        WHERE TIMESTAMP >= @Gv_date AND TIMESTAMP <= @Gv_date. 
     ENDSELECT.
ENDSELECT.

Answer 1

Concatenating works, you just need to pass timestamp into your SELECT, not string.

Here is a working simplified example based on standard BALHDR and MBEW tables:

TYPES: BEGIN OF struct,
         lognumber TYPE balhdr-lognumber,
         aldate    TYPE balhdr-aldate,
         altime    TYPE balhdr-altime,
         timestamp TYPE mbew-timestamp,
       END OF struct.

DATA: gs_msg_strukt TYPE struct.
DATA: gt_msg_strukt TYPE TABLE OF struct.

SELECT *
  FROM balhdr
  INTO CORRESPONDING FIELDS OF @gs_msg_strukt
  WHERE aldate >= @gv_input_dateto AND altime <= @gv_input_timeto.

  CONCATENATE gs_msg_strukt-aldate gs_msg_strukt-altime INTO gv_datum.
  DATA(gv_date) = CONV timestamp( gv_datum ).

  SELECT timestamp
    FROM mbew
    INTO CORRESPONDING FIELDS OF @gs_msg_strukt
   WHERE timestamp >= @gv_date AND timestamp <= @gv_date.
  ENDSELECT.

  APPEND gs_msg_strukt TO gt_msg_strukt. "<---move APPEND here
ENDSELECT.

Answer 2

This won't work as the TIMESTAMP in SAP is a decimal type and does not equal to a concatenation of the date and time in any way.

You should create your time stamp using the following sentence.

CONVERT DATE gs_msgtable-date TIME gs_msgtable-time INTO TIME STAMP DATA(gv_timestamp) TIME ZONE sy-zonlo.

Be careful also with the time zone. I do not know in which time zone your entries in Z-table are. In the BAL table they should be stored in UTC. Be sure to check it before.

Answer 3

QUESTION

I haven t got a fully working minimal example yet, but I can give you an example for the the two tables, which I would like to join together. The third table shows the wanted result. THX

-----------------------------------------------------------------------------
                               BALHDR
 ----------------------------------------------------------------------------
 | EXTNUMBER|   DATE   |   TIME |OBJECT|SUBOBJECT|  USER|MSG_ALL   |MSG_ERROR
 |---------------------------------------------------------------------------
A|  1236   |2000.10.10 |12:33:24 |KAT   |LEK      |NEK   |    NULL  | NULL
B|  1936   |2010.02.20 |02:33:44 |KAT   |MOK      |NEK   |    3     |  1
C|  1466   |2010.10.10 |11:35:34 |KAT   |LEK      |NEK   |    2     |  0
D|  1156   |2011.08.03 |02:13:14 |KAT   |MOK      |NEK   |    3     |  0
E|  1466   |2014.10.10 |11:35:34 |KAT   |LEK      |NEK   |   NULL   |  NULL
F|  1156   |2019.08.03 |02:13:14 |KAT   |MOK      |NEK   |    1     |  1


 ----------------------------------------------------------------------------
                          ZIF_LOG
-----------------------------------------------------------------------------
 |       TIMESTAMP |    REQ                  |    RES
 |---------------------------------------------------------------------------
1|  20100220023344 |      he                 |hello
2|  20101010113534 |      bla                |blala
3|  20110803021314 |      to                 |toto
4|  20190803021314 |      macs               |ka

The following table shows the wanted result. The nummbers from 1 to 4 and the letters from A to F are to helps to understand how the fields would correspond with each other.

-----------------------------------------------------------------------------
                               WANTED RESULT TABLE
 ----------------------------------------------------------------------------
  |EXTNUMBER|    DATE   |  TIME   |OBJECT|SUBOBJECT | USER|   REQ    |  RES 
 ----------------------------------------------------------------------------
 A|  1236   |2000.10.10 |12:33:24 |KAT   |LEK      |NEK   |   NULL   | NULL
B2|  1936   |2010.02.20 |02:33:44 |KAT   |MOK      |NEK   |    he    |  hello
E |  1466   |2014.10.10 |11:35:34 |KAT   |LEK      |NEK   |   NULL   |  NULL
F6|  1156   |2019.08.03 |02:13:14 |KAT   |MOK      |NEK   |   macs   |  ka

THX