0
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?

NovaDragmatika
  • 3
  • 1
  • 3
  • 1
    What DBMS is this question related to? The code looks like it is meant for SAP HANA but the question has tags for mysql and postgresql, too. – Lars Br. Mar 11 '21 at 21:05
  • Your insert have no commit after it, but you went a wrong way. Lars gave you an answer on how to obtain count in HANA (it is a specific, count is persisted in metadata). – astentx Mar 12 '21 at 00:05

1 Answers1

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.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • Thanks for the answer. I've thought about this solution already before posting. The fact is that the count shouldn't be done on the whole table, but grouped by "ID_FILE". Inside the TABLE_NAME, I have millions of records, but the correct information is to get ID_FILE and RECORD_COUNT. Thats why I thought about the dynamic SQL. – NovaDragmatika Mar 13 '21 at 11:56
  • 2
    So, you asked for one thing, got an answer but wanted to know something different, that does not relate to the code provided in the original question. – Lars Br. Mar 13 '21 at 13:05
  • I probably didn't explain correctly. Thanks anyway! – NovaDragmatika Mar 14 '21 at 17:33