Experience with CALL ... OVERVIEW ?

[former_member560074]

Hi,

how does the Call of a procedure "WITH OVERVIEW" work ?

CREATE PROCEDURE SHPL.SAMPLE12_SIMPLE_OUT ( OUT x_integer INTEGER )

LANGUAGE SQLSCRIPT

AS

BEGIN

x_integer := 1;

END

;

Calling

CALL SHPL.SAMPLE12_SIMPLE_OUT( x_integer) WITH OVERVIEW;

gives:

Could not execute 'CALL SHPL.SAMPLE12_SIMPLE_OUT( x_integer) WITH OVERVIEW'

SAP DBTech JDBC: [260] (at 111): invalid column name: X_INTEGER: line 1 col 32 (at pos 31)

Neither does:

CALL SHPL.SAMPLE12_SIMPLE_OUT(out x_integer) WITH OVERVIEW;

or

CALL SHPL.SAMPLE12_SIMPLE_OUT(out x_integer integer) WITH OVERVIEW;

do it.

Thank You

Best

Martin

[Former Member · Accepted solution]

Hi Martin,

I too faced the same issue, but calling without any parameter works fine.

CALL SYSTEM.SAMPLE12_SIMPLE_OUT(?) WITH OVERVIEW

However the above calls do not work even without 'WITH OVERVIEW'.

I will do further analysis and check what we are missing.

Regarding usage of 'with overview' you can refer SQLReference guide, which I am sure you are aware of:-)

Regards, Rahul

[former_member560074 · Accepted solution]

Hi Rahul,

CALL SHPL.SAMPLE12_SIMPLE_OUT( NULL ) WITH OVERVIEW;

at least builds up an empty HANA Developer Studio Table consisting of

the columns "variable", "table".

For Procedures which have an OUT Table with Type of Create TYPE <...> AS TABLE (..,...,...);

it also works with the NULL as described in the SAP HANA Database u2013 SQLScript Guide p.18.

The Option to write in a user defined table, Variable:OUTPUT_PUBS, Table: "SYSTEM"."OT_PUBLISHERS",

as descibrd in SAP HANA Database u2013 SQLScript Guide p.18.

does not seem to work.

It looks:

- like the Procedure need a CREATE TYPE as OUT Parameter also for the scalars

-the mentioned user defined Table option does not work

Best

Martin

[Former Member · Accepted solution]

The "with overview" is used to insert data into tables (user defined or schema level tables).

The sample code I wrote below based on your structure seems to work fine. Although I'm not sure that's what you're looking for:

CREATE TABLE COL_COSTS
( CHANNEL_ID INTEGER
, PROD_ID INTEGER
, UNIT_COST DOUBLE
, UNIT_PRICE DOUBLE
);
CREATE TYPE typ_sample_overview AS TABLE
( CHANNEL_ID INTEGER
, PROD_ID INTEGER
, UNIT_COST DOUBLE
, UNIT_PRICE DOUBLE
);
CREATE PROCEDURE PR_SAMPLE_OVERVIEW ( OUT P_COL_COSTS COL_COSTS )
LANGUAGE SQLSCRIPT
AS
BEGIN
  P_COL_COSTS = 
  SELECT 123 as CHANNEL_ID,
	     555 as PROD_ID,
	     7.5 as UNIT_COST,
         9.99 as UNIT_PRICE
    FROM DUMMY;
END;

CALL PR_SAMPLE_OVERVIEW( COL_COSTS ) WITH OVERVIEW;

SELECT * FROM col_costs;

and the result is:

Statement 'CREATE TABLE COL_COSTS ( CHANNEL_ID INTEGER , PROD_ID INTEGER , UNIT_COST DOUBLE , UNIT_PRICE ...' successfully executed in 40 ms 700 µs  - Rows Affected: 0 
Statement 'CREATE TYPE typ_sample_overview AS TABLE ( CHANNEL_ID INTEGER , PROD_ID INTEGER , UNIT_COST DOUBLE ...' successfully executed in 41 ms 490 µs  - Rows Affected: 0 
Statement 'CREATE PROCEDURE PR_SAMPLE_OVERVIEW ( OUT P_COL_COSTS COL_COSTS ) LANGUAGE SQLSCRIPT AS BEGIN ...' successfully executed in 362 ms 799 µs  - Rows Affected: 0 
Statement 'CALL PR_SAMPLE_OVERVIEW( COL_COSTS ) WITH OVERVIEW' successfully executed in 59 ms 837 µs 
Fetched 1 row(s) in 16 ms
Statement 'SELECT * FROM col_costs' successfully executed in 13 ms 152 µs 
Duration of 5 statements: 517 ms 
Fetched 1 row(s) in 0 ms

SELECT * FROM col_costs

CHANNEL_ID;PROD_ID;UNIT_COST;UNIT_PRICE
123;555;7.5;9.99

where the data is now in the table itself.

[Former Member · Accepted solution]

Hi Martin,

Were you able to call a procedure with a scalar output? I had this query, as the question is 'answered'.

Regards, Rahul

[former_member560074 · Accepted solution]

Hi Rahul,

for tables it works fins a described from the collagues if You have approprraite Rights for Schema system.

For Saclars obnly I cant figure it out.

Thank You

Martin

[AndyAnand · Accepted solution]

You could also pass in the table type while defining the procedure.. I think that was the original intent of defining that table type .. which creates the virtual structure to be used ..

CREATE PROCEDURE PR_SAMPLE_OVERVIEW ( OUT P_COL_COSTS typ_sample_overview)

LANGUAGE SQLSCRIPT

AS

.... ... .....