Make a SQL statement to retrieve a value within ExtendedData

Marsh_USA

Hi,For examplet, lets say that I have a custom node, and it has a field named "Office" which is stored in the extendedData column. How can I retrieve a list of all items that have Office = "Toronto" within the ExtendedData Column.Thanks.

Cynthia_Philpott

Hello Marsh,Given the fact that the extendedData column is declared as a long it makes it extremely difficult to search for values. Here is how we kinda got around it. Created a pl/sql function that when given the following (dataid, string to search) it will search through the extended data column.Basically we use a livereport which calls this function and gives it a (data id and string).The sql looks as followsLive Report codeselect d.* from dtree d where subtype = '' and '1' = (select findvaluefromextend(d.dataid,) from dual;The function codeCREATE OR REPLACE FUNCTION findvaluefromextend( recdataid IN VARCHAR2, STRING IN VARCHAR2)RETURN VARCHAR2IS vextended VARCHAR2 (10000); vstring VARCHAR2 (500);BEGIN SELECT extendeddata INTO vextended FROM dtree WHERE dataid = recdataid; IF INSTR (UPPER (vextended), UPPER (vstring)) > 1 THEN RETURN 1; ELSE RETURN 0; END IF;END findvaluefromextend;/