Hi all, I am not good in pl/sql and I know this code is not well structure but could just someone tell me what is wrong in this code? It returns just one value each time repeatedly instead of right values for each element. Where should I put return to have the right "area value" for my objects? Here is the code: " CREATE OR REPLACE FUNCTION AREA_CAL_1ST RETURN NUMBER IS -- constant declaration pi CONSTANT NUMBER := 3.141592654; -- other declarations P_ID NUMBER;--ID.PARAM%TYPE; P_Inner_boundary NUMBER;--Inner_boundary.PARAM%TYPE; P_Surface_Type VARCHAR2(4000);--Surface_Type.PARAM%TYPE; P_R_HEIGHT NUMBER(10,3);--R_HEIGHT.PARAM%TYPE; P_R_LENGTH NUMBER(10,3);--R_LENGTH.PARAM%TYPE; P_T_edgeA NUMBER(10,3);--T_edgeA.PARAM%TYPE; P_T_edgeB NUMBER(10,3);--T_edgeB.PARAM%TYPE; P_T_edgeC NUMBER(10,3);--T_edgeC.PARAM%TYPE; P_C_Central_ANGLE NUMBER(10,3);--C_Central_ANGLE.PARAM%TYPE; P_C_RADIUS NUMBER(10,3);--C_RADIUS.PARAM%TYPE; AREA_CAL NUMBER(10,3); p NUMBER(10,3); CURSOR param_cur IS SELECT "ID", "Inner_Boundary", "Surface_Type", "R_HEIGHT", "R_LENGTH", "T_edgeA", "T_edgeB", "T_edgeC", "C_Central_ANGLE", "C_RADIUS" FROM Param; BEGIN OPEN param_cur; LOOP FETCH param_cur into P_ID, P_Inner_boundary, P_Surface_Type, P_R_HEIGHT, P_R_LENGTH, P_T_edgeA, P_T_edgeB, P_T_edgeC, P_C_Central_ANGLE, P_C_RADIUS; EXIT WHEN param_cur%notfound; BEGIN CASE WHEN P_SURFACE_TYPE = 'Rectangle' THEN area_cal := P_R_HEIGHT * P_R_LENGTH; WHEN P_SURFACE_TYPE = 'Circle_segment' THEN area_cal := (P_C_Central_ANGLE * pi * POWER(P_C_RADIUS,2))/360; WHEN P_SURFACE_TYPE = 'Triangle' THEN P := (P_T_edgeA + P_T_edgeB + P_T_edgeC)/2; area_cal := SQRT(P * (P-P_T_edgeA) * (P-P_T_edgeB) * (P-P_T_edgeC)); ELSE area_cal := 0; DBMS_OUTPUT.PUT_LINE('No area was calculated'); END CASE; END; END LOOP; CLOSE param_cur; RETURN area_cal; END AREA_CAL_1ST; " Thanks in advance. Many

Hi Many, 1) Does this function compile ? 2) Even if it compiles (after some corrections), it will never be possible to return the "right value" for each element type. If I understand correctly, the table PARAM contains several rows of different surface types (Rectangle, Circle_segment, Triangle and others ...) and you need to calculate area for the 3 defined ones else return 0. The LOOP-FETCH will calculate area for each case and overwrite the value of the variable "area_cal " and the function will return the last calculated value each time. If you want to get the area for each surface type, in other words you want to return a "table", then you have to use a collection as the return type (not NUMBER). Please find here under a sample code you can adapt and use for your function: Code (SQL): CREATE OR REPLACE TYPE t_area AS VARRAY(4) OF NUMBER; / DECLARE l_surface_type VARCHAR2(100); l_area t_area := t_area(); CURSOR c_area IS SELECT 'Circle_segment' FROM dual UNION SELECT 'Rectangle' FROM dual UNION SELECT 'Triangle' FROM dual UNION SELECT 'ZOthers' FROM dual ORDER BY 1; BEGIN OPEN c_area; LOOP FETCH c_area INTO l_surface_type; EXIT WHEN c_area%notfound; l_area.extend; CASE WHEN l_surface_type = 'Circle_segment' THEN l_area(l_area.COUNT) := 10; WHEN l_surface_type = 'Rectangle' THEN l_area(l_area.COUNT) := 20; WHEN l_surface_type = 'Triangle' THEN l_area(l_area.COUNT) := 30; ELSE l_area(l_area.COUNT) := 0; END CASE; END LOOP; FOR i IN 1 .. l_area.COUNT LOOP DBMS_OUTPUT.PUT_LINE(l_area(i)); END LOOP; END; /

