Re: CASE statement keeps blowing up.....

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Perhaps I misunderstood, but I thought the poster's comment "created a table
in Access based upon some Oracle tables..." made it clear that the query
needed to be an Access SQL compatible query. Using a pass-through query to
Oracle would be fine if the tables were still in Oracle.

I'm not so sure that a query to an external database (ORACLE or MS_SQL using
ODBC) will bring all the records to Access for processing. I think it
depends on the query, the indexes available, the presence (or absence) of
custom user functions and other factors. However, I don't know since I have
no "access" to the underlying code.


<chris.nebinger@xxxxxxxxx> wrote in message
news:1154975653.735590.147300@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
NOTE:

If you have the Oracle tables linked, and you run an Access query on
them, you have to bring all the records from Oracle to Access for
processing. This is much slower than executing a Pass-Through query.
The Pass-Through query does not process the SQL, so you would use
Oracle's flavor of SQL vs. Access's. The query also only returns the
records that meet your criteria. The Access Help has more information.


Chris Nebinger


John Spencer wrote:
Access SQL does not support Case statements

You can use IIF, Switch, Choose functions in the SQL statement OR you can
write a custom function in VBA.

IIF statements can be nested.

See help for further information, or post back with a specific case
statement and someone should be able to help you rewrite for Access.


"myk" <mykalc@xxxxxxxxx> wrote in message
news:1154970567.560957.12890@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello All,

I created a table in Access based upon some Oracle tables. I now want
to do some more complex querying against the Access table using SQL.
One of the statements I would like to use in this is the CASE
statement, yet I keep getting Syntax Errors when I try to run the query
that contains the CASE statement. Is the statement not recognized by
Access? When I run a similar query as a Pass Through query it runs
fine.

TIA,

Mike




.

Relevant Pages

  • Re: SELECT DISTINCT slow, how can I speed up
    ... We have staff using Access 2003 as a front end to the Oracle tables ... for the purposes of ad hoc queries. ... come back within a second of the SQL starting to run. ... only 20-50 rows come back and the query takes 28 seconds and there is ...
    (comp.databases.oracle.server)
  • Re: SQL -> Oracle
    ... > 2- extracted all my SQL queries in one single file (that was the tedious ... > 3- now whenever I need new query. ... > - Sybase and SQL server prefix their parameters with '@' ... > - Firebird and Oracle have selectable stored procedures so you use them ...
    (borland.public.delphi.non-technical)
  • Re: SQL query fails
    ... You can't use the same wild card character in all db engines. ... character" wild card in MS SQL & Oracle is the underline; ... > I have a query that works in Oracle and SQL Server, ...
    (microsoft.public.access.queries)
  • Re: ORA-1866 The datetime class is invalid error...the saga continues!
    ... but you could try using a different data provider. ... Use the Microsoft ADO.NET Oracle Provider ... VB.NET to my employers and not being able to query data using a date field ... SQL statements I provided earlier will execute flawlessly in any enviornment ...
    (microsoft.public.dotnet.general)
  • Re: Pass-through Query Doesnt Return Expected Results
    ... Oracle 9.02 driver. ... DSN chosen for the pass-through query. ...
    (comp.databases.oracle.misc)