Using SQL sub-queries from MS Excel 97

From: Per Christoffersen <dkideteo_at_post9.tele.dk>
Date: Thu, 20 Aug 1998 09:11:23 +0200
Message-ID: <6rgiid$25b2$1@news-inn.inet.tele.dk>

Hi,

I've discovered a problem concerning MS Excel 97 and a query against an Oracle databasen on
a Unix platform:

I've got an Oracle table (TAB1) with a number of records containg variable name (NAME1),
date (DATE1) and a value (VAL1).
The query is supposed to find the largest value of VAL1 in a given interval and present it
together with the corresponding date.

Created in SQL*Plus on the Unix/Oracle host is looks like this:

select
  DATE1,VAL1
from
  TAB1
where name='NAME1' and

       and
                DATE1>=to_date('98-07-01','yy-mm-dd')
              and
                DATE1<to_Date('98-08-01','yy-mm-dd')
              and
  VAL1=(select max(VAL1)
              from
         TAB1
              where
                name='NAME1'
       and
                DATE1>=to_date('98-07-01','yy-mm-dd')
              and
                DATE1<to_Date('98-08-01','yy-mm-dd'));

I'm using a sub-query to find the correct value and date. It works perfect and gives the
expected result.

The problem arises when i'm trying to fetch the same data into MS Excel. I'm using the
function SQLExecQuery from within Visual Basic for applications.

It looks like this:

  VarName1="NAME1"
  FromDATE1="98-07-01 00:00"
  ToDATE1="98-07-01 00:00"

  SQLStatement = "SELECT VAL1,DATE1 FROM TAB1 " & _


                 "WHERE (NAME='" & VarName1 & "') " & _


                 "AND (DATE1>{ts '" & FromDATE1 & "'}) " & _


                 "AND (DATE1<={ts '" & ToDATE1 & "'}) " & _


                 "AND VAL1 IN (" & _


                 "SELECT MAX(VAL1) FROM TAB1 " & _


                "WHERE (NAME='" & VarName1 & "') " & _


                 "AND (DATE1>{ts '" & FromDATE1 & "'}) " & _


                 "AND (DATE1<={ts '" & ToDATE1 & "'}) " & _


                 ");"



Looks fine, but doesn't work !. The query retuens with:
"Error 2042: Unable to execute the query on the specified data source"

After some fiddling with the query, i've got the following to work:

  SQLStatement = "SELECT VAL1,DATE1 FROM TAB1 " & _


                 "WHERE (NAME='" & VarName1 & "') " & _


                 "AND VAL1 IN (" & _


                 "SELECT MAX(VAL1) FROM TAB1 " & _


                "WHERE (NAME='" & VarName1 & "') " & _


                 "AND (DATE1>{ts '" & FromDATE1 & "'}) " & _


                 "AND (DATE1<={ts '" & ToDATE1 & "'}) " & _


                 ");"



- with one WHERE clause in the main query and one sub-query. As soon as i

put more than
one WHERE clause in the main query, the problem arises and the error 2042 is returned.

Question to the community:

Am I doing something wrong, or have i discovered some un-documented feature in
the ODBC driver ?

I've browsed the various examples and help files, but all examples are with at maximum
only one WHERE clause and a sub-query.

Various tech info:

Server: Oracle 7.2 on HP-UX V9.05 platform CLient: MS Excel 97 with Oracle ODBC driver V2.5 on NT4 SP3 platform.

All help is greatly appreciated !

/Per Christoffersen Received on Thu Aug 20 1998 - 02:11:23 CDT