Hi Rajen, Thanks for your reply. I have a table called "param". It contains info about different geometries which can either "Rectangle" or "Triangle" or "Circle_segmet". It means for each row one of these geometries would be calculated so I am not sure if the collection would be necessary here or not. What I need is to calculate the area of each element and return the value which will be used in another function. do you have now any idea about the problem? I think I need a for loop from i ... n. right? and how can I define it? Best regards, Many

Hi Many, If you need to calculate the area of each element type (geometry) and return the values of the corresponding area to your calling program, then I don't see any other way than a collection. However, if you need the area of ONE element type at a time, then rather add an extra parameter to your function (p_element_type) and calculate it's area and return to main routine as a NUMBER (you code would be simpler - no CASE would then be required). Another question, can you have more than 1 row for 1 element type ? If yes, then you may need to sum the areas ? (I'm not sure about your requirement in this situation ...).

Maybe I am quite far from what I have in my mind. The main idea is: I have two tables one Param which has some attributes about geometries of the object "param". I have and another table topological attributes "topo". I have to calculate the area in the Param table and then populate the corresponding row another table. what I am trying to do now is: to create a function which can calculate area and the use this function to do some extra calculate (subtract the area of those object which has overlap based on "Inner_boundary") and then populate the second table. summarized it: I wanna define a function "area_cal" which behaves like built-in function "Sum" in the database. Regards, Many

Hi Many, Thanks for the info, but I feel it would be better if you could provide the following for us to have a clearer picture of what is required to correctly define the function "area_cal": 1) Structure of "PARAM" table 2) Sample data populated in "PARAM" table (INSERT statement with appropriate VALUES would be perfect) 3) Expected output of the "area_cal" function with regards to data provided in 2). Thanks for your co-operation.

