Home » SQL & PL/SQL » SQL & PL/SQL » Numeric data from Character colomn and filter again with condtion (oracle 8i)
Numeric data from Character colomn and filter again with condtion [message #360979] Mon, 24 November 2008 09:08 Go to next message
satishk
Messages: 26
Registered: October 2008
Location: HYD
Junior Member
Hi,

I have an issue with Sql Query, i have written the same some extent.

Aim : I am trying to fetch the Numeric data from a Character column and filter with some numeric condition.

I have written the same up to filter the numeric data, the problem is how to filter data again the resultant query with numeric operations.

Problem Facing :

Invalid Number... Error....

Test Case:

create table KSK_TEST_Q AS
( select INVENTORY_ITEM_ID,
ELEMENT_NAME,
ELEMENT_VALUE,
ITEM_CATALOG_GROUP_ID from (select cc.* ,ltrim(translate(cc.element_value,'0123456789.0123456789',' '))
res from mtl_descr_element_values_v cc where element_value <>'.' and rownum <100 ) where res is null or res = '0')


BEGIN
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (2794,'Memory Supplied','SATIH123',93);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (167,'Diameter','123SEFG',33);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (167,'Thread','123ASDF1234213',33);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (482,'Thread','32',33);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (484,'Diameter','6',33);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (484,'Thread','28',33);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (247,'Size','1232130.90',115);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (247,'Size','123213.WER234',113);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (247,'Size','17',31);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (225,'Size','TASDRTS',115);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (225,'Size','23423',113);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (225,'Size','5665.09',31);
Insert into KSK_TEST_Q (INVENTORY_ITEM_ID,ELEMENT_NAME,ELEMENT_VALUE,ITEM_CATALOG_GROUP_ID) values (199,'Size','89.00',115);
END;


Case 1 :

-- fine
select * from ( select * from (select cc.* ,ltrim(translate(cc.element_value,'0123456789.0123456789',' '))
res from KSK_TEST_Q cc where element_value <>'.' ) where res is null or res = '0' )
where element_value = '32'

Case 2:

-- error Invalid Error --
select * from ( select * from (select cc.* ,ltrim(translate(cc.element_value,'0123456789.0123456789',' '))
res from KSK_TEST_Q cc where element_value <>'.' ) where res is null or res = '0' )
where element_value = 32 ;


Case 3:

-- error Invalid Error -- applied to_number to value --
select * from ( select * from (select cc.* ,ltrim(translate(cc.element_value,'0123456789.0123456789',' '))
res from KSK_TEST_Q cc where element_value <>'.' ) where res is null or res = '0' )
where to_number (element_value) = 32 ;


Note : It was searching the value entire table KSK_TEST_Q, not in the resultant set... How can we handle the same on the resultant set only...

Please help on this... and let me know if information is required......

I need to develop the same with single query only ( not allowed to use procedures/blocks ).


Thanks,
Pavan K
Re: Numeric data from Character colomn and filter again with condtion [message #360982 is a reply to message #360979] Mon, 24 November 2008 09:16 Go to previous messageGo to next message
satishk
Messages: 26
Registered: October 2008
Location: HYD
Junior Member
Hi,

Please use the same for Table creation..........

CREATE TABLE KSK_TEST_Q
(INVENTORY_ITEM_ID NUMBER,
ELEMENT_NAME VARCHAR2(2000),
ELEMENT_VALUE VARCHAR2(2000),
ITEM_CATALOG_GROUP_ID NUMBER
)

Thanks................
Pavan
Re: Numeric data from Character colomn and filter again with condtion [message #360987 is a reply to message #360982] Mon, 24 November 2008 09:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is caused by oracle unnesting your nested queries.

You can get round this by including ROWNUM as a column in the inner query - this prevents oracle from unnesting the sub-query:
-- error Invalid Error --
select * from ( select * from (select cc.*,rownum ,ltrim(translate(cc.element_value,'0123456789.0123456789',' '))
res from KSK_TEST_Q cc where element_value <>'.' ) where res is null or res = '0' )
where element_value = 32 ;


-- error Invalid Error -- applied to_number to value --
select * from ( select * from (select cc.* ,rownum,ltrim(translate(cc.element_value,'0123456789.0123456789',' '))
res from KSK_TEST_Q cc where element_value <>'.' ) where res is null or res = '0' )
where to_number (element_value) = 32 ;


Ps Thanks for the testcase - made fixing your code a doddle.
Re: Numeric data from Character colomn and filter again with condtion [message #360993 is a reply to message #360987] Mon, 24 November 2008 09:53 Go to previous message
satishk
Messages: 26
Registered: October 2008
Location: HYD
Junior Member
Hi JRowbottom,

Thanks you very much for your swift response....

I will check and get back to you.. If any concerns.

Thanks,
Pavan K
Previous Topic: Date Bug in Oracle ?
Next Topic: PL/SQL Reflection (merged)
Goto Forum:
  


Current Time: Sat Dec 03 22:06:45 CST 2016

Total time taken to generate the page: 0.04710 seconds