Re: Matching View Columns to Table Columns

Fred Prose wrote: > I need to develop a dictionary or cross reference that maps > view/column to table/column. I realize that a view "column" is really > an expression and does not necessarily map to a table column ' but in > our case ' they do. > > They only way I can think of doing this is to process the CREATE VIEW > statement through AWK and match the first column definition to the > first SELECT column, etc. > > Anyone out there have any suggestions or put something like this > together? I haven't formally put anything together to do it. The SQL that is placed in the sysviews table is modified from what you type, but that works to your benefit. IIRC, each column in the select list is prefixed with a table alias, and that makes it easier to parse. Of course, it presumes you don't have fancy (ISO style) joins in your FROM clause. Toolkit? SQLCMD has a tokenizer - sqltoken.c, sqltoken.h (if you don't have sqltoken.h, you haven't got the current version), and there's also an IUS tokenizer, iustoken.c, built on top of sqltoken.c, though you probably don't need to use it. This would help with parsing the statement retrieved from sysviews. The SQLCMD at the IIUG is a little out of date, mainly because I've not sent them the current latest and greatest - which can be obtained from my web page at home dot earthlink dot net slash tilde jleffler slash JLSS. Be aware that there is a slight question mark against the 74.02 version; there have been problems with it and ESQL/C 9.16 (CSDK 2.10) which don't reproduce with ESQL/C 9.53 (CSDK 2.81), but I've not yet proven that the problem is in ESQL/C and not in my code (Hi Ravi!). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/