Pass-through Query Doesn't Return Expected Results

From: Orlanzo <oross_at_carolina.rr.com>
Date: 28 Mar 2007 14:25:40 -0700
Message-ID: <1175117140.617802.79120@n59g2000hsh.googlegroups.com>

Hi all,

I'm running the following through a pass-through query (Microsoft Access 2003) against an Oracle 9i
server. No results are returned. I've configured a DSN which uses the
Oracle 9.02 driver.

If I use another tool outside of Access, in this case AQT - Advanced Query
Tool, the expected results are returned. AQT is configured to use the same
DSN chosen for the pass-through query.

Has anyone observed similar behavior? The query is configured to return records, the ODBC Timeout is set to indefinite, and I've confirmed the queries are running under the same user account (Select UID from dual).

Here's the query in question. It's rather simplistic. Its executing over
table containing a few million rows of data. Also in AQT, the query completes in about 2 minutes.

Select to_date(CREAT_DT_TM, 'yyyy/mm/dd') as "Reporting Date",

    Count(Refer_Num) as "Scenarios",
    Sum(Case When App_Stat_Cde = 'AC'

        And (Lien_Pos_Cde = '1' OR Lien_Pos_Cde IS Null) Then 1
        Else 0

    End) as "Matches",
    Sum(Case When Lien_Pos_Cde = '1' Then 1

        Else 0
    End) as "Total LPEs",
    Sum(Case When App_Stat_Cde = 'AC' And (Lien_Pos_Cde = '1'

            OR Lien_Pos_Cde IS Null) Then 1 Else 0 End)


        / Count(Refer_Num) as "Pct Matches",

    Sum(Case When Lien_Pos_Cde = '1' Then 1 Else 0 End)


        / Count(Refer_Num) as "Pct LPEs"

from mflrpp.ehouse_pal
where creat_dt_tm between
to_date(add_months(last_day(sysdate),-7)+1,'yyyy-mm-dd')

    and to_date(sysdate,'yyyy-mm-dd')
Group By to_date(Creat_dt_tm, 'yyyy/mm/dd') Order By to_date(Creat_dt_tm, 'yyyy/mm/dd') Desc   Was this post helpful to Received on Wed Mar 28 2007 - 16:25:40 CDT