|
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
|