Technical Articles
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?
- Try and create test data on your own, but for that you need to be well versed with the business processes.
- 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.
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 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!
What environment is this for? Direct Hana cds programming?
Do we have a dummy table to choose from in the regular Abap cds?