Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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"

Labels (2)
1 Solution

Accepted Solutions
Highlighted

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

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
Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post

7 Replies
Creator
Creator

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

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.

Highlighted
Creator
Creator

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

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"
Highlighted

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

is that select query running properly in sql server??
1st test your sql statement in sql server then use it in qlik.
Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
Creator
Creator

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

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.
Highlighted
Master II
Master II

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

use sql before select 

 

Script : 

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

Learning never stops.
Highlighted
Creator
Creator

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

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...?
Highlighted

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

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
Great dreamer's dreams never fulfilled, they are always transcended.

View solution in original post