oa_columns

This is a system table that defines the column name, type, length, and description for all columns in all tables available in the SuiteAnalytics Connect schema.

Table name: oa_columns
table_qualifier	Name of the table qualifier
table_owner	Name of the table owner
table_name	Name of the table
column_name	Name of the column
data_type	The column’s SQL data type
type_name	Data source dependent data type
oa_length	The length in bytes of data transferred for Fetch, GetData, etc.
oa_precision	The precision of the column on the data source
oa_radix	Radix of data type, NULL for data types where radix does not apply
oa_scale	Number of digits to the right of the decimal point that are significant. If you use the NetSuite2.com data source, consider the following: The value 0 indicates that there are no significant digits. The value -127 indicates that the number of significant digits is unknown. If you use the NetSuite.com data source, consider the following: The value 0 indicates that there are no significant digits or that the number of significant digits is unknown. Note: The NetSuite.com data source is no longer being updated with newly exposed tables and columns, and support for this data source will end in a future release. The use of the NetSuite.com data source is no longer considered a best practice, and all Connect users are encouraged to use the NetSuite2.com data source. For more information about this change, see New Accounts and Access to the Connect Data Source.
oa_nullable	not supported
oa_scope	not supported
oa_userdata	Shows several values in 16 positions that describe information such as the feature required to access this data and relevant content about this column that you should consider. To view a detailed description of this column, see oa_userdata in oa_columns. Note: This column applies the NetSuite2.com data source only.
oa_support	not supported
pseudo_column	not supported
oa_columntype	not supported
remarks	Column description

Table name: oa_columns

Column Name

Description

table_qualifier

Name of the table qualifier

table_owner

Name of the table owner

table_name

Name of the table

column_name

Name of the column

data_type

The column’s SQL data type

type_name

Data source dependent data type

oa_length

The length in bytes of data transferred for Fetch, GetData, etc.

oa_precision

The precision of the column on the data source

oa_radix

Radix of data type, NULL for data types where radix does not apply

oa_scale

Number of digits to the right of the decimal point that are significant.

If you use the NetSuite2.com data source, consider the following:

The value 0 indicates that there are no significant digits.
The value -127 indicates that the number of significant digits is unknown.

If you use the NetSuite.com data source, consider the following:

The value 0 indicates that there are no significant digits or that the number of significant digits is unknown.

Note:

The NetSuite.com data source is no longer being updated with newly exposed tables and columns, and support for this data source will end in a future release. The use of the NetSuite.com data source is no longer considered a best practice, and all Connect users are encouraged to use the NetSuite2.com data source. For more information about this change, see New Accounts and Access to the Connect Data Source.

oa_nullable

not supported

oa_scope

not supported

oa_userdata

Shows several values in 16 positions that describe information such as the feature required to access this data and relevant content about this column that you should consider. To view a detailed description of this column, see oa_userdata in oa_columns.

Note:

This column applies the NetSuite2.com data source only.

oa_support

not supported

pseudo_column

not supported

oa_columntype

not supported

remarks

Column description

oa_userdata in oa_columns

Note:

This column applies the NetSuite2.com data source only.

Position	Valid Values	Valid Description
6	C or hyphen (-)	C means that this column is a calculated field. A hyphen (-) means that this column is not a calculated field.
1	C or S	C means that this is a custom column. S means that this is a standard column.
5	D or hyphen (-)	D means that this column is a display field. A hyphen (-) means that this column is not a display field.
3	M or hyphen (-)	M means that this column corresponds to the Last Modified Date column. A hyphen (-) means that this column is not the Last Modified Date column.
18	Name of a NetSuite feature	Shows the feature required for accessing this column data.
4	Not applicable	Not applicable
7-16	Not applicable	Not applicable
17	pipe (\|)	The pipe (\|) symbol is used as a delimiter between values.
2	V or H	V means that this is a visible column. H means that this is a hidden column.

Position

Valid Values

Valid Description

C or hyphen (-)

C means that this column is a calculated field.

A hyphen (-) means that this column is not a calculated field.

C or S

C means that this is a custom column.

S means that this is a standard column.

D or hyphen (-)

D means that this column is a display field.

A hyphen (-) means that this column is not a display field.

M or hyphen (-)

M means that this column corresponds to the Last Modified Date column.

A hyphen (-) means that this column is not the Last Modified Date column.

Name of a NetSuite feature

Shows the feature required for accessing this column data.

Not applicable

7-16

Not applicable

pipe (|)

The pipe (|) symbol is used as a delimiter between values.

V or H

V means that this is a visible column.

H means that this is a hidden column.

Example Queries

To find all columns in a specific table, use the following query:

              select * from oa_columns where table_name = 'TABLE_NAME';

For example, if you search for all columns in the departments table, the output may include the following rows:

table_name	column_name	type_name	oa_length	oa_precision	oa_scale
DEPARTMENTS	DATE_LAST_MODIFIED	TIMESTAMP	0	0	0
DEPARTMENTS	DEPARTMENT_EXTID	VARCHAR2	255	255	—
DEPARTMENTS	DEPARTMENT_ID	NUMBER	8	22	0
DEPARTMENTS	FULL_NAME	VARCHAR2	1791	1791	—
DEPARTMENTS	ISINACTIVE	VARCHAR2	3	3	—
DEPARTMENTS	NAME	VARCHAR2	31	31	—
DEPARTMENTS	PARENT_ID	NUMBER	8	22	0

To find all tables that include a specific column, use the following query:

              select * from oa_columns where column_name = 'COLUMN_NAME';

For example, if you search for all tables that include the subsidiary_id column, the output result may include the following rows:

table_name	column_name	type_name	oa_length	oa_precision
CUSTOMERS	SUBSIDIARY_ID	NUMBER	8	22
SUBSIDIARIES	SUBSIDIARY_ID	NUMBER	8	22
EMPLOYEES	SUBSIDIARY_ID	NUMBER	8	22
SUBSIDIARY_CLASS_MAP	SUBSIDIARY_ID	NUMBER	8	22
SUBSIDIARY_LOCATION_MAP	SUBSIDIARY_ID	NUMBER	8	22

To see the available column descriptions for columns in a specific table, use the following query:

              select table_name, column_name, remarks from oa_columns where table_name = 'TABLE_NAME' AND remarks !='';

For example, if you try to find the descriptions for columns in the vendors table, the output result may include the following rows:

table_name	column_name	remarks
VENDORS	INDUSTRY_2_ID	Select the industry that best describes the business of the individual or company. You can enter new industry options by selecting new on the list.
VENDORS	NO__OF_EMPLOYEES	The number of employees working for the company.

table_name

column_name

remarks

VENDORS

INDUSTRY_2_ID

Select the industry that best describes the business of the individual or company. You can enter new industry options by selecting new on the list.

VENDORS

NO__OF_EMPLOYEES

The number of employees working for the company.

oa_userdata in oa_columns

Example Queries

Related Topics