Re: Query giving different results using server sqlplus and client sqlplus

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

--
http://www.freelists.org/webpage/oracle-l