Hello I have Oralce Query for Selecting all the columns:- SELECT tc.column_name, tc.owner, tc.table_name, tc.column_id, tc.nullable, tc.data_type, c.constraint_type, c.r_owner AS reference_owner, rcc.table_name AS reference_table, rcc.column_name AS reference_column_name FROM SYS.ALL_TAB_COLUMNS tc LEFT OUTER JOIN SYS.ALL_CONS_COLUMNS cc ON ( tc.owner = cc.owner AND tc.table_name = cc.table_name AND tc.column_name = cc.COLUMN_NAME ) LEFT OUTER JOIN SYS.ALL_CONSTRAINTS c ON ( tc.owner = c.owner AND tc.table_name = c.table_name AND c.constraint_name = cc.constraint_name ) LEFT OUTER JOIN ALL_CONS_COLUMNS rcc ON ( c.r_owner = rcc.owner AND c.r_constraint_name = rcc.constraint_name ) WHERE tc.table_name = 'REPORTSETTING' AND tc.OWNER = 'NVN' ORDER BY tc.column_id;
*This query is working fine in Oracle DB, but while using same query in Drill, it giving error. Query for Drill is:-* SELECT tc.column_name, tc.owner, tc.table_name, tc.column_id,tc.nullable,tc.data_type,c.constraint_type,c.r_owner AS reference_owner, rcc.table_name AS reference_table, rcc.column_name AS reference_column_name FROM OracleDB.SYS.ALL_TAB_COLUMNS tc LEFT OUTER JOIN OracleDB.SYS.ALL_CONS_COLUMNS cc ON ( tc.owner = cc.owner AND tc.table_name = cc.table_name AND tc.column_name = cc.COLUMN_NAME ) LEFT OUTER JOIN OracleDB.SYS.ALL_CONSTRAINTS c ON ( tc.owner = c.owner AND tc.table_name = c.table_name AND c.constraint_name = cc.constraint_name ) LEFT OUTER JOIN OracleDB.SYS.ALL_CONS_COLUMNS rcc ON ( c.r_owner = rcc.owner AND c.r_constraint_name = rcc.constraint_name ) WHERE tc.table_name = 'REPORTSETTING' AND tc.OWNER = 'NVN' ORDER BY tc.column_id ASC; Following Error Showing:- org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. sql SELECT * FROM (SELECT "t1"."OWNER", "t1"."TABLE_NAME", "t1"."COLUMN_NAME", "t1"."DATA_TYPE", "t1"."NULLABLE", "t1"."COLUMN_ID", "ALL_CONSTRAINTS"."CONSTRAINT_TYPE", "ALL_CONSTRAINTS"."R_OWNER", "ALL_CONSTRAINTS"."R_CONSTRAINT_NAME" FROM (SELECT "t0"."OWNER", "t0"."TABLE_NAME", "t0"."COLUMN_NAME", "t0"."DATA_TYPE", "t0"."DATA_TYPE_MOD", "t0"."DATA_TYPE_OWNER", "t0"."DATA_LENGTH", "t0"."DATA_PRECISION", "t0"."DATA_SCALE", "t0"."NULLABLE", "t0"."COLUMN_ID", "t0"."DEFAULT_LENGTH", "t0"."DATA_DEFAULT", "t0"."NUM_DISTINCT", "t0"."LOW_VALUE", "t0"."HIGH_VALUE", "t0"."DENSITY", "t0"."NUM_NULLS", "t0"."NUM_BUCKETS", "t0"."LAST_ANALYZED", "t0"."SAMPLE_SIZE", "t0"."CHARACTER_SET_NAME", "t0"."CHAR_COL_DECL_LENGTH", "t0"."GLOBAL_STATS", "t0"."USER_STATS", "t0"."AVG_COL_LEN", "t0"."CHAR_LENGTH", "t0"."CHAR_USED", "t0"."V80_FMT_IMAGE", "t0"."DATA_UPGRADED", "t0"."HISTOGRAM", "ALL_CONS_COLUMNS"."OWNER" "OWNER0", "ALL_CONS_COLUMNS"."CONSTRAINT_NAME", "ALL_CONS_COLUMNS"."TABLE_NAME" "TABLE_NAME0", "ALL_CONS_COLUMNS"."COLUMN_NAME" "COLUMN_NAME0", "ALL_CONS_COLUMNS"."POSITION", CAST("t0"."OWNER" AS VARCHAR(120) CHARACTER SET "ISO-8859-1") "$f36" FROM (SELECT "OWNER", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "DATA_TYPE_MOD", "DATA_TYPE_OWNER", "DATA_LENGTH", "DATA_PRECISION", "DATA_SCALE", "NULLABLE", "COLUMN_ID", "DEFAULT_LENGTH", "DATA_DEFAULT", "NUM_DISTINCT", "LOW_VALUE", "HIGH_VALUE", "DENSITY", "NUM_NULLS", "NUM_BUCKETS", "LAST_ANALYZED", "SAMPLE_SIZE", "CHARACTER_SET_NAME", "CHAR_COL_DECL_LENGTH", "GLOBAL_STATS", "USER_STATS", "AVG_COL_LEN", "CHAR_LENGTH", "CHAR_USED", "V80_FMT_IMAGE", "DATA_UPGRADED", "HISTOGRAM", CAST("COLUMN_NAME" AS VARCHAR(4000) CHARACTER SET "ISO-8859-1") "$f31" FROM "SYS"."ALL_TAB_COLUMNS" WHERE "TABLE_NAME" = 'REPORTSETTING' AND "OWNER" = 'NVN') "t0" LEFT JOIN "SYS"."ALL_CONS_COLUMNS" ON "t0"."OWNER" = "ALL_CONS_COLUMNS"."OWNER" AND "t0"."TABLE_NAME" = "ALL_CONS_COLUMNS"."TABLE_NAME" AND "t0"."$f31" = "ALL_CONS_COLUMNS"."COLUMN_NAME") "t1" LEFT JOIN "SYS"."ALL_CONSTRAINTS" ON "t1"."$f36" = "ALL_CONSTRAINTS"."OWNER" AND "t1"."TABLE_NAME" = "ALL_CONSTRAINTS"."TABLE_NAME" AND "t1"."CONSTRAINT_NAME" = "ALL_CONSTRAINTS"."CONSTRAINT_NAME") "t2" LEFT JOIN (SELECT "CONSTRAINT_NAME", "TABLE_NAME", "COLUMN_NAME", CAST("OWNER" AS VARCHAR(120) CHARACTER SET "ISO-8859-1") "$f5" FROM "SYS"."ALL_CONS_COLUMNS") "t3" ON "t2"."R_OWNER" = "t3"."$f5" AND "t2"."R_CONSTRAINT_NAME" = "t3"."CONSTRAINT_NAME" plugin OracleDB Fragment 0:0 [Error Id: 2a11fed2-ec79-4ef1-9d29-781af21274f6 *Please Tell me what i am doing wrong in this query?* -- Thanks & Regards. Sanjiv Swaraj