Re: Creating an index on a view

Tony

I realise in the example that I have given I could have used the view, but I am not really as stupid as that :-)

In reality the function that I need to index is as follows:

CREATE OR REPLACE FUNCTION GET_DATA_ITEM_LIST (nQueryID IN NUMBER) RETURN VARCHAR2 IS strDataItemList VARCHAR2(4000);

	CURSOR DataItemsCursor IS
	SELECT DISTINCT(DECODE(QF.ITEMTYPE, 1, CF.DESCRIPTION, 2, 'Test: ' ||
TR.TESTNAME, NULL)) DESCRIPTION
	FROM TABLE1 QF, TABLE2 CF, TABLE3 TR
	WHERE QF.QUERYID = nQueryID
	AND (CF.ID = QF.FIELDID OR TR.ID = QF.FIELDID)
	AND CF.SAMPLEDATAID <> -1
	AND (CF.ID <> -1
	OR TR.ID <> -1)
	ORDER BY UPPER(DESCRIPTION);


	strItem		VARCHAR2(50);

BEGIN         strDataItemList := '';

        FOR DataItems IN DataItemsCursor LOOP

                strItem := DataItems.DESCRIPTION;

                IF LENGTH(strDataItemList) > 0 THEN

                        strDataItemList := strDataItemList || ', ';

                END IF;                 strDataItemList := strDataItemList || strItem;

        END LOOP; RETURN strDataItemList;

END GET_DATA_ITEM_LIST;
/

I had tried to provide a simple example but realise that the example I provided certainly wasn't the best.

So again, back to anybody who can help.

Thanks

Paul Received on Thu Oct 13 2005 - 10:59:50 CDT