pass column in select query through input parameter logic supported in READ ONLY procedure/function

[Former Member]

Dear Experts,

The below procedure takes column name(s) in input and select column(s) that user entered from table also pass them in group by clause

and at the end generate final result set always in two columns.

-----------------

Example: Procedure Snippet

CREATE PROCEDURE "SCHEMA_NAME"."PROC_TEMP"(IN COL VARCHAR(100), OUT TAB "SCHEMA_NAME"."TABLE_TYPE1" )

LANGUAGE SQLSCRIPT

AS

BEGIN

declare COL1 varchar(100);

create local temporary table #temp (QUANTITY INTEGER, CONFIG VARCHAR(100));

COL1 = replace(:col,',','||'' ; '' ||');

EXECUTE IMMEDIATE 'insert into #temp (QUANTITY , CONFIG)

select QUANTITY, ' || :COL1 || ' as CONFIG from (select

sum(QUANTITY) as "QUANTITY",

'||:COL||'

from "SCHEMA_NAME"."TABLE1"

group by '||:COL||'

order by QUANTITY desc)';

TAB = select QUANTITY, CONFIG from #temp;

END;

----------------------

Sample Table: TABLE1

QUANTITY COLUMN_A COLUMN_B COLUMN_C

50 CA1 CB1 CC2

20 CA2 CB1 CC1

25 CA1 CB2 CC2

45 CA1 CB1 CC1

------------------------

CALL "SCHEMA_NAME"."PROC_TEMP"('COLUMN_A,COLUMN_C',?);

Procedure output:

QUANTITY CONFIG

75 CA1;CC2

45 CA1;CC1

20 CA2;CC1

-------------------------

Now I wanted to incorporate above procedure in calculation view and take it further to reporting layer however as read-write procedure and Dynamic SQL is not supported in READ ONLY procedure/function therefore I am not able to call above procedure in Table Function / SQL Script calculation view.

So Is there any other way to achieve above functionality in READ ONLY procedure/function/SQL Script calculation view?

If not then how should one pass required column(s) dynamically in select query from reporting layer and get desired aggregated data at run time?

Thanks and Regards,
Danish Abdullah

[former_member208449]

Hi Danish

It's quite late, but if you haven't found a solution yet, then the only way through I see is to call this procedure inside an XSJS. Expose this XSJS with parameters as a service for the reporting layer.