Q+E: DDE or SQL Error Message Extracting Oracle Dates

The information in this article applies to:

• Q+E for Microsoft Excel for Windows, versions 3.0, 3.0a
• Q+E for Microsoft Excel for OS/2, versions 3.0
• Microsoft Excel for Windows, versions 3.0 and 4.0
• Microsoft Excel for OS/2, version 3.0

SYMPTOMS

When you set an external database to an Oracle table and extract dates, you may receive a DDE or SQL error message.

STATUS

Microsoft has confirmed this to be a problem with the Q+E add-in macro. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

WORKAROUNDS

To work around this problem, do either of the following.

Workaround 1

The following procedure should produce the same results you get when you choose Extract from the Data menu. (You can automate this procedure with a macro by recording steps 2-5 with the Macro Recorder.)

1. In Q+E, open the Oracle Table, choose Select Add Condition and run your Query on the Date field. Save the Query as DATE.QEF.

2. Start Microsoft Excel.

3. From the Data menu, choose SQL Query.

4. Open the DATE.QEF file and choose the Run button.

5. Paste the records as unlinked.

Workaround 2

Because this problem is the result of a syntax error in the SQL statement, you can correct it by correcting the SQL statement. To view the SQL query statement and correct it in Microsoft Excel, do the following:

1. In Q+E, open the Oracle Table, choose Select Add Condition and run your Query on the Date field.

2. Select the SQL query (the SQL statement is already highlighted) and press CTL+INS to copy the query.

3. In Microsoft Excel, paste the SQL statement into an empty portion of the spreadsheet. It should resemble the following:

         (where Table name = Datephoner Date Field = DATED. SELECT DATED,
         NAME, NUMBERED, PHONERFROM DATEPHONERWHERE DATED > TO_DATE('1992-12-
         04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'
   

4. From the Data menu, choose Set Database, select the External option and choose OK.

5. In the External Database dialog box, in the Source box, choose the Sources button, log on to Oracle, and select your table.

6. In cell A1, use a substitute field name for your criteria date field. For example, use "Fred" instead of "Date." For the criteria in cell A2, use a portion of the SQL statement that was pasted from Q+E. Select and copy everything after the "where" statement in the above example and paste this into cell A2. Your data should resemble the following example:

         A1: Fred A2: DATED > TO_DATE('1992-12-00:00','YYYY-MM-DD HH24:MI:SS'
   

7. Select cells A1:A2 and choose Set Criteria from the Data menu.

8. Paste the field names, set the extract range and choose Extract from the Data menu.

REFERENCES

"Q+E for Microsoft Excel User's Guide," version 3.0a, included with Microsoft Excel version 3.0 "Q+E for Microsoft Excel User's Guide," version 3.0a, included with Microsoft Excel version 4.0