Required information for custom SQL statements

By default, the Connection Manager offers default SQL statements for DB2, Oracle and MS SQL Server data sources.

If you selected a Custom data source category, you need to enter your own, custom SQL statements:

In the Tables and columns tab.
In the Uniqueness and Foreign keys tabs if you want to test your data for constraints, uniqueness, and/or referential integrity.

If you disabled the checkbox Get uniqueness information and/or the checkbox Get referential integrity information in the General section, the Connection Manager grays out the Uniqueness and/or the Foreign keys SQL tab, respectively.

You can also overwrite a default SQL statement with a custom statement. For information on how to enter SQL statements and verify them, see chapter "Validate SQL statements".

The chapters below list all headers that are mandatory for custom SQL statements.

Tables and columns

The SQL statement in the Tables and columns tab must include the following information:

Header	Description
Schema	Name of the schema
Table	Name of the table
Column	Name of the column
Type	Data type of the column
Length	Length of the column
Scale	Scale of the column
Nullable	Information whether a column can be nullable. The value has to be Y or N.

Example

The example below is a possible custom Tables and columns SQL statement for PostgreSQL 10. The actual SQL statement depends on the kind of query you want to run.

SELECT   c.table_schema AS "SCHEMA", 
             c.table_name   AS "TABLE", 
             c.column_name  AS "COLUMN", 
             c.data_type    AS "TYPE", 
             CASE 
                      WHEN c.character_maximum_length IS NULL THEN c.numeric_precision 
                      ELSE c.character_maximum_length 
                             END as "LENGTH", 
             c.numeric_scale AS "SCALE", 
             CASE c.is_nullable 
                      WHEN 'YES'THEN 'Y'
                      ELSE 'N'
             END AS "NULLABLE"
    FROM     information_schema.columns c 
    JOIN     information_schema.tables t 
    ON       c.table_schema = t.table_schema 
    AND      c.table_name = t.table_name 
    WHERE    t.table_type = 'BASE TABLE'
    AND      c.table_schema = 'public'
ORDER BY c.table_schema, 
         c.table_name, 
         c.ordinal_position offset 0 rows;
                                                    

Uniqueness

The SQL statement in the Uniqueness tab must include the following information:

Header	Description
Schema	Name of the schema
Table	Name of the table
Constraint	Name of the constraint
Column	Name of the column
Rule	Information on the type of constraint. Allowed values are: U for uniqueness constraint P for primary key constraint

Example

The example below is a possible custom Uniqueness SQL statement for PostgreSQL 10. The actual SQL statement depends on the kind of query you want to run.

SELECT   c.table_schema    AS "SCHEMA", 
         c.table_name      AS "TABLE", 
         c.constraint_name AS "CONSTRAINT", 
         s.column_name     AS "COLUMN", 
         CASE c.constraint_type 
                  WHEN 'UNIQUE' THEN 'U' 
                  ELSE 'P' 
         END as "RULE"
FROM     information_schema.table_constraints c 
JOIN     information_schema.key_column_usage s 
ON       c.constraint_name = s.constraint_name 
WHERE   ( 
                  constraint_type = 'UNIQUE'
         OR       constraint_type = 'PRIMARY KEY') 
ORDER BY c.table_schema, 
         c.table_name, 
         c.constraint_name, 
         s.ordinal_position offset 0 rows;
                                                    

Foreign keys

The SQL statement in the Foreign keys tab must include the following information:

Header	Description
Schema	Name of the schema
Table	Name of the table
Constraint	Name of the foreign key
Column	Name of the column
ParentSchema	Name of the schema the foreign key points to
ParentTable	Name of the table the foreign key points to
ParentColumn	Name of the column the foreign key points to

Example

The example below is a possible custom Foreign keys SQL statement for PostgreSQL 10. The actual SQL statement depends on the kind of query you want to run.

SELECT  t.table_schema as "SCHEMA",
        t.table_name as "TABLE",
        c.column_name as "COLUMN",
        r.constraint_name as "CONSTRAINT",
        k2.table_schema as "PARENTSCHEMA",
        k2.table_name as "PARENTTABLE",
        k2.column_name as "PARENTCOLUMN"       
FROM information_schema.tables t
    LEFT JOIN pg_catalog.pg_description td
        ON (td.objoid = (quote_ident(t.table_schema)||'.'||quote_ident(t.table_name))::regclass AND td.objsubid = 0)
    NATURAL LEFT JOIN information_schema.columns c
        LEFT JOIN(
            information_schema.key_column_usage k
                NATURAL JOIN information_schema.table_constraints n
                NATURAL LEFT JOIN information_schema.referential_constraints r)
        ON c.table_catalog=k.table_catalog
        AND c.table_schema=k.table_schema
        AND c.table_name=k.table_name
        AND c.column_name=k.column_name
    LEFT JOIN information_schema.key_column_usage k2
        ON k.position_in_unique_constraint=k2.ordinal_position
        AND r.unique_constraint_catalog=k2.constraint_catalog
        AND r.unique_constraint_schema=k2.constraint_schema
        AND r.unique_constraint_name=k2.constraint_name
    LEFT JOIN pg_catalog.pg_description cd
        ON (cd.objoid = (quote_ident(t.table_schema)||'.'||quote_ident(t.table_name))::regclass AND cd.objsubid = c.ordinal_position)
WHERE t.TABLE_TYPE='BASE TABLE' AND t.table_schema NOT IN('information_schema','pg_catalog') AND n.constraint_type = 'FOREIGN KEY'
ORDER BY t.table_schema, t.table_name, r.constraint_name, k.ordinal_position;
                                                    