I have an Oracle query which I need to translate into SQL Anywhere 17. Here is the Oracle query: SELECT a.table_name || '|' || a.column_name AS Reference, a.table_name AS TableName, a.column_name AS ColName FROM user_cons_columns a JOIN user_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name JOIN user_constraints c_pk ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name WHERE c.constraint_type = 'R' AND c_pk.table_name = 'S1_DOCUMENT_TEMPLATE' / What are the equivalent tables/views for: user_cons_columns and user_constraints?? Thanks Murray |
Here's my attempt: select TF.table_name || '|' || TC.column_name as REFERENCE, TF.table_name as TABLENAME, TC.column_name as COLNAME -- In case you have foreign tables with several FKs to the same parent table -- or have FKs with more than one column, you will usually also include -- X.index_name as role_name -- or group by TF.table_name, X.index_name to list all the columns -- that build one FK... from SYS.SYSTAB TP inner join SYS.SYSFKEY FK on TP.table_id = FK.primary_table_id inner join SYS.SYSTAB TF on FK.foreign_table_id = TF.table_id inner join SYSIDX X on FK.foreign_table_id = X.table_id and FK.foreign_index_id = X.index_id inner join SYSIDXCOL XC on X.table_id = XC.table_id and X.index_id = XC.index_id inner join SYS.SYSTABCOL TC on XC.table_id = TC.table_id and XC.column_id = TC.column_id where TP.table_name = 'S1_DOCUMENT_TEMPLATE' order by REFERENCE Thanks Volker - that works perfectly :)
(18 Dec '18, 16:13)
murraysobol
|
I tried this SQL: SELECT a.table_name + '|' + c.column_name AS Table_Reference, a.table_name AS TableName, c.column_name AS ColName FROM systable a, syscolumn c WHERE c.table_id = systable.table_id AND systable = 'S1_DOCUMENT_TEMPLATE' but get this error: Correlation name 'systable' not found. However this query works: select * from systable where table_name = 'S1_DOCUMENT_TEMPLATE'; updated SQL: SELECT a.table_name + '|' + c.column_name AS Table_Reference, a.table_name AS TableName, c.column_name AS ColName FROM systable a, syscolumn c WHERE c.table_id = a.table_id AND a.table_name = 'S1_DOCUMENT_TEMPLATE'
(17 Dec '18, 16:33)
murraysobol
So is this still an open answer?
(18 Dec '18, 03:17)
Volker Barth
Volker: it is still an open question. Here is the expected results: REFERENCE TABLENAME COLNAME ----------------------------------------- S1_CONTRACT|TEMPLATE_ID S1_CONTRACT TEMPLATE_ID S1_VENDOR_DATA|PC_CONTRACT_TEMPLATE_ID S1_VENDOR_DATA PC_CONTRACT_TEMPLATE_ID S1_CUSTOMER_DATA|SC_CONTRACT_TEMPLATE_ID S1_CUSTOMER_DATA SC_CONTRACT_TEMPLATE_ID S1_PLC_DATA|SC_CONTRACT_TEMPLATE_ID S1_PLC_DATA SC_CONTRACT_TEMPLATE_ID S1_PLC_DATA|PC_CONTRACT_TEMPLATE_ID S1_PLC_DATA PC_CONTRACT_TEMPLATE_ID S1_GRAINSMART_OPTION|SC_CONTRACT_TEMPLATE_ID S1_GRAINSMART_OPTION SC_CONTRACT_TEMPLATE_ID S1_GRAINSMART_OPTION|PC_CONTRACT_TEMPLATE_ID S1_GRAINSMART_OPTION PC_CONTRACT_TEMPLATE_ID S1_ORDERLOG_LOADING_INSTR|DEFAULT_TEMPLATE S1_ORDERLOG_LOADING_INSTR DEFAULT_TEMPLATE S1_ADVANCED_SHIPMENT_MGM_DOC|DEFAULT_TEMPLATE S1_ADVANCED_SHIPMENT_MGM_DOC DEFAULT_TEMPLATE 9 rows selected. In other words, any time the table 's1_document_template' is the parent table in a Foreign Key relationship report the following: table_name '|' column_name table_name column_name Our intent is to place this SQL in a view so that any future additional tables will be reported in the view automatically. Murray
(18 Dec '18, 10:58)
murraysobol
|