Skip to content
Advertisement

How to consider a long “integer” as a string in SQL query when useing R to connect HANA?

I am using R package RODBC to connect HANA. One of the columns is like a long integer, but actually used as a string such as 2772161413309, 4239530000000239. SQL takes such data as integers. In R, these numbers are shown in scientific notation like 2.77136e+12. my code that pick up some values from this column is:

> a <- sqlQuery(ch,paste(' SELECT "_tmSum"."/BIC/ZTMCARDNO"
                           FROM "SAPB1P"."/BIC/AZ_RT_A212" "_tmSum"
                           WHERE "_tmSum"."/BIC/ZTMCARDNO">0 AND "_tmSum"."CALDAY" BETWEEN',StartDate,'AND',EndDate,'  '))

I have the following error information:

[1] "S1000 339 [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;339 invalid number:  [6930] exception 6930:nims_search_api/Search/PartSearchImpl.cpp:427nattribute value is not a numbern"                                                                                                               
[2] "[RODBC] ERROR: Could not SQLExecDirect ' SELECT                        "_tmSum"."/BIC/ZTMCARDNO"n                       FROM "SAPB1P"."/BIC/AZ_RT_A212" "_tmSum"n    

Here, “_tmSum”.”/BIC/ZTMCARDNO” is the comlumn including long integers. I want to consider it to be string and pick up some values that is not 0.

Advertisement

Answer

I’m pretty sure that the problem here is not with the way R and SAP HANA handle floats. In both systems 2.77136e+12 is recognised correctly:

R:

> x <- 2.77136e+12
> x
[1] 2.77136e+12
> typeof(x)
[1] "double"

HANA:

select to_double('2.77136e+12') from dummy;

> 2771360000000

The error you receive, however, seems to come from the data processing within the "SAPB1P"."/BIC/AZ_RT_A212" view.

Could it be that in it some data type conversion is performed? Based on the name it looks like a BW table – often the BW data types are mapped to character type SQL data types. So, it might be worthwhile checking what data types are used in the model.

2 People found this is helpful
Advertisement