#14848: Add a "Select Top 1000 Rows" action to Table/View popup menu in schema tree

NielsGron reported 2016-11-30T04:08:23Z · Diksha.kushwah(*) last modified 2017-01-05T11:10:26Z

Add a "Select Top 1000 Rows" action to Table/View popup menu in schema tree

Dev	ivangron
QA	SachinPrakash

Priority	Low
Complexity	Unknown

Component	App - Schema Browser
Version	18.0

Add a "Select Top 1000 Rows" action to Table/View popup menu in schema tree. Attached are examples of other tool.

We should also use the options set in File->Options->Table Data Editor->[TOP default size]

2 attachments

actionresults.png

2016-11-30T04:08:23Z

103 KB
mssqlserverMactionmenu.png

2016-11-30T04:08:23Z

34 KB

All Comments (8) Change History

NielsGron 2016-11-30T04:12:03Z

See usage of EditViewData/EditTableData.getTitleString().

ivangron 2016-12-05T20:08:22Z

The NROWS value is determined by the File->Options->Table Data Editor->[TOP default size]. Do we want to continue to use this setting or privode a new option?

MYSQL, PostreSQL, VoltDB, RedShift, Greenplum, ParAccel, Cassandra, Informix, BigQuery, Vertica, Netezza, NCluster, Hive, MongoDB & SQLite will use the [ LIMIT {#} ] syntax to limit the rows returned, where {#} equals the [TOP default size]

SybaseASE, Sybase Anywhere, Sybase IQ, SQL Server, SQL Azure, Excel, SAP Hana & Teradata will use the [ TOP {#} ] syntax to limit the rows returned, where {#} equals the [TOP default size]

DB2, DB2 iSeries & DB2 z/OS will use the [ FETCH FIRST {#} ROWS ONLY ] syntax to limit the rows returned, where {#} equals the [TOP default size]

Oracle will provide two different syntaxs depending on the version of oracle. Versions older then 12c will use the [ WHERE ROWNUM <= {#} ] syntax. Versions 12c and newer will use the [ OFFSET 0 ROWS FETCH NEXT {0} ROWS ONLY ] syntax, where {#} equals the [TOP default size]

Apache Derby will only provide this action for versions 10.4 and greater and the syntax supported will use the ROW_NUMBER() OVER() syntax to create a subquery and limit the results by rownum. Example: SELECT * FROM (SELECT ROW_NUMBER() OVER() AS rownum, <table>.* FROM <table>) AS <table> WHERE rownum <= {#}

ScriptView & ScriptTable both have new methods scriptTOPNROWS() that will provide the required syntax.

** I have currently reused the SELECT(*) action icon, we still need to create a new icon for this action.

A new query window will open with the query. I have tested this for all the databases listed above. I have not tested for all versions of every database, so we will need to do more testing through all the versions. Currently the statement syntax generation is done and it will open a query window. The query window requires initialization before it can execute. I will check in code but still need to work on querypanel to fire query once initialization has completed.

** Work required to have querypanel execute query once connection initialization is complete.

Committed revision: 52114

The NROWS value is determined by the File->Options->Table Data Editor->[TOP default size]. Do we want to continue to use this setting or privode a new option?

SybaseASE, Sybase Anywhere, Sybase IQ, SQL Server, SQL Azure, Excel, SAP Hana & Teradata will use the [ TOP {#} ] syntax to limit the rows returned, where {#} equals the [TOP default size]

DB2, DB2 iSeries & DB2 z/OS will use the [ FETCH FIRST {#} ROWS ONLY ] syntax to limit the rows returned, where {#} equals the [TOP default size]

ScriptView & ScriptTable both have new methods scriptTOPNROWS() that will provide the required syntax.

** I have currently reused the SELECT(*) action icon, we still need to create a new icon for this action.

** Work required to have querypanel execute query once connection initialization is complete.

Committed revision: 52114

ivangron 2016-12-09T20:38:03Z

As per verbal request:

The open/close object qualifiers will use the specified qualifiers set in File->Options->Scripts->[<database>]->Quote Identifier-> [<value>]. This will give the user control on how the table or view is qualified.

Commit Revision : 52194

ivangron 2016-12-09T21:50:56Z · Edited by

SachinPrakash

I have not marked this issue resolved because we are not auto executing the query. Query panel is threaded and it must initiate the connection before the query execution can occur. If this is no longer a requirement then the issue can be resolved.

[SP] This is not required. Marking issue as resolved

SachinPrakash 2016-12-09T21:53:25Z

@qa: as explained here, you should test this by generating the SELECT for every DB & version that you have access to. The SELECT statement will not be automatically executed as explained here. When you right click on a table & see the SELECT Top xxx rows, the "xxx" is determined based off the File > Options > Table Data Editor > TOP default size setting

Diksha.kushwah(*) 2016-12-19T09:03:47Z

For Informix 11.5, Limit clause is not supported.It is supported for 12.1 and above versions but script is generated successfully like version 12.

Is it expected or we should disable the "Select xxxTop rows" option?

ivangron 2016-12-19T19:08:12Z

Informix will now use the LIMIT # for versions 12.1 and above and the FIRST # clause for versions below 12.1

Committed revision: 52339

Diksha.kushwah(*) 2016-12-22T04:23:38Z

I have tested it for listed databases in ADS 18-rc-2.I did not test on SQL Server 2000 & Volt DB 5.5 due to lack of environment availability.

Search Tips

Aqua Data Studio / nhilam

Add a "Select Top 1000 Rows" action to Table/View popup menu in schema tree

2 attachments

Issue #14848

Completion