Create view using function help please

[geerae]

I've created a function (get_lob) to return a value which I'm trying to use in a view. My view has about 35,000 records and only the last record has a value returned from the function, the rest are null. Any ideas? See below for the jest of the view script.

CREATE OR REPLACE FORCE VIEW TEST_VIEW
(ASSET_ID, ASSET_CLASS, LINE_O_BIDNUSS)
AS
SELECT obj_code, obj_class, get_lob (obj_code)
FROM r5objects
WHERE obj_obrtype = 'A';

Thanks for any help,
geerae

 

[jee]

Does executing this CREATE VIEW statement result in an error? If so, what is the code?

 

[geerae]

There is no errors running the create view statement listed above.

 

[carp]

If you just run the query, how many rows do you get? Just because there are 35k rows in the table doesn't mean they all match your WHERE clause.

 

[geerae]

There are 37,179 records if I run the query. If I run the function seperately passing the parameter there are 3,797 records.
When adding the function to the view it should populate 3,797 of the 37,179 records with the value but the funtion only seems to return a value on the last record of the view.

 

[Turkbear]

Hi, I'm confused..How does using the function to get a column value for LINE_O_BIDNUSS affect how many ROWS will be returned when that column is not referenced in the WHERE clause?

It does not appear that obj_obrtype is changed by the function...

 

[geerae]

I'm just trying to return LINE_O_BIDNUSS as a column in the view using the return value from the function. The obj_obrtype is just another column in the r5objects table, it has nothing to do with the function.

Does this answer your question?

 

[Turkbear]

Hi,
Thanks, can you post the function code?

 

[geerae]

I've got the function returning a value in the view but only when I add the function to the 'WHERE' statement: see below.

CREATE OR REPLACE FORCE VIEW MP5DEV.MY_VIEW
(ASSET_ID, ASSET_CLASS, LINE_O_BIDNUSS)
AS
SELECT obj_code, obj_class, get_lob (obj_code)
FROM r5objects
WHERE obj_obrtype = 'A'
AND get_lob(obj_code) IS NOT NULL;

The function is:

CREATE OR REPLACE FUNCTION get_lob (
p_object IN r5objects.obj_code%TYPE
)
RETURN NUMBER
IS
v_lob NUMBER (3);
v_class r5objects.obj_class%TYPE;

CURSOR c1
IS
SELECT prv_value
FROM r5propertyvalues
WHERE prv_property = 'LOB'
AND prv_code = p_object || '#*';

CURSOR c2
IS
SELECT asset_lob
FROM r5objects, kes_assettype
WHERE obj_category = asset_series
AND obj_class = asset_class
AND obj_code = p_object;
BEGIN
SELECT obj_class
INTO v_class
FROM r5objects
WHERE obj_code = p_object;

IF v_class = 'RIG'
THEN
OPEN c1;
FETCH c1 INTO v_lob;
CLOSE c1;
ELSE
OPEN c2;
FETCH c2 INTO v_lob;
CLOSE c2;
END IF;

RETURN v_lob;
END get_lob;
/