HANA SQL Select Count (*) from multiple tables found in a table

Question

DECLARE VI_CNT INTEGER DEFAULT 0;
DECLARE VI_IDX INTEGER;
DECLARE VI_LIMIT INTEGER;
DECLARE VS_OUTPUTSTRG1 NVARCHAR(500);
DECLARE VS_OUTPUTSTRG2 NVARCHAR(500);


/* ANAGRAFICA TABELLE FLUSSI  */

ANAGRAFICA = SELECT * 
                    FROM (SELECT DISTINCT 
                               ZCSOURSYS,
                               ZTABLE,
                               ROW_NUMBER() OVER (ORDER BY ZCSOURSYS) AS ROW_NB
                            FROM ZDAFNE_INFO);
                                                        

/************ FOR ***********/

SELECT COUNT (ZTABLE) INTO VI_LIMIT FROM :ANAGRAFICA;

FOR VI_IDX IN 1..:VI_LIMIT DO
VI_CNT = :VI_IDX;
SELECT ZTABLE INTO VS_OUTPUTSTRG1 FROM :ANAGRAFICA WHERE ROW_NB = VI_IDX;        
END FOR;

VS_OUTPUTSTRG2 := 'INSERT INTO "TEAMBW"."IFRS17.INTEGRATION.DATA_QUALITY::ZTB_DQ_DAFNE_TEST" SELECT COUNT(*) FROM '||:VS_OUTPUTSTRG1||'';
EXECUTE IMMEDIATE (:VS_OUTPUTSTRG2);

Hello everyone! Thanks in advance! Any help about this? The output doesn't insert anything... maybe I'm doing something wrong?

Answer 1

It looks like the OP wants to store the raw record count of a list of tables into yet another table.

This requirement can be met without the use of SQLScript.

SAP HANA keeps the number of committed records in tables available in catalog tables like [M_TABLES][1].

With this information available, the INSERT-statement can be rewritten like so:

INSERT INTO 
     "TEAMBW"."IFRS17.INTEGRATION.DATA_QUALITY::ZTB_DQ_DAFNE_TEST" 
       (TABLE_NAME, RECORD_COUNT)
(SELECT 
        TABLE_NAME, RECORD_COUNT
 FROM M_TABLES
 WHERE 
   SCHEMA_NAME ='xyz' 
   AND TABLE_NAME IN (SELECT DISTINCT TABLE_NAME 
                      FROM ZDAFNE_INFO)
 );

This solution works as long as no filtering of to-be-counted records in the source tables is required.