new CASE keyword conflicts with old table VPPRO column name

Hi all. I'm porting an old application from 7.13 to IDS 7.31. In this application reports are generated via ViewPointPro. One of the tables used by ViewPointPro (syscolformats) has a column whose name (case) conflicts with the keyword case present in IDS 7.31 SQL syntax. So, when I run a report, the statement: SELECT colname, priority, typeface, fontsize, fontstyle, fontcolor, formatmask, align, case, ruletype, checktext FROM "informix".syscolformats WHERE owner='informix' ANDtabname='xxxxxxxxxx' ORDER BY colname, priority gives -201 syntax error. Before mangling the binary of ViewPointPro to change this unfortunate (and unwise, maybe) column name, I would like to know if there is another workaround less brute-force oriented. TIA Michele System coordinates: AIX 4.3 - IDS 7.31.UC2

Michele Iacobellis wrote: > > Hi all. > > I'm porting an old application from 7.13 to IDS 7.31. In this application > reports are generated via ViewPointPro. One of the tables used by > ViewPointPro (syscolformats) has a column whose name (case) conflicts with > the keyword case present in IDS 7.31 SQL syntax. So, when I run a report, > the statement: > > SELECT colname, priority, typeface, fontsize, fontstyle, fontcolor, > formatmask, align, case, ruletype, checktext FROM > "informix".syscolformats WHERE owner='informix' AND> tabname='xxxxxxxxxx' > ORDER BY colname, priority > > gives -201 syntax error. > > Before mangling the binary of ViewPointPro to change this unfortunate (and > unwise, maybe) column name, I would like to know if there is another > workaround less brute-force oriented. Hmmm...interesting problem. I don't know to what extent the designers of VPPro can be faulted for not be prescient. I fear it was released after the SQL-92 standard was finalized, so the prescience required was not huge... Howver, there is one simple way to work around the problem, and several more dubious alternatives: SELECT ..., F.Case, ... FROM "informix".syscolformats F WHERE ... SELECT CASE AS CASE FROM ... Set DBDELIMITER environment variable (woe unto you if you try this, but the code you showed is written so that it is safe), and write: SELECT "case" FROM ... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Jonathan Leffler wrote: > Set DBDELIMITER environment variable (woe unto you if you try this, > but the code you showed is written so that it is safe), and write: > > SELECT "case" FROM ... Jonathan, you probably mean "DELIMIDENT=y", don't you? Regards, Richard PS: Do you have any idea why the Informix OLE DB Provider requires this variable to be set? It creates SQL with quotes around identifiers, and Leopold the Cat, the OLE DB guru in this group, didn't answer my question on this topic. -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+