CODEWITHSUNDEEP
sqlrtype-conversionhanarodbc
Feng Chen
Feng Chen

Reputation: 2253

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:427\nattribute value is not a number\n"                                                                                                               
[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.

Upvotes: 0

Views: 912

Answers (2)

Lars Br.
Lars Br.

Reputation: 10388

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.

Upvotes: 1

Feng Chen
Feng Chen

Reputation: 2253

I think I have a misunderstanding about SQL. When the integer is really long, like 13 or more digits. SQL will consider it as a string. Those integers shows in R in scientific notation way because R considers them as integers. So, in SQL query, just using character related methods to deal with them is OK.

Upvotes: 0

Related Questions