Re: Pass-through Query Doesn't Return Expected Results

On 28 Mar 2007 14:25:40 -0700, "Orlanzo" <oross_at_carolina.rr.com> wrote:

>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

The ultimate (and as far as I am concerned the only) proof would be provided by running the query through sql*plus. The to_date's in the where, group by and order by clauses are redundant, as create_dt_tmj hopefully already is a date, and sysdate definitely is. This may result in incorrect results. I would replace your expression lisg by * or count(*) to find out whether the where clause is the culprit. Note: Your use of to_date doesn't result in a parse error.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA