oracle application 11i


    SQL Query

    Share

    satish k

    Posts : 2
    Join date : 2008-09-25

    SQL Query

    Post  satish k on Mon Nov 24, 2008 8:09 am

    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

      Current date/time is Sat Sep 23, 2017 5:39 pm