1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

The function returns only one value repeatedly

Discussion in 'SQL PL/SQL' started by many_yammy, May 25, 2014.

  1. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    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
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    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;
    /
     
     
  3. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    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
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Function should return single value. But in your code area_cal may contains multiple values.
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    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 ...).
     
  6. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    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
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    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.
     
  8. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    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
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    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
     
    many_yammy likes this.
  10. many_yammy

    many_yammy Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    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 :)
     
  11. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    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.
     
    many_yammy likes this.