Dynamically Query table content by help of SQL script

[JoergBoeke]

How To Query any table content by HANA SQL Script

Have you ever had the problem that data within tables does not match with your expectations?

Especially working with SAP BW -BW4/HANA is often happens that data in dataloads  might stall the upload to e.g. Business Warehouse solutions caused by strange characters or strings.

Analyzing the individual tables might consume a lot of time because sometimes you know the problematic sting but you’re not sure what column of table is carrying that false information.

My little help script can brighten your day.

The script can be used directly in SQL console ( proper authorization required to execute or analyze the desired table(s)) or can be used in table functions or HANA procedures.

In first part of script all declarations are made and you need to exchange the part (yellow highlighted) within the modification area.

Working with SAP HANA tables you need to define the table schema, table name ( of physical table or HANA view, as well as the name for temporary table (don’t forget to add the ‘#’ prefix.

During execution, the script will analyze the given table and generates it’s structure.

In second step, the script will query the table for defined search string ‘ %bier%’ in my case.

The query inserting the lookup result (select 'INSERT INTO ' || TEMP_TABLE_NAME ||…) can be modified as well. In my case it just looks for the top3 occurances.

As said, all tables can be used as select criteria in SOURCE_TABLE parameter.

Restrictions:

Due to my actual restriction to read the structure from sys.table_columns views are not supported but could be added in coding similar to my access to table_columns as shown below

All entries in screen display SAP schema SYS and its official views, available in any SAP HANA system ( by having SELECT authorizations on that schema)

 HANA system views

Just use the following script

 SAP HANA SQL SCRIPT CODE

DO

BEGIN

                 DECLARE SQLQUERY NCLOB;

                DECLARE SEARCH_STRING NVARCHAR(5000);

                DECLARE TEMP_TABLE_NAME nvarchar(256);

                DECLARE SOURCE_TABLE nvarchar(256);

                DECLARE SCHEMA_NAME nvarchar(30);

                DECLARE MYcolumn_name  NVARCHAR(5000);

--declare cursor for looping over dynaic columns

                DECLARE CURSOR cur FOR   SELECT COLUMN_NAME

                        FROM SYS.TABLE_COLUMNS

                        WHERE SCHEMA_NAME = :schema_name AND TABLE_NAME = :SOURCE_TABLE;

 --MODIFICATION AREA----                      

---set YOUR values-----e.g. the search expression 'bier' might not suit you

                SCHEMA_NAME          = YOUR_TABLE_SCHEMA;

                SOURCE_TABLE          = YOUR_SPECIFIC PHYSICAL_TABLE';

                TEMP_TABLE_NAME    = '#tempTABLENAME';

                SEARCH_STRING         =  '%bier%';

--build dynamic internal temporal table for all column Look Up

                WITH columns as (

                 SELECT

                  position,

                  '"' || column_name ||

                  '" ' || data_type_name ||

                  '(' || length ||

                  (case when scale is null then '' else ',' || scale end) || ')' as columnStr

                 from sys.table_columns

                 where table_name = :SOURCE_TABLE

                 order by position

                )

                SELECT

                 'create local temporary table ' || TEMP_TABLE_NAME || ' ( ' || String_Agg(columnStr, ', ' order by position) || ');'

                 into "SQLQUERY" from columns;

                EXECUTE IMMEDIATE :SQLQUERY;

-- end of create temp table

--Now do the real SQL Work to spot the data you're looking for

--loop over the columns of given table

                 FOR column_row as cur

                                   DO

---MYcolumn_name for the just selected colum

                                 MYcolumn_name := column_row.COLUMN_NAME;

--Search in individual column only for top 3---matter of your specific changes in SQL Querying the data in given table

--MODIFICATION AREA----

-- The insert query string can be modifies as well 😊

select 'INSERT INTO ' || TEMP_TABLE_NAME || ' ("' || MYcolumn_name || '") SELECT top 3 "' || MYcolumn_name || '"  FROM ' ||SCHEMA_NAME || '.' || SOURCE_TABLE || ' WHERE "' || MYcolumn_name || '" like '||

                                  CHAR(39) || SEARCH_STRING || CHAR(39)  || ';' into "SQLQUERY"  from dummy;

                                  --run SQL Query

                               EXECUTE IMMEDIATE :SQLQUERY;           

                END FOR;           

--display found data at the end

                SELECT 'SELECT * FROM ' || TEMP_TABLE_NAME into "SQLQUERY" from dummy;

                EXECUTE IMMEDIATE :SQLQUERY;

                -- clean up tmp tables

                SELECT 'DROP TABLE ' || TEMP_TABLE_NAME into "SQLQUERY" from dummy;

                EXECUTE IMMEDIATE :SQLQUERY;

END;