Skip to Content
0
Former Member
Jan 16, 2017 at 06:36 PM

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

729 Views

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