Hello Rajarshi,
Please find the below link, it will help you to answer your questions.
Regards,neha
I already have that guide .. and no .. it does not have the answers I was needing ...
Here to go..
1. Table types are used to define parameters for a procedure that represent tabular results. Here no row and column storage come into picture.Table types are only used while procedure come into picture.
2. Call "_SYS_BIC". "<procedure_name>" (VAR_result, '2004001, '2004002', @VAR_Input)
Here var_result is the output parameter table, '2004001' and '2004002' are input parameter scalar, @var_input imput parameter table.
Hope it help you.
Regards,neha
Neha
Thanks for answering !! I thought so too for the type table , but wondered why it shows a symbol as a row table in the column view .
the second question is still not answered as , i can pass params when i use call procedure
CALL DEVGUIDE.GETCOLDPRECIPITATIONX(-2, null );
but cant find the syntax when i try to call the RESULT VIEW
Hello,
1. You can see the your created table type under the Schema -> Procedure -> Table type.
If you open the definition then the type is table type rather than row or column store.
2. For your second question, if I understood correctly you want to see the data of your resulted view that you have created. In your example it is "Procview".
If this so, then have two option two preview your data
Hope it is clear to you.
Regards,neha
Neha: Actually I just noticed that the link you pasted was for older version of the SQL script guide ..
The new SQL script guide does not have the same content ...
I am working with release 24 of HANA DB , maybe thats why its not supported
thanks
Hi Rajarshi,
Try this in your SQL Editor
SELECT * FROM PROCVIEW WITH PARAMETERS('placeholder'=('$$LOW_TEMP$$','25'));
In place of ‘25’, put your value in single quotes for integer.
Hopefully it will help you out.
Regards,
Ruchi
Neha : select from procview works perfect when we are not passing input params , which was my query .
ruchi : i had tried that approach seeing the sqlscript easy guide , it works for string params , but not for numbers , i had tried different permutation of it .
did not work yet
Hi Rajarshi,
This statement is working perfectly in my system, as your scenario i have also taken the IN parameter as INTEGER which need single quotes and varchar takes 3 single quotes( don’t know why exactly).
You will not able to veiw the data of PROCVIEW,if you are using IN parameter because it genrate the data only when you will supply the required parameters.
-Ruchi
Message was edited by: Ruchi Jain
ruchi : your post gives me hope 
, let me copy paste my entire code and you can point out where I am going wrong :
CREATESCHEMA DEVGUIDE;
CREATE COLUMN TABLE DEVGUIDE.LOCAL_CLIMATE
(
LOCATION NVARCHAR (60),
TEMPERATURE_LOW INTEGER,
TEMPERATURE_HIGH INTEGER,
ANNUAL_PRECIPITATION DOUBLE
);
INSERT INTO DEVGUIDE.LOCAL_CLIMATE VALUES ('BERLIN', -2, 24, 570.7);
INSERT INTO DEVGUIDE.LOCAL_CLIMATE VALUES ('HEIDELBERG', 3, 20, 745.0);
INSERT INTO DEVGUIDE.LOCAL_CLIMATE VALUES ('BIEL', -2, 25, 1203.0);
INSERT INTO DEVGUIDE.LOCAL_CLIMATE VALUES ('DUBLIN', 5, 15, 769.0);
INSERT INTO DEVGUIDE.LOCAL_CLIMATE VALUES ('MILANO', -2, 29, 943.2);
INSERT INTO DEVGUIDE.LOCAL_CLIMATE VALUES ('VANCOUVER', -1, 22, 1181.5);
/* Create Table Type */
CREATE TYPE DEVGUIDE.PCPTYPE AS TABLE (LOCATION NVARCHAR (60), ANNUAL_PRECIPITATION REAL);
/* Create Procedure */
CREATE PROCEDURE DEVGUIDE.GETCOLDPRECIPITATION (IN LOW_TEMP INTEGER, OUT PRCPT DEVGUIDE.PCPTYPE)
LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW DEVGUIDE.PROCVIEW AS
BEGIN
PRCPT = SELECT LOCATION, ANNUAL_PRECIPITATION FROM DEVGUIDE.LOCAL_CLIMATE WHERE TEMPERATURE_LOW = :LOW_TEMP ;
END;
/* Call Procedure */
CALL DEVGUIDE.GETCOLDPRECIPITATION(-2, null );
/* The select statement from procview should give the same results as the call procedure */
SELECT * FROM DEVGUIDE.PROCVIEW WITH PARAMETERS('placeholder'=('$$LOW_TEMP$$','-2'));
ruchi : which version of HANA DB ( newDB) are you using ?
Hi Rajarshi,
I am using HanaDB version 21. And statements are working perfectly in my system.
-Ruchi
Hello,
SELECT * FROM DEVGUIDE.PROCVIEW WITH PARAMETERS('placeholder'=('$$LOW_TEMP$$','-2'));
One thing, when you are passing the parameter the it should be in lower case "low_temp". So, can you please use the lower case and try out the same.
Hope it resolve your problem.
Regards,neha
Thanks Neha and Ruchi !!!
and lower case worked .... .. who would have thought of it
Hi Neha,
Can you suggest me syntax for executing the column view while passing 2 input parameters?
for one parameter it is as given above
SELECT * FROM DEVGUIDE.PROCVIEW WITHPARAMETERS('placeholder'=('$$LOW_TEMP$$','-2'))
but how to pass 2 input parameters? and what is the significance of 'placeholder'?
Thanks and Regards,
Vineet.
Hi,
I found the solution. So just thought of updating it for others benefit.
suppose we have two input parameters i.e. low_temp and low_temp1, the query will be
SELECT * FROM DEVGUIDE.PROCVIEWWITHPARAMETERS('placeholder'=('$$LOW_TEMP$$','-2'),'placeholder'=
('$$LOW_TEMP$$','-3'))
Thanks and Regards,
Vineet.
