Conversion character to integer

[Former Member]

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

[Former Member]

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

[Ravi_Channe]

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

[Former Member]

Hi,

Try debugging the script in SQLScript debugger in SAP HANA development perspective. I found that the scripts run properly in SAP HANA development perspective than in modeller perspective. I faced a similar issue while trying to run in SQL console.

Regards,

Rashmi

[Former Member]

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