Skip to Content
0
Former Member
Nov 10, 2014 at 07:40 PM

Supplying calc view parameter via a procedure

25 Views

We have a calc view that takes a date as an input parameter. I would like to call this view from a procedure where the date is supplied as a parameter to the procedure, but I'm encountering problems doing so since I can't seem to find an example of a procedure supplying non-constant value as a parameter to a view. How should I modify the syntax?

Example of what I'm trying to do:

create procedure BULKLOAD.TEST_MATERIALIZED_ACCT_METRICS( IN p_InputDate DATE ) language SQLSCRIPT as

begin

SELECT CUST7_NAME, REVENUE_YTD

FROM "_SYS_BIC"."customer_reporting.models/CA_ACCOUNT_METRICS"

GROUP BY CUST7_NAME, REVENUE_YTD

WITH PARAMETERS ('PLACEHOLDER' = ('$$INPUT_DATE$$', :p_InputDate )) -- doesn't work

-- WITH PARAMETERS ('PLACEHOLDER' = ('$$INPUT_DATE$$', '2014-11-01')) -- this works

;

end;

Here's the result when I try to run that:

Could not execute 'create procedure BULKLOAD.TEST_MATERIALIZED_ACCT_METRICS( IN p_InputDate DATE ) language SQLSCRIPT ...' in 9 ms 36 µs .

SAP DBTech JDBC: [257] (at 298): sql syntax error: incorrect syntax near ":p_InputDate": line 6 col 55 (at pos 298)