Re: Query giving different results using server sqlplus and client sqlplus
- From: Lothar Flatz <l.flatz@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 22 Jan 2015 11:26:08 +0100
Hi Ahmed,
I had a similar case once where the century was set wrong on the server
and therefore the wrong century was stored.
We could not see because we were using a two digit century. It is
possible that nls setting on your client differ from that on your server.
Not an explanation, but maybe a hint.
Is transcribeddate really of type date?
Lothar
Am 22.01.2015 um 11:16 schrieb Zabair Ahmed:
Hello All,
Can somebody help with this, I think am going mad.
Am running some simple select queries 'select count(*) from report.job
where transcribeddate < '01-OCT-2014';' and 'select count(*) from
report.job;'.
When I run these queries within sqlplus logged directly onto the
server where the database is running, I get the following results. Am
logged in as system in both cases:-
SQL> show user
USER is "SYSTEM"
SQL> select name from v$database;
NAME
---------
DV1DW
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select count(*) from report.job where transcribeddate <
'01-OCT-2014';
COUNT(*)
----------
6758430
SQL> select count(*) from report.job;
COUNT(*)
----------
19626224
SQL>
And when I run the same queries within a client sqlplus I get the
following results.
SQL> show user
USER is "SYSTEM"
SQL> select name from v$database;
NAME
---------
DV1DW
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select count(*) from report.job where transcribeddate <
'01-OCT-2014';
COUNT(*)
----------
18185097
SQL> select count(*) from report.job;
COUNT(*)
----------
19626224
SQL>
Why would i get different number of rows returned for the first query
in each case?
Here is the structure of the table and it's the same irrespective of
where the sql is executed.
SQL> select object_name,object_type,created,status from dba_objects
where object_name = 'JOB' and owner = 'REPORT';
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ------------------- --------- -------
JOB TABLE 11-JUL-13 VALID
JOB TABLE PARTITION 11-JUL-13 VALID
JOB TABLE PARTITION 11-JUL-13 VALID
JOB TABLE PARTITION 11-JUL-13 VALID
JOB TABLE PARTITION 11-JUL-13 VALID
JOB TABLE PARTITION 12-OCT-13 VALID
JOB TABLE PARTITION 13-JUN-14 VALID
JOB TABLE PARTITION 13-JUN-14 VALID
JOB TABLE PARTITION 20-NOV-14 VALID
JOB TABLE PARTITION 20-NOV-14 VALID
JOB TABLE PARTITION 20-NOV-14 VALID
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ------------------- --------- -------
JOB TABLE PARTITION 20-NOV-14 VALID
JOB TABLE PARTITION 20-NOV-14 VALID
JOB TABLE PARTITION 09-JAN-15 VALID
JOB TABLE PARTITION 10-JAN-15 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ------------------- --------- -------
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 11-JUL-13 VALID
JOB TABLE SUBPARTITION 12-OCT-13 VALID
JOB TABLE SUBPARTITION 12-OCT-13 VALID
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ------------------- --------- -------
JOB TABLE SUBPARTITION 12-OCT-13 VALID
JOB TABLE SUBPARTITION 12-OCT-13 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 13-JUN-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ------------------- --------- -------
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ------------------- --------- -------
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 20-NOV-14 VALID
JOB TABLE SUBPARTITION 09-JAN-15 VALID
JOB TABLE SUBPARTITION 09-JAN-15 VALID
JOB TABLE SUBPARTITION 09-JAN-15 VALID
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ------------------- --------- -------
JOB TABLE SUBPARTITION 09-JAN-15 VALID
JOB TABLE SUBPARTITION 10-JAN-15 VALID
JOB TABLE SUBPARTITION 10-JAN-15 VALID
JOB TABLE SUBPARTITION 10-JAN-15 VALID
JOB TABLE SUBPARTITION 10-JAN-15 VALID
71 rows selected.
TIA
--
---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
http://www.avast.com
Other related posts:
