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

On Mar 28, 5:41 pm, sybra..._at_hccnet.nl wrote:
> On 28 Mar 2007 14:25:40 -0700, "Orlanzo" <o..._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- Hide quoted text -
>
> - Show quoted text -

Sybrand,

Thank you for replying.

In my case, I need to use Count(Refer_Num) as I don't want to count null values. The problem I encountered was related to the use of the to_date functions within the statement's Where clause. Something between Access, Jet, and the Oracle was parsing the dates incorrectly.

As a test, I executed the following statement in SQL Plus and through a pass-through query in Microsoft Access:

          Select to_date(sysdate, 'yyyy/mm/dd') from dual

SQL Plus returned: 2007-03-29

Executing the same statement through a pass-through query resulted in the following:

        03/07/1929

Notice the year in the pass-through result. The date wasn't being parsed correcly.

My reason for using the to_date funtion was to drop the time component from the date. I was able to work around the issue by modifying the query using the Trunc function as indicated below.

    where creat_dt_tm between trunc(add_months(last_day(sysdate) +1,-7))

        and trunc(sysdate)

I wasn't aware of it earlier. Fortunately, the query is returning the expected results whichis my ultimate goal.

Kind regards and thanks for your help!
Orlanzo Received on Thu Mar 29 2007 - 12:51:44 CDT