cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with converting SQL query to HANA

Former Member
0 Kudos

Hi experts,

I'm new to SAP HANA database. I have to convert some  MS SQL queries to HANA and I could convert all the basic queries except one. This query has lot of SQL functions used and I don't know what are the functions I can use instead in HANA. I tried to use the provided SAP HANA - SQL converter but it didn't give me working query as an output. Below you can see the original SQL query and the output of the converter(which is wrong). Can some one help me to convert this query?  I'm just an intern

ORIGINAL SQL QUERY :


SELECT T0.[BirthDate], T0.[CardCode], T1.[CardName], T0.[Name], T0.[Tel1], T0.[E_MailL] FROM OCPR T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode WHERE 1 = (FLOOR(DATEDIFF(dd,T0.[BirthDate],GETDATE()+" + period + ") / 365.25)) - (FLOOR(DATEDIFF(dd,T0.[BirthDate],GETDATE()-" + period + ") / 365.25))

here "period" is an integer value.

HANA QUERY GOT FROM CONVERTER :


SELECT T0."BirthDate", T0."CardCode", T1."CardName", T0."Name", T0."Tel1", T0."E_MailL" FROM OCPR T0 INNER JOIN OCRD T1 ON T0."CardCode" = T1."CardCode" WHERE 1 = (FLOOR(DAYS_BETWEEN(T0."BirthDate", ADD_DAYS('1900-01-01 00:00:00.000', NOW() + " + period + ")) / 365.25)) - (FLOOR(DAYS_BETWEEN(T0."BirthDate", ADD_DAYS('1900-01-01 00:00:00.000', NOW() - " + period + ")) / 365.25));

ERROR:

invalid name of function or procedure: DATEDIFF

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Hello,

I'm trying to migrate an add-on solution (for SAP B-One) that uses t-sql queries, towards SAP Hana SQL ANSI. I'm using this converter (the dll file) to translate queries at run time :

https://store.sap.com/sap/cpa/ui/resources/store/html/SolutionDetails.html?pid=0000012995&catID=&pcn...

The queries of the application are still written in t-sql, the add-on application code (C#) calls the ConverterLib.dll that translates the queries from t-sql to ANSI SQL dynamically at runtime.

This scenario works correctly for the 32 bits version of the add-on solution. But it doesn't work for the 64 bits version of the add-on application, because the dll is only available in 32 bits version.

Is there any solution to this problem ? When will the 64 bits version be available ?

Best regards.

pedro_magueija
Active Contributor
0 Kudos

Hi Isuru,

There is a tool for that:


Best regards,

Pedro Magueija


View Pedro Magueija's profile on LinkedIn

Former Member
0 Kudos

Hi Pedro,

I used that converter as mentioned in the question but didn't help. Nevertheless I found a helpful documentation and hope I can use that to solve my problem.

Regards,

Isuru