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]

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