Retrieving Data With SQL › Getting the Exact Column Names for an SQL Table

Getting the Exact Column Names for an SQL Table

If you do not know the exact column names in the INV.STOCKS table, try running the following simple CA Culprit report.

col. 2
▼
PRO  EX=NO
IN  DB(Q)  DICTIONARY=TSTDICT   SCHEMA=INV
SQL SELECT * FROM STOCKS;
0151*001 ' '

The EX=NO tells CA Culprit to simply compile the report. No data extraction will be performed.

SQL SELECT * FROM STOCKS tells CA Culprit to describe all of the columns and null indicators for the STOCKS table. The generated REC cards will appear on the INPUT PARAMETER LISTING.

Generated REC Cards

********
REC       START    SIZE    TYPE   DP        FIELD-NAME
********
 REC      0100     00005     3    3   CLOSE_PRICE
 REC      0105     00004     1        CLOSE_PRICE_NULL_IND
 REC      0069     00005              COMPANY_ID
 REC      0074     00004     1        COMPANY_ID_NULL_IND
 REC      0049     00020              COMPANY_NAME
 REC      0078     00010              TRADING_DATE
 REC      0088     00004     1        TRADING_DATE_NULL_IND
 REC      0092     00004     1        VOLUME
 REC      0096     00004     1        VOLUME_NULL_IND

From this report, you can deduce that the STOCKS table was created using the following SQL command:

SQL Command

CREATE TABLE INV.STOCKS
(COMPANY_NAME  CHAR(20) NOT NULL,
 COMPANY_ID    CHAR(5),
 TRADING_DATE  DATE,
 VOLUME        INTEGER,
 CLOSE_PRICE   DECIMAL(9,3));