You can translate the question and the replies:

sequence in denodo

HI, Can i create a sequence in VQL or can i used nextval and increment a sequence in VQL. Regards Salman Khan
sequence
user
17-04-2018 04:57:14 -0400
code

4 Answers

Hi, At the moment, it is only possible to create the tables, views, stored procedures, synonyms of a database in the Virtual DataPort. Hence, I would use the "**ROWNUM()**" function in order to generate a number sequence by performing the following steps in Virtual DataPort Administration Tool: 1. Create a selection view on top of the base view by right-clicking the view and navigate to 'New > Selection' option. 1. Click on the 'Output' tab and select 'New field' option to add a new field. 1. Provide a name to the field in the 'field name' parameter and specify the "ROWNUM()" function in the 'field expression' parameter. 1. Then click on 'Save' to save the view. By executing the view, the "ROWNUM()" function returns unique number for each row. Additionally using "ROWNUM()" function, I can also generate a unique value for each query even if the data were retrieved from different data sources. For more information, you could have a look at the section [ROWNUM](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/other_functions#rownum) of Virtual DataPort VQL Guide. Hope this helps!!
Denodo Team
18-04-2018 01:51:28 -0400
code
Quetion is: will the result of above method be consistant in the sense that each row will always get the same id (sequence number) irrespective of how many times the view is queried?
user
07-04-2022 16:16:25 -0400
In addition, my view created via VQl contains group by clause. By adding the additional field rownum() named id, it can't run anymore, throwing #0 [JDBC ROUTE] [ERROR] Received exception with message 'ORA-00979: not a GROUP BY expression'
user
07-04-2022 16:22:20 -0400
Hi, The '**ORA-00979: not a GROUP BY expression**' is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. Hence to avoid this error, please ensure that all of the GROUP BY columns match the SELECT clause. You can refer to this [link](https://community.oracle.com/tech/welcome/discussion/4477130/ora-00979-not-a-group-by-expression-oracle-18c) for more information. Besides that, the **ROWNUM** function returns a unique number for each row of the result of a query. This number is unique for each query even if the data were retrieved from different sources. For consistent results between several executions of a query, you could use** ROWNUM()** over a view with the **ORDER BY** clause. You could refer to the section [ROWNUM](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/functions/other_functions/other_functions#rownum) of Virtual DataPort VQL Guide for more information. Hope this helps!!
Denodo Team
 Edited on: 20-04-2022 00:44:53 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here