Question Re: Conversion character to integer in Technology Q&A

Conversion character to integer

Former Member — Thu, 04 Jul 2013 09:32:01 GMT

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

Re: Conversion character to integer

Former Member — Thu, 04 Jul 2013 09:57:14 GMT

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

Re: Conversion character to integer

Former Member — Thu, 04 Jul 2013 10:20:08 GMT

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

Re: Conversion character to integer

Former Member — Thu, 04 Jul 2013 10:42:02 GMT

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 ....

Re: Conversion character to integer

rindia — Thu, 04 Jul 2013 18:06:21 GMT

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

Re: Conversion character to integer

Former Member — Fri, 05 Jul 2013 07:26:41 GMT

Raj,

Thanks, i will try this on monday.

The big difference seems not to use any kind of implicit conversion function (like int or to_decimal).

I'll let you know what the result is 🙂

Grz,

Stefan

Re: Conversion character to integer

Former Member — Tue, 09 Jul 2013 07:06:18 GMT

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

Re: Conversion character to integer

Ravi_Channe — Tue, 09 Jul 2013 07:33:00 GMT

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

Re: Conversion character to integer

Former Member — Tue, 09 Jul 2013 09:15:19 GMT

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

Re: Conversion character to integer

Former Member — Tue, 09 Jul 2013 13:42:26 GMT

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

Re: Conversion character to integer

Former Member — Wed, 25 May 2016 05:57:57 GMT

This message was moderated.

Re: Conversion character to integer

Former Member — Thu, 09 Jun 2016 19:25:17 GMT

This message was moderated.

Re: Conversion character to integer

anindya_bose — Thu, 09 Jun 2016 19:49:42 GMT

This message was moderated.