I was trying to make a report on various application tables and I got
stymied by a couple of errors

Could not execute statement.
Syntax error near 'default' on line 3
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1

similar error on col.'compressed'


select a.table_name, a.table_id, a.creator, a."encrypted" ,
col.column_name, col.column_type , col.width, col.scale,
col.'default' , // <--what is wrong with this column?
//, col.'compressed' // <--what is wrong with this column?
col.nulls
from systab as a,
SYSTABCOL as col
// , syscolumns as scol

where a.table_type = 1
and a.table_name not in ('EXCLUDEOBJECT', 'jdbc_function_escapes',
'RowGenerated', 'tmpwk', 'dummy')
and a.table_name not like 'ISYS%' and a.table_name not like 'IX%' and
a.table_name not like 'migrate%'
and a.table_name not like 'r%' and a.table_name not like 'sa_%' and
a.table_name not like 'spt_%' and a.table_name not like 'pbc%'
and a.table_id = col.table_id
and col.column_id = scol.column_id
order by a.table_name, col.id

For some reason, copy in the OS the orginal SSA 10 dabase fiels to a new
location and and renamed the suffix before creating the new user OBDC
Dataset and connect form sql11, all previously dbo prefixed tabel have
become dba

Reserved keywords used as column names should be enclosed in double
quotes (as is the case with a."encrypted"). So it should read :

select a.table_name, a.table_id, a.creator, a."encrypted" ,
col.column_name, col.column_type , col.width, col.scale,
col."default" , // <--what is wrong with this column?
//, col."compressed"' // <--what is wrong with this column?
col.nulls
from systab as a,
SYSTABCOL as col
...

HTH
Reimer

gg wrote:

Thank you.

Must be late night, I missed the double quote and used single quote

Now I only need to improve on the domain_ID with something more legible like
real descriptive domain ID on the following successful query
select a.table_name, a.table_id, a.creator, a."encrypted"
, scol.pkey ,scol.column_name, scol.column_type, scol.domain_id ,
scol.width, scol.scale
, scol."default"
, col."compressed" // <--what is wrong with this column?
, scol."nulls"
, scol."remarks"
from systab as a,
sys.systabcol as col,
sys.syscolumn as scol
where a.table_type = 1
and a.table_name not in ('EXCLUDEOBJECT', 'jdbc_function_escapes',
'RowGenerated', 'tmpwk', 'dummy')
and a.table_name not like 'ISYS%' and a.table_name not like 'IX%' and
a.table_name not like 'migrate%'
and a.table_name not like 'r%' and a.table_name not like 'sa_%' and
a.table_name not like 'spt_%' and a.table_name not like 'pbc%'
and a.table_id = scol.table_id
and col.column_id = scol.column_id
and a.table_id = col.table_id
order by a.table_name, scol.column_id

I will be browing various sys tables looking for descripton and create store
procedure for final query