Forum Stats

  • 3,752,115 Users
  • 2,250,461 Discussions
  • 7,867,726 Comments

Discussions

Categories

How could I write a trigger to get the sum of values held in a different form?

User_6OSDL
User_6OSDL Member Posts: 4 Green Ribbon
edited Mar 30, 2021 6:37PM in SQL & PL/SQL

I'm new to PL/SQL and Oracle Form Builder so, pardon my ignorance. I have three relations DEBTOR, LIST and REGISTRATION. I have described these below:

DEPTOR- debtorID (PK) | firstname | surname | address | postcode

LIST- dCode (PK) | name | fee | description |

REGISTRATION- regID (PK) | debtorID* | dCode* | approvedBy

I have a non-database item called DEBT_FEE which display LIST.fee on my REGISTRATION form. I used a WHEN-NEW-ITEM-INSTANCE trigger to do this.

DECLARE
     V_FEE NUMBER(5, 2);
BEGIN
     SELECT FEE INTO V_FEE FROM LIST
     WHERE :REGISTRATION.DCODE = MODULE.DCODE;

     :DEBT_FEE:= V_FEE;
END;

I was wondering how I could do something similar to get the sum of all the debts the current debtor owes. Any help would be appreciated.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,923 Red Diamond

    Hi,

    Welcome to the Forum! Or rather: Welcome to the Forums, plural, since there are several. There's the SQL and PL/SQL forum, where you posted this question, and then there the Oracle Forms forum

    https://community.oracle.com/tech/developers/categories/forms

    Unfortunately, it's not always clear which forum is the best place for a given question, and it's wrong to post the same question in different places. Let's assume this really is a PL/SQL question, even though you happened to be using Oracle Forms when the problem came up.

    You already know the way to get a single fee using PL/SQL, that is:

    SELECT  fee
INTO 	v_fee
FROM 	list
WHERE  :registration.dcode = module.dcode;

    That gets the value of one fee. If, for some reason, you wanted to get the total of all fees, you could use the aggregate SUM function, like this:

    SELECT  SUM (fee)
INTO 	v_fee
FROM 	list;

    I suspect you need to do something similar, using whatever table contains the debts. What table is that? What column in that table identifies who owes the debt, and what column contains the amount of the debt?

    · Share on TwitterShare on Facebook
  • User_6OSDL
    User_6OSDL Member Posts: 4 Green Ribbon
    edited Mar 30, 2021 10:22PM

    @Frank Kulash Apologies for the vagueness of my question. The Registration table contains the details of all the loans people have taken. It contains the ID of debtor and the ID of the debt.

    The fee is kept in the debt table. I would like to know at any time what user1 for instance owes in total.


    Debtor

    user1 | James | Liden | address | postcode

    user2 | Janet | Bates | address | postcode

    List

    C01 | Banana Boat | 200.00 | Yellow

    C02 | Brown Socks | 400.00 | Brown

    Registration

    10000 | user1 | C01 | Harry

    20000 | user1 | C02 | Geroge

    20000 | user2 | C01 | Harry

    So if I'm on a user1 Registration record 10000 i.e., JAMES LIDEN. I want to know that he owes 600.00 in total and if I'm on a user2 REGISTRATION record i.e., JANET BATES record I want to know she owes 400.00 in total.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,923 Red Diamond

    Hi,

    Sorry, I don't follow how the tables are connected. Apparently, both of the rows in the list table are related to James Lidden, since his total is 600, and one of the rows is also related to Janet Bates, since her total is 400, but I don't see exactly how the tables are joined. It would also help if I knew the names of the columns.

    What are you given? For example, do you start with a number from the first column of the registration table (like 20000)? If the number is 20000, do you need to include both user1 and user2, since both are on a row with 20000?

    · Share on TwitterShare on Facebook
  • User_6OSDL
    User_6OSDL Member Posts: 4 Green Ribbon
    edited Mar 30, 2021 11:32PM
    image.png

    @Frank Kulash I've tried to remove as many redundant fields as to make more sense. I made a copy and paste mistake in my previous post in regards to the two 20000's the third row regID is 30000. Debtor and List are separate relations. They are connected to the registration relation through foreign keys within registration (As I have shown through my use of colour). I'm finding it difficult to write a trigger inside of my Registration form to display the total sum of the current user's fees, a display item. I am displaying only 1 registration record at one time. For each, I have displayed the Name and fee using triggers similar to the one in my first post. Hope that helps. Thank you

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,923 Red Diamond

    Hi, @User_6OSDL

    You still haven't explained what you're given to start with. If you have a variable (lets call it :debtorIDWanted) that contains a debtorID, then you can get the sum like this:

    SELECT  SUM (l.fee)
INTO 	v_fee
FROM 	registration  r
JOIN	list          l ON l.dCode = r.dCode
WHERE   r.debtorID = :debtorIDWanted;
    · Share on TwitterShare on Facebook
  • User_6OSDL
    User_6OSDL Member Posts: 4 Green Ribbon
    edited Mar 30, 2021 11:45PM

    The only thing I have to work with are the keys in Registration. Which is why I'm using the trigger to display the information I need from the List table. As I am only displaying a single record at one time. I am given that registrationID, the debtorID and the dCode (Debt Code). I'll give your answer a go.

    · Share on TwitterShare on Facebook