Ask Your Question

how to take the results of a calulation (alias) and store it into a field in a table

asked 2020-10-30 04:55:52 +0200

Panchopar gravatar image

I want to know how to take the result of a calculation ,which is a alias of a summed field, and save it to a field in a table?

edit retag flag offensive close merge delete


@Panchopar: Are you summing several field values within each row/record. Or aggregating over many records (and storing in a different table)?

frofa gravatar imagefrofa ( 2020-10-30 09:14:19 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-10-30 09:21:18 +0200

frofa gravatar image

updated 2020-10-31 09:13:29 +0200

See additional question in below COMMENTS.

edit flag offensive delete link more

answered 2020-10-30 17:15:25 +0200

Ratslinger gravatar image


This seems to be a continuation of this question:

how to use the total of a sum function as part of another calulation in libreoffice base

So starting with the last question, it was noted to use the alias in a select you could do this with an embedded select.

Simple calculation with alias:

image description

Trying to use that alias at the same level (not embedded) results as an error - not found:

image description

If the alias is embedded in another select it can be used in the upper level:

image description

Now your current question is very different from what has just been discussed. An update is done using the value. This can be a fixed value, a value of another field or the value of a calculation. In HSQLDB embedded it matters not if the calculation has an alias or not (other databases may be different). The update is looking for a value:

image description

Whether the alias P1 is present or not, the result is the same.

edit flag offensive delete link more


It also occurs to me you may be looking to do this on a regular basis with every record. This will take more thought process on your part and will require you to use either macros (must be specific to your needs) or possibly triggers (see you DB documentation for this).

MySQL also has calculated fields but this may not fit your needs (again see documentation).

If this is the case, you will need to consider all possible situations and not just at entry of a new record.

Ratslinger gravatar imageRatslinger ( 2020-10-31 05:21:51 +0200 )edit

I am doing this for every new record entered. (Trying to do it), I am using mysql as my backend and Libre office base as my front end. In the old days dBase lll did this easy. Setting up variables was also eazy ,doing that in LO Base ,not so eazy. Thanks for all the help. I will study this.

Panchopar gravatar imagePanchopar ( 2020-10-31 07:13:43 +0200 )edit

My question (original) seems unanswered - i.e. does the calculation ONLY involve other values from the current table record - maybe a TRIGGER (as Ratslinger suggests) would suffice in this case. More info about your database schema (structure) would help (at least me). Maybe you might be able to give us a step-by-step description of WHAT you actually do 'by hand' where you say... I am doing this for every new record entered. (Trying to do it)... That does sound like to me that you ONLY doing the manual update for NEW records?

Another question: do you really need to store this information in your table? It seems like a lot of extra work whichever way you look at it. Why couldn't this info just be 'calculated' by a suitable SQL query whenever you need it?

frofa gravatar imagefrofa ( 2020-10-31 08:43:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-10-30 04:55:52 +0200

Seen: 80 times

Last updated: Oct 31 '20