Use of dummy table for mocking and testing logic

Consider a scenario where you do not have data in the underlying table. Could be a customizing or application table or any other table. You need to test, build your SQL query logic. Since there is no data in the underlying table what do you do?

  1. Try and create test data on your own, but for that you need to be well versed with the business processes.
  2. Ask a functional expert to create test data for you across different landscape.

Well in any case you would not be able to test your SQL query immediately and enhance the logic.

Well, you can make use of dummy table in such a scenario to test, build your logic in case of data insufficiency.

Let’s take an example.

Table T007K, T007L, and BSET does not have the data to test the business logic that we have built for our legal reporting requirement.

Table T007K,is a customizing table whereas table BSET is an application table. Although T007K is a customizing table it requires the tax code (MWSKZ) to be present in the referenced table. Now if the tax codes are not created in the system you will have to go through the pain of creating the tax codes to test or build your logic.

Similarly, the table BSET is an application table which contains transactional data. To fill the table, you will have to know how to post an accounting document. That’s where we can make use of dummy table.

You have built the following logic where there is a select on table T007K and left outer join with BSET based on tax code and transaction key.

set schema <SCHEMANAME>; -- SET SCHEMA NAME HERE WITHOUT <>
set 'CDS_CLIENT' = ''; -- SET YOUR CLIENT HERE set 'APPLICATIONUSER' = ''; --SET YOUR APPLUCATION USER HERE DO BEGIN SELECT BSET.BUKRS,
BSET.GJAHR,
BSET.BELNR,
BSET.HWBAS,
BSET.HWSTE,
T007K.MWSKZ,
T007K.KTOSL,
T007K.BASGRUNO,
CASE WHEN BASGRUNO = '3'
THEN BSET.HWBAS
ELSE 0.00
END AS ZERORATETAX, CASE WHEN BASGRUNO = '4'
THEN BSET.HWBAS
ELSE 0.00
END AS ZERORATEONLYEXPORTEDGOODS FROM T007K LEFT OUTER JOIN BSET ON BSET.MANDT = T007K.MANDT AND BSET.MWSKZ = T007K.MWSKZ AND BSET.KTOSL = T007K.KTOSL
WHERE T007K.MANDT = '500'
AND T007K.LAND1 = 'ZA' AND T007K.VERSION = 'ZA01';
END

The above query does not return any data as there are no records in the underlying table.

ZeroResults

Zero results

Use of dummy table to mock the data and test business logic

Let’s take it step by step. First lets mock the data for the source table i.e. T007K.

set schema <SCHEMANAME>; -- SET SCHEMA NAME HERE WITHOUT <>
set 'CDS_CLIENT' = ''; -- SET YOUR CLIENT HERE
set 'APPLICATIONUSER' = ''; --SET YOUR APPLUCATION USER HERE do begin lt_t007k = ( ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A1' AS MWSKZ, 'MWS' AS KTOSL, '3' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A2' AS MWSKZ, 'MWS' AS KTOSL, '4' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A3' AS MWSKZ, 'MWS' AS KTOSL, '5' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA02' AS VERSION, 'A4' AS MWSKZ, 'MWS' AS KTOSL, '5' BASGRUNO from dummy ) ); select * from :lt_t007k; end

T007K%20mock%20data

T007K mock data

On to the next step!

Similarly we will mock the data for application table i.e. BSET.

