Home » SQL & PL/SQL » SQL & PL/SQL » Whats wrong with query.  () 1 Vote
Whats wrong with query. [message #194688] Sun, 24 September 2006 02:44 Go to next message
oradev91
Messages: 3
Registered: September 2006
Junior Member
I came across with this very strange problem. I write this very simple query:

select column_list
from table_name
where date_column = 'some date';

this gives me a character string error.
I have tried each and every thing to correct it, like TO_CHAR, TO_DATE conversions both side of the where clause, even i put the date value in a variable and then use that variable in the where clause, but no use. I am using Oracle HTMLDB at front end and Oracle 9i at backend.

Please suggest me something.

Thank you.
Re: Whats wrong with query. [message #194692 is a reply to message #194688] Sun, 24 September 2006 04:35 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
execute that query on sqlplus and post the exact query and exact error as well as table structure.
Re: Whats wrong with query. [message #194721 is a reply to message #194692] Sun, 24 September 2006 23:54 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
hey i came across the same situation a few days back, spent a lot of time to solve it but no use. But somebody made my life easy.
There is nothing wrong with your query. just see this page and you will find the most strange news of your oracle career. Smile

http://free-advisory.com/forums/oracle/YaBB.pl?num=1158753876

[Updated on: Mon, 25 September 2006 00:00]

Report message to a moderator

icon14.gif  Re: Whats wrong with query. [message #194724 is a reply to message #194721] Mon, 25 September 2006 00:12 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
thanks liza.i have learnt something new and i am sure that i will keep this BUG in mind in the future.

[Updated on: Mon, 25 September 2006 01:03]

Report message to a moderator

Re: Whats wrong with query. [message #194728 is a reply to message #194724] Mon, 25 September 2006 00:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well, THAT is a bug Razz
Re: Whats wrong with query. [message #194732 is a reply to message #194728] Mon, 25 September 2006 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

where date_column = '20-SEP-2006';

THAT seems to be a bug to me as well.
(If the datatype of date_column is DATE, that is)
Never compare dates to strings without explicit conversion, using an explicit format-mask!

Nonetheless, this should not be solved by adding the owning schema to the tablename, so yes, that looks like a bug as well Wink
Re: Whats wrong with query. [message #194773 is a reply to message #194732] Mon, 25 September 2006 04:22 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
ya but there is nothing wrong with date comparison. And this only happens when you use Oracle 9i against HTMLDB.
Re: Whats wrong with query. [message #194900 is a reply to message #194773] Tue, 26 September 2006 01:46 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
drop table t1

create table t1(eno number,ename varchar2(10))
/

insert into t1 values(10,'Abdul')
/

drop table t2

create table t2(ename varchar2(10))
/

insert into t2 values('Mustafa')
/

commit;

select * from t1 where eno in(select eno from t2)
/
eno ename
--- ------

10 Abdul


using ORACLE 10G

http://forums.oracle.com/forums/thread.jspa?messageID=1476200&#1476200 this is new...and i learnt what the problem was.

[Updated on: Tue, 26 September 2006 02:40]

Report message to a moderator

Re: Whats wrong with query. [message #194919 is a reply to message #194900] Tue, 26 September 2006 03:06 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Strange! but I think very well commented, check it out

http://free-advisory.com/forums/oracle/YaBB.pl?num=1159253336

Re: Whats wrong with query. [message #194972 is a reply to message #194919] Tue, 26 September 2006 06:11 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
yup that was me too there..
Re: Whats wrong with query. [message #195245 is a reply to message #194972] Wed, 27 September 2006 09:43 Go to previous messageGo to next message
oradev91
Messages: 3
Registered: September 2006
Junior Member
very strange. Thank you Liza, Thank you every one.

Regards.
Re: Whats wrong with query. [message #195332 is a reply to message #194688] Thu, 28 September 2006 01:39 Go to previous messageGo to next message
venkat02_504
Messages: 1
Registered: September 2006
Location: hyderabad
Junior Member

hi friend try this query

select * from emp
where hiredate=to_char('17/dec/1987');
Re: Whats wrong with query. [message #195384 is a reply to message #195332] Thu, 28 September 2006 06:01 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
venkat02_504 wrote on Thu, 28 September 2006 08:39

hi friend try this query

select * from emp
where hiredate=to_char('17/dec/1987');

And then what?
Count the number of errors in it?
Previous Topic: Replace
Next Topic: retrieving values from objects
Goto Forum:
  


Current Time: Sun Dec 11 08:25:42 CST 2016

Total time taken to generate the page: 0.10101 seconds