Re: Query giving different results using server sqlplus and client sqlplus
- From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- To: "roon987@xxxxxxxxxxx" <roon987@xxxxxxxxxxx>
- Date: Thu, 22 Jan 2015 10:32:22 +0000
Different NLS settings combined with implicit date conversion?
Sent from my iPhone
> On 22 Jan 2015, at 10:18, "Zabair Ahmed" <roon987@xxxxxxxxxxx> wrote:
>
> 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
>
Other related posts:
