Skip to Content
Technical Articles
Author's profile photo Mohd Danish Imam

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!

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.