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]

This message was moderated.

[anindya_bose]

This message was moderated.

[Former Member]

This message was moderated.

[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

[former_member184768]

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

[Former Member]

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

[Former Member]

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

[rindia]

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

[Former Member]

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

[Former Member]

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