Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
saivina2920
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
PrashantSangle

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
santhiqlik
Creator
Creator

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.

saivina2920
Creator
Creator
Author

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

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.
saivina2920
Creator
Creator
Author

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

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.
saivina2920
Creator
Creator
Author

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

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