on 07-04-2013 10:32 AM
Hi all,
I'm building a Calculation View based on SQL Scripting. Within this Calculation View I try to use the CE-functions as much as possble.
One of the challenges is the conversion of a character field (containing numbers) to something like an integer.
So, is there a way of doing that within a Calculation View?
Using conversion functions the validation is always okay, but running the view always leads to errors (mentioning that the data type for a specific function is wrong).
Any ideas?
Thanks!
Grz,
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Working with Rev. 56.
This is in fact the part of my script using the function to_int.
var_out = CE_PROJECTION(:USR,["ID",
"SALES_REP_ID__C",
CE_CALC('to_int("SALES_REP_ID__C")',integer) as "X"
]);
Little confused, becasue i think using the function to_int is the correct way. But now even the validation ends up whit the following error message:
Attribute 'to_int' is missing in node $$VAR_OUT$$.nnnSet Schema DDL statement: set schema "SALESFORCE"nType DDL
In all other cases wheneven I use something else than to_int the validation is alway okay, but the error message i'm getting when performing a Data Preview (validation is okay) is:
Error: SAP
DBTech JDBC: [339]: invalid number:
[6930] attribute value is not a number;Error executing physical plan:
attribute value is not a number;in executor::Executor in cube:
STEFANR:_SYS_CE_$REQUEST$_popid_2_51CA06E2D3271024E10000000A66BB6B_176: calcEngine search on olapIndex failed.
Guess the syntax is correct.
What i saw was that my original input column is not of the type varchar, but of type nvarchar. Would that make a difference?
Grz,
Stefan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Stefan,
The function is int(arg) and not to_int(arg). Please refer to Studio Help (Press F1) and search for "Functions used in Expressions". VARCHAR or NVARCHAR doesn't have any impact in this case.
Just out of curiosity, why do you need to convert it to Int. Do you wish to use it as a MEASURE ?
Regards,
Ravi
Ravi,
Using the 'int' function validating and activating is not a problem. But requesting the data using Data Preview will give me the erro I mentioned:
Error: SAP
DBTech JDBC: [339]: invalid number:
[6930] attribute value is not a number;Error executing physical plan:
attribute value is not a number;in executor::Executor in cube:
STEFANR:_SYS_CE_$REQUEST$_popid_2_51CA06E2D3271024E10000000A66BB6B_176: calcEngine search on olapIndex failed.
I't s a good curious question. The main reason is that data from several source systems will be combined in a view. For a particular item, all ID-fields are stored as an integer, which is fine with me. Not gonna use this field as a measure by the way. But one ID field from a source is stored as a character field. From a practical point of view, I just want to convert this exception to an integer field.
Obvious, we could change the type to integer directly when loading the data into HANA. Or another solution could be to change all the other ID-fields to a character type field.
Grz,
Stefan
Hi,
You can use CE_CALC function to convert the data type:
For Example:
var = CE_PROJECTION(:product, ["ID", "NAME", CE_CALC('int("<field_name>"', Integer) AS "new_field_name>" ]);
Regards,
Rashmi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
That's exactly how I tried it. Validation is okay.
But executing the view, it leads to an error saying that the attribute is of the wrong type. hence, <field_name> is a character type, but apparently it is not allowed. This is the message when performing a Data Preview and opening the tab Raw Data.
Error: SAP DBTech JDBC: [339]: invalid number: [6930] attribute value is not a number;Error executing physical plan: attribute value is not a number;in executor::Executor in cube ....
Hi Stefan,
Here is the approach i followed (HANA revision 46).
I created a table with column YEAR with data type NVARCHAR which has numeric value.
In CE_PROJECTION I included this YEAR field and using CE_CALC function I converted NVARCHAR YEAR column to INTEGER YEAR1 attribute.
In the output, I not included the YEAR but included YEAR1. In the script always code the column in CE_PROJECTION which you use in CE_CALC.
This is the script:
/********* Begin Procedure Script ************/
BEGIN
T_CE_FUN = CE_COLUMN_TABLE(CE_FUN);
var_out = CE_PROJECTION(:T_CE_FUN, [PRODUCT_ID
, PRODUCT_NAME
, SALES
, "YEAR"
, CE_CALC( '("YEAR")', integer) AS "YEAR1"
]);
END /********* End Procedure Script ************/
The below screenshot shows the table definition, data, sqlscript, and output of Calculation view.
Regards
Raj
Hi Rijinkels,
Could you provide error evidence for detail information, besides, whether you use to_int function to do convert character to int?
Regards,
Jerry
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
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.