OraFAQ Forum: SQL & PL/SQL » Query Error

Home » SQL & PL/SQL » SQL & PL/SQL » Query Error - Invalid Type

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Error - Invalid Type [message #21558]

Tue, 13 August 2002 07:46

Richard McBride
Messages: 10
Registered: August 2002

Junior Member

I have two tables

SQL> desc xxx_lists;

LISTID NOT NULL NUMBER(10)
LISTNAME NOT NULL VARCHAR2(50)

SQL> desc xxx_items;

LISTID NOT NULL NUMBER(10)
ITEMID NOT NULL NUMBER(10)
ITEMNAME NOT NULL VARCHAR2(60)
ITEMEXT VARCHAR2(10)

When I execute the query

SELECT itemname
FROM xxx_items, xxx_lists
WHERE xxx_lists.listname = 'Tempe'
AND xxx_lists.listid = xxx_items.listid
AND to_number(xxx_items.itemext) LIKE to_number(777);

in environment A it executes fine but in environment B which has different data I get the error

AND to_number(xxx_items.itemext) LIKE to_number(639)
*
ERROR at line 5:
ORA-01722: invalid number

I thought that there may be some characters in the field xxx_items.itemext so I executed the query

select to_number(xxx_items.item_abbr)
FROM xxx_items, xxx_lists
WHERE xxx_lists.listname = 'Tempe'
AND xxx_lists.listid = xxx_items.listid

and this worked fine in both environments.

Lastly I truncated the table in Environment B and imported the table from environment A into it and I get the same error - what could possibly be wrong? Would really appreciate your help.

Thanks

Rich

Report message to a moderator

Re: Query Error - Invalid Type [message #21564 is a reply to message #21558]

Tue, 13 August 2002 09:25

ram kumar
Messages: 113
Registered: August 2002

Senior Member

SELECT itemname
FROM xxx_items, xxx_lists
WHERE xxx_lists.listname = 'Tempe'
AND xxx_lists.listid = xxx_items.listid
AND xxx_items.itemex LIKE '%777%';

try out the above statement and let me know..

Report message to a moderator

Re: Query Error - Invalid Type [message #21566 is a reply to message #21564]

Tue, 13 August 2002 12:59

Richard McBride
Messages: 10
Registered: August 2002

Junior Member

Yah that query worked for me as well. Problem is when you put to_number around the field xxx_items.itemex as shown

SELECT itemname
FROM xxx_items, xxx_lists
WHERE xxx_lists.listname = 'Tempe'
AND xxx_lists.listid = xxx_items.listid
AND to_number(xxx_items.itemex) LIKE '%777%'

This results in the error

*
ERROR at line 5:
ORA-01722: invalid number

The result is different for identical data and tables in two seperate environments. Does the indexing affect this?

Thanks

Rich

Report message to a moderator

Re: Query Error - Invalid Type [message #21568 is a reply to message #21566]

Tue, 13 August 2002 15:23

Richard McBride
Messages: 10
Registered: August 2002

Junior Member

I found out that the problem is due to different execution plans being generated based on different situations...

A TO_NUMBER should not be used against a column
that can contain numerics.

If I force a rule mode into this query

SELECT /*+ RULE */ itemname
FROM xxx_items, xxx_lists
WHERE xxx_lists.listname = 'Tempe'
AND xxx_lists.listid = xxx_items.listid
AND to_number(xxx_items.itemex) LIKE TO_NUMBER(777)

this query executes

Hope this helps you folks

Rich

Report message to a moderator

Previous Topic:	COPY command in PL/SQL
Next Topic:	read text file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 07:41:11 CDT 2024