I am trying to build sql queries for CXX and my DIR_COLUMNS table has < 1,200 rows in it, anyone have a query to list all columns for a table ?

Mark,

The Dynamic System Tables do not always contain the information for all columns of a table as indicated in this snippet from the Dynamic System Tables Reference in our documentation:

DIR_COLUMN (DRC)

Dale, thanks, that confirms what I read in the manual, only contains column details that are nullable or have DBEDITS.

However, I have AQT (Advanced Query Tool) which connects to Datacom (and other DBMS') via ODBC and it seems to have no issues extracting column information  - see attached snippet.  The nullable columns are in DIR_COLUMNS as expected.

So it is available somewhere...

Hi Mark,

The ODBC calls get the table and column definitions out of Datadictionary.

You can run following SQL query to list the columns in a table :

 SELECT DISTINCT(ENTITY_NAME) , DISP_IN_TABLE , TYPE , CLASS , SIGN , LENGTH,
 JUSTIFICATION FROM SYSADM.FIELD
 WHERE STATUS = 'P' AND ENTITY_TYPE = 'FLD' AND ENABLE = 'Y'
AND AUTHID = 'your AUTHID' AND AGR_SQLNAME = 'your SQL table name'
ORDER BY DISP_IN_TABLE

Thanks Karina,

That may be better than what I had developed, mine was relying on the longest element being the latest table definition:

I will post it anyway in case it is useful to someone.

Select fld.AUTHID, fld.RECORD_NAME AS TABLE_NAME, fld.ELM_NAME
  , fld.ENTITY_NAME, fld.DISP_ELEMENT
     , CASE fld.TYPE
  when 'C' then 'Character'
  when 'N' then 'Numeric'
  when 'D' then 'Decimal'
  else fld.TYPE end
  , fld.NULL_INDICATOR, fld.CLASS
  , fld.SIGN, fld.LENGTH, fld.DECIMALS
     , fld.PRECISION, fld.REDEFINES, fld.SQLNAME
     , fld.REPEAT
     , fld.VALUE , fld.ENTITY_VER
  , fld.AGR_SQLNAME as TBL_SQLNAME
  , fld.DATE_LAST_CHG, fld.TIME_LAST_CHG
     , fld.DESCRIPTION, fld.DATACOM_NAME AS ELM_NAM
  , fld.SEQNO
from SYSADM.FIELD fld
where fld.STATUS = 'P'
and fld.ENTITY_TYPE = 'FLD'
and fld.RECORD_NAME = 'ITEM-ADJUSTMENT-HISTORY'
and fld.ENTITY_NAME NOT IN ('START','END')
and fld.DATACOM_NAME > ''
and fld.ELM_NAME = (SELECT EL.ENTITY_NAME from SYSADM.ELEMENT EL
    where el.status = 'P' and el.RECORD_NAME = fld.RECORD_NAME and el.length =
    (SELECT max(el2.length) from SYSADM.ELEMENT EL2
    where el2.status = 'P' and el2.RECORD_NAME = fld.RECORD_NAME))
-- this is to list in column name order

--order by RECORD_NAME, ENTITY_NAME, ENTITY_VER, DATE_LAST_CHG, TIME_LAST_CHG
-- this is to list in column sequence order
order by RECORD_NAME, ELM_NAME, SEQNO, ENTITY_NAME, ENTITY_VER, DATE_LAST_CHG, TIME_LAST_CHG