Comparing NVARCHAR with INT in hana

[Former Member]

Hi,

A strange observation. Can anyone pls explain. I am trying to create a procedure in hana something like this:

create procedure abc(

IN UserID int,

in EventID int)

LANGUAGE SQLSCRIPT 

AS      

BEGIN

DECLARE cnt INT := 0;

SELECT COUNT(*) INTO cnt FROM SCHEMA."ABCD"

  WHERE "EventID" = :EventID AND "UserID" = :UserID AND "RoleID"=(SELECT "RoleID" FROM SCHEMA."aa" WHERE "ggg"= 'XYZ');

  select :cnt,:EventID, :UserID from dummy;

END;

In Table ABCD the EventID column is NVARCHAR(100) and UserID is INT type. When i execute this procedure using

call abc(1,2)

it does not give me correct result. It returns cnt as 0 whereas actually output should be 1. I tried playing with the query somewhat and found that if i replace the part "EventID" = :EventID with "EventID" = 2 then it gives correct output. I finally tried replacing this clause with "EventID" = TO_INT(:EventID) and it gives correct output. The question is why is it behaving in such a manner ?  Why do i need to use TO_INT function for my EventID input ?

TIA.

[lbreddemann · Accepted solution]

Hi Nikhil,

I wasn't able to reproduce this.

create column table abc (aaa nvarchar (40), bbb nvarchar(40));


create procedure abc_p(
IN aaa int,
in bbb int)
LANGUAGE SQLSCRIPT  
AS       
BEGIN
DECLARE cnt INT := 0;

    SELECT COUNT(*) INTO cnt 
    FROM abc 
    WHERE 
        aaa = :aaa 
    AND bbb = :bbb; 

    SELECT :cnt, :aaa, :bbb 
    FROM dummy;

END;




insert into abc values (1, 2);




call abc_p (1, 2);

:CNT	:AAA	:BBB
1	1	2

This was on Rev. 94, so maybe you're looking at a bug in an older rev.

- Lars

[Former Member · Accepted solution]

Hi Lars,

Thanks for your reply. I am having HANA One version Rev 80.0. Moreover, in my case in table ABCD EventID column is NVARCHAR(100). Just an information since I do not know about the data types of columns in table abc.

Thanks,

Nikhil

[lbreddemann · Accepted solution]

The length of the VARCHAR column shouldn't affect whether or not implicit type casting is triggered or not.

Right now, I'd propose to install at least the last SPS 8 revision.

As the issue doesn't seem to reproduce on SPS 9, there might not be another solution to this.

- Lars