set schema <SCHEMANAME>; -- SET SCHEMA NAME HERE WITHOUT <>
set 'CDS_CLIENT' = ''; -- SET YOUR CLIENT HERE
set 'APPLICATIONUSER' = ''; --SET YOUR APPLUCATION USER HERE do begin lt_t007k = ( ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A1' AS MWSKZ, 'MWS' AS KTOSL, '3' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A2' AS MWSKZ, 'MWS' AS KTOSL, '4' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A3' AS MWSKZ, 'MWS' AS KTOSL, '5' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA02' AS VERSION, 'A4' AS MWSKZ, 'MWS' AS KTOSL, '5' BASGRUNO from dummy ) ); select * from :lt_t007k; lt_bset = ( ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D1' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A1' AS MWSKZ, 'MWS' AS KTOSL, 1000.00 AS HWBAS, 0.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D2' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A1' AS MWSKZ, 'MWS' AS KTOSL, 1100.00 AS HWBAS, 0.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D3' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A2' AS MWSKZ, 'MWS' AS KTOSL, 1500.00 AS HWBAS, 0.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D4' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A3' AS MWSKZ, 'MWS' AS KTOSL, 2000.00 AS HWBAS, 50.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D5' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A2' AS MWSKZ, 'MWS' AS KTOSL, 3000.00 AS HWBAS, 0.00 AS HWSTE from dummy ) ); SELECT * FROM :LT_BSET; end

BSET mocked data

Now the data is ready let’s integrate it in our logic. We would remove the reference to table T007K and BSET from our code and use :lt_t007k and :lt_bset instead.

set schema <SCHEMANAME>; -- SET SCHEMA NAME HERE WITHOUT <>
set 'CDS_CLIENT' = ''; -- SET YOUR CLIENT HERE
set 'APPLICATIONUSER' = ''; --SET YOUR APPLUCATION USER HERE do begin lt_t007k = ( ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A1' AS MWSKZ, 'MWS' AS KTOSL, '3' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A2' AS MWSKZ, 'MWS' AS KTOSL, '4' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA01' AS VERSION, 'A3' AS MWSKZ, 'MWS' AS KTOSL, '5' BASGRUNO from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA' AS LAND1, 'ZA02' AS VERSION, 'A4' AS MWSKZ, 'MWS' AS KTOSL, '5' BASGRUNO from dummy ) ); select * from :lt_t007k; lt_bset = ( ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D1' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A1' AS MWSKZ, 'MWS' AS KTOSL, 1000.00 AS HWBAS, 0.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D2' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A1' AS MWSKZ, 'MWS' AS KTOSL, 1100.00 AS HWBAS, 0.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D3' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A2' AS MWSKZ, 'MWS' AS KTOSL, 1500.00 AS HWBAS, 0.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D4' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A3' AS MWSKZ, 'MWS' AS KTOSL, 2000.00 AS HWBAS, 50.00 AS HWSTE from dummy ) UNION ALL ( select '500' AS MANDT, 'ZA01' AS BUKRS, 'D5' AS BELNR, '2022' AS GJAHR, '001' AS BUZEI, 'A2' AS MWSKZ, 'MWS' AS KTOSL, 3000.00 AS HWBAS, 0.00 AS HWSTE from dummy ) ); SELECT * FROM :LT_BSET; lt_output = SELECT BSET.BUKRS, BSET.GJAHR, BSET.BELNR, BSET.HWBAS, BSET.HWSTE, T007K.MWSKZ, T007K.KTOSL, T007K.BASGRUNO, CASE WHEN BASGRUNO = '3' THEN BSET.HWBAS ELSE 0.00 END AS ZERORATETAX, CASE WHEN BASGRUNO = '4' THEN BSET.HWBAS ELSE 0.00 END AS ZERORATEONLYEXPORTEDGOODS FROM :lt_t007k as T007K LEFT OUTER JOIN :lt_bset as BSET ON BSET.MANDT = T007K.MANDT AND BSET.MWSKZ = T007K.MWSKZ AND BSET.KTOSL = T007K.KTOSL WHERE T007K.MANDT = '500' AND T007K.LAND1 = 'ZA' AND T007K.VERSION = 'ZA01'; select * from :lt_output; end

Final Result

And just like that we can test our logic using dummy table.

Conclusion

Whenever you have written a new logic or would want to test the logic with different scenarios and underlying tables don’t have sufficient data you can make use of dummy table and mock the data. If the blog added something to your knowledge and helped you in your daily development tasks let me and the community know in the comment section and drop a like. It would be very interesting to see  different scenarios in which you made use of dummy table using the above approach. Your feedback and comments are much appreciated.

If you have questions related to following topics please refer to the links.

Happy learning and Thank you for reading!