TIP: SQL Query data in SAP

It's very useful to be able to look at data in SAP if you are familiar with SQL.

Note: You can always use transaction SQVI to build views and query data if you are not familiar with SQL.

Use Transaction ST04 -> Diagnostics -> SQL Editor

Enter the SQL in the Input Query tab and "Execute" -> View the results in the "Result" tab


Examples:


EH Creation by month

(SELECT SUBSTR(S3.CREATED_DATE,2,6), count (*)

FROM "SAPEM"."/SAPTRX/EH_HDR" AS S3

WHERE S3.CLIENT = '100'

GROUP by SUBSTR(S3.CREATED_DATE,2,6)

ORDER BY SUBSTR(S3.CREATED_DATE,2,6))


Number of Control Parameters by month

(SELECT SUBSTR(S1.CREATED_DATE,2,6), count (*)

FROM "SAPEM"."/SAPTRX/EH_HDR" AS S1

INNER JOIN "SAPEM"."/SAPTRX/EH_CNTRL" AS S2 ON S2.EH_GUID = S1.EH_GUID

WHERE S1.CLIENT = '100'

GROUP by SUBSTR(S1.CREATED_DATE,2,6)

ORDER BY SUBSTR(S1.CREATED_DATE,2,6))


Event Code Set Count by Day

(SELECT S7.EVTDAT, S7.EVTCOD, COUNT(*)

FROM "SAPEM"."/SAPTRX/EVM_HDR" AS S7

WHERE S7.CLIENT = '100' and

substr(s7.evtdat,0,6) >= '201906'

GROUP BY S7.EVTDAT,S7.EVTCOD

ORDER BY S7.EVTDAT,S7.EVTCOD)


Unprocessed Events By Month

(SELECT substr(s1.PRCTST,2,6), COUNT(*)

FROM "SAPEM"."/SAPTRX/EVM_UNPR" AS S1

WHERE S1.CLIENT = '100' and

substr(s1.PRCTST,2,6) >= '201801'

GROUP BY substr(s1.PRCTST,2,6)

ORDER BY substr(s1.PRCTST,2,6))


Unprocessed Events By Month by Event ID

(SELECT substr(S1.PRCTST,2,6), S2.INT_EV_COD, COUNT(*)

FROM "SAPEM"."/SAPTRX/EVM_UNPR" AS S1

inner join "SAPEM"."/SAPTRX/EVM_HDR" AS S2 ON S2.EVT_GUID = S1.EVT_GUID

WHERE S1.CLIENT = '100' and

substr(S1.PRCTST,2,6) >= '201906'

GROUP BY substr(S1.PRCTST,2,6),S2.INT_EV_COD

ORDER BY substr(S1.PRCTST,2,6),S2.INT_EV_COD)


Event Message count by month

(SELECT S7.event_code,SUBSTR(S7.PROC_DATE,2,6),COUNT(*)

FROM "SAPEM"."/SAPTRX/EH_EVMSG" AS S7

WHERE S7.CLIENT = '100'

AND SUBSTR(S7.PROC_DATE,2,6) >= '201906'

GROUP BY SUBSTR(S7.PROC_DATE,2,6),S7.EVENT_CODE

ORDER BY SUBSTR(S7.PROC_DATE,2,6),S7.EVENT_CODE)

92 views

© 2020 by ERPGenie. All Rights Reserved