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

asked 17 Dec '18, 15:58

murraysobol's gravatar image

murraysobol
10112
accept rate: 0%

edited 18 Dec '18, 03:14

Volker%20Barth's gravatar image

Volker Barth
32.6k328476698


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

permanent link

answered 17 Dec '18, 16:26

murraysobol's gravatar image

murraysobol
10112
accept rate: 0%

edited 18 Dec '18, 03:15

Volker%20Barth's gravatar image

Volker Barth
32.6k328476698

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

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

answered 18 Dec '18, 13:30

Volker%20Barth's gravatar image

Volker Barth
32.6k328476698
accept rate: 32%

edited 18 Dec '18, 13:31

Thanks Volker - that works perfectly :)

(18 Dec '18, 16:13) murraysobol
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×35

question asked: 17 Dec '18, 15:58

question was seen: 78 times

last updated: 18 Dec '18, 16:13