You can translate the question and the replies:

How to use "WITH" clause query using temporary tables to create a view in DENODO

I am trying to create a view using the query which is having WITH clause for temporary table but getting syntax error.. please help me asap. ex: WITH dept_count AS ( SELECT deptno, COUNT(*) AS dept_count,ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY debt_date desc) AS ROW_NUM, FROM emp GROUP BY deptno) SELECT e.ename AS employee_name, dc.dept_count AS emp_dept_count FROM emp e, dept_count dc WHERE e.deptno = dc.deptno and row_num = 1;
user
01-11-2016 03:51:17 -0400

3 Answers

Hi! I’ve been taken a look at your query and there is an extra comma after *“… AS ROW_NUM”*. In addition, the *ROW_NUMBER* function is implemented as *ROWNUM* in the Denodo Platform and it doesn’t currently support *OVER* clause. I’d recommend you to check its syntax in the ROWNUM section of the [Advanced VQL Guide](https://community.denodo.com/docs/download/document/Virtual%20DataPort/Denodo%206.0/Advanced%20VQL%20Guide). In this document you can also find all the information regarding VQL language syntax and functions. Hope this helps!
Denodo Team
03-11-2016 08:57:57 -0400
**How to use ROWNUM in condition clause **
user
 Edited on: 14-05-2019 02:28:55 -0400
Hi! As I can confirm in the [Advanced VQL Guide](https://community.denodo.com/docs/download/document/Virtual%20DataPort/Denodo%206.0/Advanced%20VQL%20Guide), *Rownum* function returns a unique number for each row of the result of a query. Therefore, it can only be used in the select clause of a query. Hope this helps!
Denodo Team
23-02-2017 11:58:26 -0500
You must sign in to add an answer. If you do not have an account, you can register here