从一个表中找到的多个表中选择HANA SQL Select Count(*) [英] HANA SQL Select Count (*) from multiple tables found in a table

问题描述

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?

推荐答案

OP似乎希望将表列表的 raw 记录计数存储到另一个表中.

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

无需使用SQLScript即可满足此要求.

This requirement can be met without the use of SQLScript.

SAP HANA保留目录表中可用表中已提交记录的数量，例如 [M_TABLES] [1] .

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

有了这些信息，就可以像这样重写 INSERT 语句:

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.

这篇关于从一个表中找到的多个表中选择HANA SQL Select Count(*)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！