Skip to Content
0

Dynamic Select - Could not create catalog object: scalar type is not allowed;

Aug 17, 2017 at 02:28 PM

41

avatar image
Former Member

I'm creating a procedure to list some information and inside the main select it runs two other selects that need to be done dynamically (because they change every time).

* Procedure is set as: "READS SQL DATA AS" - Used Calculation View

I'm having a problem at the moment of creating the count in a table (dynamically).

Follow the code

PROCEDURE "S_SCHEMA"."x_package.report.fact.record_count::sp_report_count" (OUT REPORT S_SCHEMA.TYPE_REPORT) 
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER 
    READS SQL DATA AS
    CURSOR REPORT_FILES FOR
        SELECT NAME, SCHEMA_NAME, TASK_NAME, VIRTUAL_TABLE, HANA_TABLE, GET_VT 
            FROM "_SYS_BIC"."x_package.report.dim/DIM_REPORT";
BEGIN
    DECLARE VT_RECORD INTEGER;
    DECLARE HT_RECORD INTEGER;
    DECLARE VT_SQL NVARCHAR(200);

    FOR CUR_REPORT AS REPORT_FILES DO
        IF CUR_REPORT.GET_VT = '1' THEN
            BEGIN
                --EXEC 'SELECT COUNT(*) INTO VT_RECORD FROM ' || CUR_REPORT.SCHEMA_NAME || '.' || CUR_REPORT.VIRTUAL_TABLE;
                --EXEC 'SELECT COUNT(*) INTO HT_RECORD FROM ' || CUR_REPORT.SCHEMA_NAME || '.' || CUR_REPORT.HANA_TABLE;                
                
                VT_SQL    := CUR_REPORT.SCHEMA_NAME || '.' || CUR_REPORT.VIRTUAL_TABLE;
                --ERROR SHOWED IN THIS SELECT
                SELECT COUNT(*) INTO VT_RECORD FROM :VT_SQL;
                
                REPORT = SELECT * FROM :REPORT
                         UNION ALL
                         SELECT TOP 1
                            CUR_REPORT.NAME,
                            CUR_REPORT.SCHEMA_NAME,
                            CUR_REPORT.TASK_NAME,
                            CUR_REPORT.VIRTUAL_TABLE,
                            CUR_REPORT.HANA_TABLE,
                            START_TIME,
                            END_TIME, 
                            STATUS, 
                            PROCESSED_RECORDS AS RECORD_TASK, 
                            TOTAL_PROGRESS_PERCENT AS PROGRESS_TASK,
                            VT_RECORD AS RECORD_VT, 
                            HT_RECORD AS RECORD_HT
                         FROM M_TASKS 
                         WHERE TASK_NAME = ESCAPE_SINGLE_QUOTES(CUR_REPORT.TASK_NAME)
                         ORDER BY START_TIME DESC;
            END;
        ELSE 
            BEGIN

            END;
        END IF;
    END FOR;    
END;

Sorry for bad english.....

10 |10000 characters needed characters left characters exceeded

Did you find a solution? If so can you pleas post your solution?

0
* Please Login or Register to Answer, Follow or Comment.

0 Answers