on 05-07-2012 7:33 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.