New registrations alert

[former_member691564]

Hi guys!!
I adapted a query to create an alert for item registration. However, the "CONVERT" instruction is returning an error.

ERROR:
1). [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;328 invalid name of function or procedure: CONVERT: line 8 col 29 '' (SWEI) (at pos 112)

I need help to solve this problem!

SELECT
t0."ItemCode",
t0."ItemName",
t0."CreateDate",
T1."USER_CODE",
T1."U_NAME",

CASE WHEN t0."CreateDate" = CONVERT (date,getdate()) THEN 'SIM' ELSE 'NAO' end as "Incluido"
FROM OITM t0
LEFT JOIN OUSR T1 ON T1."USERID" = T0."UserSign"
where 
CASE WHEN t0."CreateDate" = CONVERT (date,getdate()) THEN 'SIM' ELSE 'NAO' end = 'SIM'

Thank you very much in advance!!!

[pfefferf · Accepted solution]

CONVERT is not a valid HANA function. I guess you mean something like

CAST or the TO_VARCHAR (with a date format) to convert the date value to a value which can be compared to your CreateDate column. Maybe you want to add what type and format your CreateDate column has, then probably a more detailed answer can be given.

[former_member691564 · Accepted solution]

Thank you so much for your help! In fact after placing TO_VARCHAR it was possible to perform.

But now I'm having to set a specific date. I need the consultation to bring me daily creations.
What parameter could you use in TO_VARCHAR for this?

SELECT
t0."ItemCode",
t0."ItemName",
t0."CreateDate",
T1."USER_CODE",
T1."U_NAME",

CASE WHEN t0."CreateDate" = TO_VARCHAR (TO_DATE('2020-01-05'), 'YYYY/MM/DD') THEN 'SIM' ELSE 'NAO' end as "Incluido"
FROM OITM t0
LEFT JOIN OUSR T1 ON T1."USERID" = T0."UserSign"
where 
CASE WHEN t0."CreateDate" = TO_VARCHAR (TO_DATE('2020-01-05'), 'YYYY/MM/DD') THEN 'SIM' ELSE 'NAO' end = 'SIM'    

That way the query is static, and I need it to be updated day by day without me having to change it.

Thanks in advance again!

[pfefferf · Accepted solution]

Use CURRENT_DATE instead of a fix date.

TO_VARCHAR(CURRENT_DATE, 'YYYY/MM/DD')

[former_member691564 · Accepted solution]

Thanks a lot for the help. The query was processed successfully!

[jhozz]

Hi, CONVERT function is not allowed on HANA environments.

You may try with this:
1-Remove this: t0."CreateDate" = CONVERT (date,getdate())

2- And use this: t0."CreateDate" = CURRENT_DATE

CURRENT_DATE function is allowed by HANA.

Regards/Saludos.

[former_member691564]

Thanks a lot for the help. The query was processed successfully!