SQL Query result not stable

[venur]

Hi,

I am running a query which runs fine in SQL Navigator 4.2 but it does't run in TOAD 7.4 and SQL*Plus 8.1.6.
This query is on a view and its between dates. Is it to doing some thing with the Query or With any settings ?? I am lost..

QRY:

SELECT * FROM PLAYERINFO_VIEW A
WHERE TO_DATE(A.REG,'DD-MON-YYYY HH24:MI:SS')
BETWEEN
TO_DATE('01-Feb-2003 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND
TO_DATE('27-Feb-2003 00:00:00','DD-MON-YYYY HH24:MI:SS')

Thank's in advance,

Venu

 

[SantaMufasa]

Venu,

Yours is a VERY straightforward query that should behave the same regardless of source environment. When you say, "SQL Query result not stable..." what does that mean? Are your results unpredictably changing from environment to environment or between executions? When you say, "...it does't run in TOAD 7.4 and SQL*Plus 8.1.6", what are the reactions (error messages, incorrect results) that occur in, say, SQL*Plus?

Dave

 

[venur]

Hi Dave,

There is no error message as such. All I get is
" no rows selected ". Well all I am trying to do is get the rows between 2 given date and times.


Thank you
venu

 

[SantaMufasa]

Venu,

In this case you need to &quot;set dwimnwis on&quot;...This tells Oracle to &quot;Do What I Mean, Not What I Say&quot; <smile>.

If you receive the message, &quot;no rows selected &quot;, it means that absolutely, positively, there are no rows in PLAYERINFO_VIEW whose &quot;REG&quot; column (when converted from character to date) falls between midnight on the morning of 01-FEB-2003 and midnight on the morning of 27-FEB-2003. If you believe rows should print out, then I would specifically &quot;SELECT * &quot; from the rows that you believe should display. You will probably need to re-formulate your WHERE clause using a conditional expression that does not involve &quot;REG&quot;, however.

Please post your results.

Dave

 

[venur]

Hi Dave,

But this query runs fine in all the source environment.

SELECT * FROM PLAYERINFOVIEW A
WHERE A.REG
BETWEEN TO_DATE('01-Feb-2003 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND TO_DATE('27-Feb-2003 00:00:00','DD-MON-YYYY HH24:MI:SS')

Was my approach wrong to get the rows between the the date?

Thanks,
venu

 

[sem]

First of all you must be sure that you run your query against the same schema. This may involve both connecting as the same user to the same database and similar altering/not altering session to set current schema. Then check NLS_DATE_FORMAT (if not set may be implied by other NLS settings), it may affect default conversions. You're sure that all conversions in your statements are quite explicit, but this may not be the case. If A.REG is DATE, your attempt to convert it to date behind the scenes converts it to char first, using default format, and only then applies provided mask. Otherwise, if A.REG is [VAR]CHAR[2], it's converted by default format mask in the second statement. So, in any case they're not identical.

Regards, Dima

 

[sampsonr]

Dima nailed it (again). Try this to see what's happening:

SELECT
TO_CHAR(A.REG,'DD-MON-YYYY HH24:MI:SS'),
TO_CHAR(TO_DATE(A.REG,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
FROM PLAYERINFO_VIEW A
WHERE A.REG IS NOT NULL