cancel
Showing results for 
Search instead for 
Did you mean: 

calculation view using sql script

Former Member
0 Kudos

Hi Experts

I am trying to create a simple calc view using a statement like

var_out = select ID, FNAME, SALARY from schemax.customer_table;

I define ID, FNAME , SALARY as output parameters  and add fname as attribute and salary as measure .

but i am having errors.

can any one tell me how to create a simple calc view using script , when it is as simple as pulling a few columns from a table

-Beth

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Elizabeth,

If you are creating Calc views, you need to keep in mind the order your select statement and output parameters should match along with the data type.

Here is a syntax for a simple script based calc view,

BEGIN 

VAR_OUT =  SELECT  "ID" AS "COL_OUT1",
                     "FNAME" AS "COL_OUT2",

                     SUM("SALARY") AS "COL_OUT3"

                    FROM schemax.customer_table

                    GROUP BY "ID", "FNAME"; 

END

When you are defining your output, make sure to use the same data type - see image.

Regards,

Khadar

Former Member
0 Kudos

Thanks Khadar .. but I had been doing the same stuff like yours but getting error.. just for kicks i just duplicated what you did but as before I get this error .

MDXRuntime: Internal deployment of object failed: Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "CUSTOMER_TABLE": line 4 col 84 (at pos 331)nSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."beth/CALC_TESTV/proc/tabletype/VAR_OUT" as table ("C1" INTEGER, "C2" VARCHAR(10), "C3" INTEGER)nProcedure DDL: create procedure "_SYS_BIC"."beth/CALC_TESTV/proc" ( OUT var_out "_SYS_BIC"."beth/CALC_TESTV/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t var_out = SELECT ID as C1, FNAME as C2, sum(SALARY)as C3 FROM SCHEMAX."SCHEMAX"."CUSTOMER_TABLE"n t group by IDnnEND /********* End Procedure Script ************/nVersion: 60n

rajarshi_muhuri
Active Participant
0 Kudos

duplicated but you must have left out the ; (semi colon) at the end of the statement .

or did not add FNAME to the GROUP BY statement.

And going by your error you have left out both FNAME and ;

BEGIN n t var_out = SELECT ID as C1, FNAME as C2, sum(SALARY)as C3 FROM SCHEMAX."SCHEMAX"."CUSTOMER_TABLE"n t group by IDnnEND

Answers (0)