topic Re: 'nvl' is not a recognized built-in function name in QlikView

'nvl' is not a recognized built-in function name

saivina2920 — Sat, 16 Nov 2024 21:09:28 GMT

I'm trying to convert date along with handling null value. This query written in Oracle.

Same i want to use in Qlik Script. But, i am getting Error.

Script :

JOININGDATE:
select
CAST(TO_DATE(nvl(JOINDATE,''),'DD/MM/YYYY HH24:MI:SS') AS TIMESTAMP) AS JOIN_DATE
FROM Emp.dbo."Employee"

ERROR :

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'nvl' is not a recognized built-in function name.
JOININGDATE:
select
CAST(TO_DATE(nvl(JOINDATE,''),'DD/MM/YYYY HH24:MI:SS') AS TIMESTAMP) AS JOIN_DATE
FROM Emp.dbo."Employee"

Re: 'nvl' is not a recognized built-in function name

santhiqlik — Fri, 29 Mar 2019 12:13:50 GMT

Hi,

Your error description says - 'nvl' is not a recognized built-in function name in SQL Server. So you need to use "ISNULL()" which is built-in function to handle nulls in SQL Server.

Re: 'nvl' is not a recognized built-in function name

saivina2920 — Fri, 29 Mar 2019 12:17:37 GMT

I used ISNULL. But, i am getting another error.
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'TO_DATE' is not a recognized built-in function name.
below my script.
select
CAST(TO_DATE(nvl(JOINDATE,''),'DD/MM/YYYY HH24:MISmiley FrustratedS') AS TIMESTAMP) AS JOIN_DATE
FROM Emp.dbo."Employee"

Re: 'nvl' is not a recognized built-in function name

PrashantSangle — Fri, 29 Mar 2019 12:33:06 GMT

is that select query running properly in sql server??
1st test your sql statement in sql server then use it in qlik.

Re: 'nvl' is not a recognized built-in function name

saivina2920 — Fri, 29 Mar 2019 12:36:27 GMT

Yes. I first tested in Oracle SQL Developer then i used in Qlik Script page.
In Oracle, it's executing successfully. But, In Qlikview only the problem.
ErrorMsg: 'TO_DATE' is not a recognized built-in function name.

Re: 'nvl' is not a recognized built-in function name

pradosh_thakur — Fri, 29 Mar 2019 12:42:30 GMT

use sql before select

Script :

JOININGDATE:
sql select
CAST(TO_DATE(nvl(JOINDATE,''),'DD/MM/YYYY HH24:MIS') AS TIMESTAMP) AS JOIN_DATE

Re: 'nvl' is not a recognized built-in function name

saivina2920 — Fri, 29 Mar 2019 12:49:35 GMT

Same Error.
"TO_DATE" ==> It is not my Column name. It is one date function name.
My column name is ==> JOINDATE
I think "TO_DATE" Function might not be available in Qlikview.
Am i right...?

Re: 'nvl' is not a recognized built-in function name

PrashantSangle — Sun, 31 Mar 2019 04:14:05 GMT

nvl () and to_date() are Oracle sql support function. If you want to run same query in sql server then use isnull () and convert ().
Isnull () work similar as nvl() and convert() work similar as to_date().
For convert() syntax navigate below URL
https://stackoverflow.com/questions/45074493/use-to-date-in-sql-server-2012.

Regards,
Prashant Sangle