sure. Thank you. Actually I wanna at the end have a table which has all information about the object on a building's facade. . 1) Code (SQL): CREATE TABLE "ANDREAS"."ABSTRACT_PARAMETRIC_SURFACE" ( "ID" NUMBER NOT NULL ENABLE, "AZIMUTH_ANGLE" NUMBER, "ROLL_ANGLE" NUMBER, "TILT_ANGLE" NUMBER, "SURFACE_TYPE" VARCHAR2(4000 BYTE), "DESCRIPTION" VARCHAR2(4000 BYTE), "R_HEIGHT" NUMBER(10,3), "R_WIDTH" NUMBER(10,3), "T_EDGE_A" NUMBER(10,3), "T_EDGE_B" NUMBER(10,3), "T_EDGE_C" NUMBER(10,3), "C_CENTRAL_ANGLE" NUMBER, "C_RADIUS" NUMBER(10,3), "MP_COLUMNS" NUMBER(*,0), "MP_COLUMN_SPACING" NUMBER(10,3), "MP_ROWS" NUMBER(*,0), "MP_ROW_SPACING" NUMBER(10,3), "FK_CITYOBJECT_ID" NUMBER, "FK_ELEMENT" NUMBER, "FK_REFERENCE_POINT" NUMBER, "FK_INNER_BOUNDARY" NUMBER, "FK_SURFACE_MEMBER" NUMBER, CONSTRAINT "ABSTRACT_PARAMETRIC_SURFA_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING TABLESPACE "USERS" ENABLE, CONSTRAINT "ABST_PARAM_SURFACE_FK1" FOREIGN KEY ("FK_INNER_BOUNDARY") REFERENCES "ANDREAS"."ABSTRACT_PARAMETRIC_SURFACE" ("ID") ENABLE, CONSTRAINT "ABST_PARAM_SURFACE_FK2" FOREIGN KEY ("FK_ELEMENT") REFERENCES "ANDREAS"."ABSTRACT_PARAMETRIC_SURFACE" ("ID") ENABLE, CONSTRAINT "ABST_PARAM_SURFACE_FK3" FOREIGN KEY ("FK_SURFACE_MEMBER") REFERENCES "ANDREAS"."ABSTRACT_PARAMETRIC_SURFACE" ("ID") ENABLE, CONSTRAINT "ABST_PARAM_SURF_CITYOBJECT_FK1" FOREIGN KEY ("FK_CITYOBJECT_ID") REFERENCES "ANDREAS"."CITYOBJECT" ("ID") ENABLE, CONSTRAINT "ABST_PARAM_SURF_GM_POINT_FK2" FOREIGN KEY ("FK_REFERENCE_POINT") REFERENCES "ANDREAS"."GM_POINT" ("ID") ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" ; ** Hint: 1.1)each row is representing only one geometry which means either Triangle or Rectangale or Circle_segment. SO when: SURFACE_TYPE = Rectangle --> only R_HEIGHT and R_WIDTH have values/ SURFACE_TYPE = Triangle --> only T_EDGE_A, T_EDGE_B and T_EDGE_C have values and so 1.2) "ID" has unique value which shows the object id 1.3)"Inner_Boundary" is a forein-key which might be null or when the object is located inside another object the object should have the Id number of the bigger object. e.g. first rectangle shows the boundary of a facade: ID =1, Inner_boundary = NULL Second rectangle shows a windows on the facade: ID= 2, Inner_Boundary= 1 --> it means this object is located in the object with ID '1'. 2) INSERT INTO (ID, R_HEIGHT, R_WIDTH, FK_INNER_BOUNDARY) VALUES (1, 4.65, 8.5, 'NULL' ); INSERT INTO (ID, R_HEIGHT, R_WIDTH, FK_INNER_BOUNDARY) VALUES (2, 1.2, 1.8, 1); INSERT INTO (ID, T_EDGE_A; T_EDGE_B, T_EDGE_C, FK_INNER_BOUNDARY) VALUES (3, 1.5, 1.4, 1.6, 1); 3) I wanna define a function which gives me the area for each object. [It should be one value per object or per row] For example I wanna later use this function like: Code (SQL): SELECT ID, SUM(area_cal) AS area FROM ABSTRACT_PARAMETRIC_SURFACE WHERE ID=INNER_BOUNDARY AND INNER_BOUNDARY IS NOT NULL; Thank you for your help. Regards, Many

