0

I'm JPablos and I trying to view the structure of "orders" table.

I'm using Base

LibreOffice Versión: 5.2.0.4 Id. de compilación: 1:5.2.0~rc4-0ubuntu1~xenial2 Subprocesos de CPU: 1; Versión de SO: Linux 4.4

SQL statement

select listagg(column_name ||','|| data_type ||','||   case
 when data_type in ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'RAW')
   then to_char(data_length)
 when data_type = 'NUMBER' and (data_precision is not null or data_scale is not null)
   then data_precision || case
 when data_scale > 0 then '.' || data_scale
 end
end, ',') within group (order by column_id) 
from all_tab_columns where table_name = 'orders';

Then SQL informs me

1: Access is denied: LISTAGG in statement [select listagg(]

Note: obviously... the easy way in Base UI: select "orders" / right click / Edit, and yes it opens the structure of table "orders". But, I want to use SQL to do it.

Thanks in advance

JPablos

ɐlǝx
  • 1,384
  • 2
  • 17
  • 22
JPablos
  • 1
  • 3

2 Answers2

0

The SQL statement is written for the Oracle database. The LISTAGG function is not supported by HSQLDB.

If you use LibreOffice base together with the latest HSQLDB 2.3.4 (instead of the bundled version 1.8.0) then you can use the HSQLDB function GROUP_CONCAT.

fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks @fredt. I am learning to use SQL. The SQL statement used was a copy of (http://stackoverflow.com/questions/39022808/retrieving-table-structure-with-dynamic-sql). Now how can I use codeGROUP_CONCAT/code ? – JPablos Aug 30 '16 at 10:08
  • You need to set up the HSQLDB 2.3.4 jar and learn how to use different functions from the HSQLDB docs. http://hsqldb.org/doc/2.0/guide/index.html – fredt Aug 30 '16 at 10:54
0

after all it is a SQL statement to do the query object of my question above, and is:

SELECT * FROM "INFORMATION_SCHEMA"."SYSTEM_COLUMNS" WHERE "TABLE_NAME" = 'Students'

Where "Students" is the name of a table used for this answer.

The SQL statement reports:

Result of the query

Best regards

JPablos

JPablos
  • 1
  • 3