CODEWITHSUNDEEP
hanahana-sql-script
Sangamesh Hs
Sangamesh Hs

Reputation: 1447

Scalar type not allowed : SQLSCRIPT

With limited knowledge of SQL, I write a procedure on HANA database and here is small part of my code.

CREATE PROCEDURE _SYS_BIC.claims(OUT percent_value distinct_values) 
    LANGUAGE SQLSCRIPT 
    SQL SECURITY INVOKER 
    READS SQL DATA AS 
BEGIN 
/*****************************
    write your procedure logic
******************************/

declare orgid_artikel_sofort decimal(8,0):= 0;
declare matnr_ertrag varchar(18) :=null;
declare count_result_sofort BIGINT := 0;
declare count_value BIGINT := 0;
declare verhw_value BIGINT := 0;
declare vnthw_value BIGINT := 0;
declare refid_value decimal(9,0) := 0;
declare cntr BIGINT :=0;


RESULT_SOFORT =  select "REFID", "ORGID_WE", "ARTIKEL", "ORGID_ARTIKEL", "HERSTELLER", sum("WERT")as WERT, sum("BZBAS_AW") as BZBAS_AW 
                from "SYSTEM"."T000_SOFORT" 
                GROUP BY "REFID", "ORGID_WE", "ARTIKEL", "ORGID_ARTIKEL", "HERSTELLER" ; 

select count("refid") into count_result_sofort  from :RESULT_SOFORT; 

FOR RESULT_SOFORT IN 0 .. :count_result_sofort DO 

    select "REFID" into refid_value from :RESULT_SOFORT; 

    RESULT_ERTRAG = select "REFID", "LIFNR", "MATNR", "ORGID", "VNTHW", "VERHW" from "SYSTEM"."T000_ERTRAG" TO where :refid_value= "REFID" ; 

    select :ORGID_ARTIKEL into  orgid_artikel_sofort from :RESULT_SOFORT ; 

    select "MATNR", "VNTHW", "VERHW" into  matnr_ertrag. vnthw_value, verhw_value from :RESULT_ERTRAG;

    If :orgid_artikel_sofort = :matnr_ertrag then 
        percent_value := (verhw_value)/(vnthw_va1ue/100);
    End IF ; 
END FOR ; 

END ;

enter image description here

When I run the following I get an error

Error creating procedure; scalar type is not allowed: line 25 col 39 (at pos 1037) at ptime/query/checker/check_proc.cc:383

What is the scalar variable is specifying about? I am stuck please help.

Upvotes: 2

Views: 11377

Answers (2)

CodeMonkey
CodeMonkey

Reputation: 1107

I had the same error and this is how I solved it:

    PROCEDURE "SLT_DELETE"."HCDW.IT.IT::TO_TIMESTAMP_CALL" (IN IN_DATE DECIMAL(15), OUT OUT_DATE TIMESTAMP) 
   LANGUAGE SQLSCRIPT AS
   --DEFAULT SCHEMA <default_schema_name>
   --READS SQL DATA AS
BEGIN
   OUT_DATE = select to_timestamp(IN_DATE)  FROM DUMMY;
END;

The above code produced the following error: Syntax error in procedure object: scalar type is not allowed; OUT_DATE: line 7 col 4 (at pos 237)

Working version below:

   PROCEDURE "SLT_DELETE"."HCDW.IT.IT::TO_TIMESTAMP_CALL" (IN IN_DATE DECIMAL(15), OUT OUT_DATE TIMESTAMP) 
   LANGUAGE SQLSCRIPT AS
   --DEFAULT SCHEMA <default_schema_name>
   --READS SQL DATA AS
BEGIN
   select to_timestamp(IN_DATE) into OUT_DATE FROM DUMMY;
END;

Upvotes: 2

Twinkles
Twinkles

Reputation: 1994

Just a guess, in line 32 you have

SELECT :ORGID_ARTIKEL into ...

Perhaps you mean

SELECT "ORGID_ARTIKEL" into ...

Upvotes: 3

Related Questions