Hi Many, Thanks. This definitely gives a better idea. Here's a function which can calculate the area (based on the formula you used in your 1st post) - hope I'm closer in the understanding of your needs: Code (SQL): CREATE OR REPLACE FUNCTION f_calc_area( p_id IN NUMBER) RETURN NUMBER IS -- constant declaration pi CONSTANT NUMBER := 3.141592654; -- other declarations l_id NUMBER; l_fk_inner_boundary NUMBER; l_surface_type VARCHAR2(4000); l_r_height NUMBER(10,3); l_r_width NUMBER(10,3); l_t_edge_a NUMBER(10,3); l_t_edge_b NUMBER(10,3); l_t_edge_c NUMBER(10,3); l_c_central_angle NUMBER(10); l_c_radius NUMBER(10,3); area_cal NUMBER(10,3); p NUMBER(10,3); CURSOR cur_param IS SELECT id, fk_inner_boundary, surface_type, r_height, r_width, t_edge_a, t_edge_b, t_edge_c, c_central_angle, c_radius FROM abstract_parametric_surface WHERE id = p_id; BEGIN OPEN cur_param; LOOP FETCH cur_param INTO l_id, l_fk_inner_boundary, l_surface_type, l_r_height, l_r_width, l_t_edge_a, l_t_edge_b, l_t_edge_c, l_c_central_angle, l_c_radius; EXIT WHEN cur_param%notfound; BEGIN CASE WHEN l_surface_type = 'Rectangle' THEN area_cal := l_r_height * l_r_width; WHEN l_surface_type = 'Circle_segment' THEN area_cal := (l_c_central_angle * pi * POWER(l_c_radius,2))/360; WHEN l_surface_type = 'Triangle' THEN p := (l_t_edge_a + l_t_edge_b + l_t_edge_c)/2; area_cal := SQRT(p * (p-l_t_edge_a) * (p-l_t_edge_b) * (p-l_t_edge_c)); ELSE area_cal := 0; DBMS_OUTPUT.PUT_LINE('No area was calculated'); END CASE; END; DBMS_OUTPUT.PUT_LINE('Surface type:'||l_surface_type||' => area :'||area_cal); END LOOP; CLOSE cur_param; RETURN area_cal; END f_calc_area; / You can then eventually call it to calculate the area of your facade [ assuming you use the following rule: area of facade = area of "main" object - sum of areas of inner objects ]: Code (SQL): SELECT ID, f_calc_area(ID)- (SELECT SUM(f_calc_area(ID)) FROM abstract_parametric_surface t_sub WHERE t_sub.fk_inner_boundary = t_main.ID GROUP BY t_sub.fk_inner_boundary ) facade_area FROM abstract_parametric_surface t_main WHERE fk_inner_boundary IS NULL; I've used the following data to test: Code (SQL): INSERT INTO ABSTRACT_PARAMETRIC_SURFACE(ID, SURFACE_TYPE,R_HEIGHT, R_WIDTH, FK_INNER_BOUNDARY) VALUES (1, 'Rectangle',4.65, 8.5, NULL ); INSERT INTO ABSTRACT_PARAMETRIC_SURFACE(ID, SURFACE_TYPE,R_HEIGHT, R_WIDTH, FK_INNER_BOUNDARY) VALUES (2, 'Rectangle',1.2, 1.8, 1); INSERT INTO ABSTRACT_PARAMETRIC_SURFACE(ID, SURFACE_TYPE,T_EDGE_A, T_EDGE_B, T_EDGE_C, FK_INNER_BOUNDARY) VALUES (3, 'Triangle',1.5, 1.4, 1.6, 1); INSERT INTO ABSTRACT_PARAMETRIC_SURFACE(ID, SURFACE_TYPE,R_HEIGHT, R_WIDTH, FK_INNER_BOUNDARY) VALUES (4, 'Rectangle',5.57, 9.5, NULL ); INSERT INTO ABSTRACT_PARAMETRIC_SURFACE(ID, SURFACE_TYPE,R_HEIGHT, R_WIDTH, FK_INNER_BOUNDARY) VALUES (5, 'Rectangle',1.8, 1.2, 4); INSERT INTO ABSTRACT_PARAMETRIC_SURFACE(ID, SURFACE_TYPE,R_HEIGHT, R_WIDTH, FK_INNER_BOUNDARY) VALUES (6, 'Rectangle',0.8, 1.1, 4); INSERT INTO ABSTRACT_PARAMETRIC_SURFACE(ID, SURFACE_TYPE,T_EDGE_A, T_EDGE_B, T_EDGE_C, FK_INNER_BOUNDARY) VALUES (7, 'Triangle',1.2, 1.4, 1.1, 4); And the results are: Code (SQL): ID FACADE_AREA ---------- ----------- 4 49.238 1 36.399 Hope we're closer to the target :hurray

Dear Rajen, Thank you so much. Your help is super great for me. I just have one question. Is it possible to cal a function like Code (SQL): SELECT p2.ID, p1.F_CALC_AREA(ID) FROM table1 p1, table2 p2 WHERE ...... ; Apparently it is not possible though. Anyway... Thank you so much for your great help and patient as well

Dear Many, Pleasure to assist . It is possible, there's just a little mistake in your call - you cannot precede a stored function with a table alias : Code (SQL): SELECT p2.ID, F_CALC_AREA(p1.ID) FROM table1 p1, table2 p2 WHERE ...... ; I suppose that's what you're trying to achieve: calculate area for ID from table1.