Why doesn't the simplest query on earth work?

Haan6252

New Member
I want to create a qyery in MS Query using the SQL 92 driver but get a -20003 error. The field I query is called date and that is the problem I suppose. I can query all other fields, but this is a reserved keyword in SQL.

I tried to add brackets in several forms, but I don't get the query to work. Anybody any suggestions? Generating an SQL trace log didn't provide any additional information.

Tried:

Following kb article 18909

select "pub"."prhtrs".date from "pub"."prhtrs"

SELECT "date"
FROM PUB.prhtrs prhtrs_0
 

Stefan

Well-Known Member
The problem is indeed the field / column named DATE. In both SQL and ODBC this is a reserved keyword. The SQL92 driver (via the Progress SQL Explorer) actually will swallow columns named "date" if you enclose it in quotes.

The easiest work around (without renaming database fields) to also allow ODBC to handle it is to create a VIEW on the table with a legal column name for the illegal column names as follows:

CREATE VIEW odbc.prhtrs ( odbc_date, field1, field2 ) AS
SELECT "date", field1, field2
FROM pub.prhtrs;
COMMIT WORK;

Execute this statement when connected to the database with the Progress SQL Explorer. Having done this when you connect to this database using ODBC make sure that 'VIEW's are visible, select 'odbc.prhtrs' and query your data.
 
Top