Re: Pass-through Query Doesn't Return Expected Results
- From: "Orlanzo" <oross@xxxxxxxxxxxxxxx>
- Date: 29 Mar 2007 10:51:44 -0700
On Mar 28, 5:41 pm, sybra...@xxxxxxxxx wrote:
On 28 Mar 2007 14:25:40 -0700, "Orlanzo" <o...@xxxxxxxxxxxxxxx>
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
.
- References:
- Pass-through Query Doesn't Return Expected Results
- From: Orlanzo
- Re: Pass-through Query Doesn't Return Expected Results
- From: sybrandb
- Pass-through Query Doesn't Return Expected Results
- Prev by Date: Getting an Application to run with both 8i and 10g
- Next by Date: Re: empty string or null
- Previous by thread: Re: Pass-through Query Doesn't Return Expected Results
- Next by thread: utl_smtp -> meeting request
- Index(es):
Relevant Pages
|
