same select different result on different machine

[kukuluku]

A developer runs this sql and gets 3 rows selected but when I run on my machine I get no row selected. I understand that due to time stamp if use trunc or even to_char (compare charactor) this works all the time. What puzzle me is that with same sql we get different results. I checked her registry (PC) but didn't find anything that may result differently. I appreciate any input you can give.

Thanks,

select * from ps_cost
where business_unit = 'TDMN'
and asset_id = '000000010025'
and deptid = '86000'
and category = 'BLDG'
and trans_type = 'TRF'
and to_date(dttm_stamp,'DD-MON-YYYY') = '25-JUN-1999'
and accounting_dt = '25-JUN-1999'

 

[sem]

Either dttm_stamp or accounting_dt (suppose first) may contain TIME part that in turn may be either truncated or not according to you NLS_DATE_FORMAT.

Regards, Dima

http://seminihin.narod.ru

 

[Turkbear]

Hi,
I'd suspect accounting_dt since you are using it directly in an equate - if it has a time component, it will not match the criteria - the to_date(dttm_stamp,'DD-MON-YYYY') avoids that problem by only comparing the date part of the data.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kukuluku]

I understand how to change the code to avoid the problem. But my question is why we get different results running same sql for same database on her machine and on mine. Anyone?

 

[Turkbear]

Hi,
Possibly the default date format (NLS_DATE_FORMAT) is being interpreted differently..


The registry settings can control that..If ANY NLS_ parameters are set in the client PC's registry they will override the database's settings for that parameter, as well as set all unspecified NLS_ parameters to Oracle's default format ( for dates , DD-MON-YY ).







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[kukuluku]

Thank you for all the replies. Can someone give more depth for this?

I understand the client nls_date_format overwrites the init.ora. I did ALTER SESSION SET NLS_DATE_FORMAT='DD/MON/YYYY'; then it seems ignore the time (sql gets result). I did 'show parameter nls_date_format' and it did not change after the ALTER SESSION. Is the 'show parameter' only shows from init.ora?
How does alter session change the result? I still don't get it.

Thanks so much.

 

[Turkbear]

Hi,
I think it 'says', in effect, ignore any time part of the DateTime field when doing a comparison and I have not used any conversion functions (like to_char or to_date).








To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[sem]

In fact all local settings override server ones. Server value for such parameter is in fact just a default for your session. SHOW PARAMETER command shows DATABASE parameters, not your session ones.

Regards, Dima

http://seminihin.narod.ru

 

[kukuluku]

I am still not clear...

SQL> show parameter nls_date

NAME TYPE VALUE
------------------------------------ ---------- ----------
nls_date_format string DD_MON_YYYY

SQL> select * from ps_cost
2 where business_unit = 'TDMN'
3 and asset_id = '000000010025'
4 and deptid = '86000'
5 and category = 'BLDG'
6 and trans_type = 'TRF'
7 and to_date(dttm_stamp,'DD-MON-YYYY') = '25-JUN-1999'
8 and to_date(accounting_dt, 'DD-MON-YYYY') = '25-JUN-1999';
and to_date(dttm_stamp,'DD-MON-YYYY') = '25-JUN-1999'
*
ERROR at line 7:
ORA-01858: a non-numeric character was found where a numeric was expected

alter session set nls_date_format='DD_MON_YYYY';

select * from ps_cost
where business_unit = 'TDMN'
and asset_id = '000000010025'
and deptid = '86000'
and category = 'BLDG'
and trans_type = 'TRF'
and to_date(dttm_stamp,'DD-MON-YYYY') = '25-JUN-1999'
and to_date(accounting_dt, 'DD-MON-YYYY') = '25-JUN-1999';

3 ROWS SELECTED

1) What is the problem with first select?
2) didn't I set nls_date_format to be the same as DB?
Why 2nd SQL work?

 

[kukuluku]

Thank you for all responses. I've found this site explain pretty well.

http://asktom.oracle.com/pls/ask/f?...0_P8_DISPLAYID,F4950_P8_CRITERIA:351017764854