How to add field1 to field2 and give result to field3

English
, , ,
#1

I have tried using the + sign in sql to add 3 feilds together, but it doesn’t give any results, and also tried the same in the query edit window on a new field after watching a youtube video on how to do what I want, but it didn’t work for me.
In sql I have
“Cost1” + “DelCost1” + “VAT1” AS “TotalCost1” FROM “tblRecInfo” this doesn’t give any results.

as well as this
“Cost1”, “DelCost1” ,“VAT1” (“Cost1” + “DelCost1” + “VAT1”) AS “TotalCost1” FROM “tblRecInfo” but that gives me a syntax error.

I have also looked in the HSQLDB User Guide but can’t find what I am looking for.

Thanks but it doesn’t give me any results

This is the whole SQL in the query I am using. Due to me not knowing about the data types being set correctly when I copied from Access database, everything was set to VARCHAR, so I copied some of the fields and added a 1 at the end so I could set the correct data type as currency etc.

SELECT “ID” AS “ID”, “BuyDate” AS “BuyDate”, “Item” AS “Item”, “Quantity” AS “Quantity”, “SupplierName” AS “SupplierName”, “TotalCost” AS “TotalCost”, “OrderID” AS “OrderID”, “SupplierPhone” AS “SupplierPhone”, “SupplierEmail” AS “SupplierEmail”, “Notes” AS “Notes”, “SupplierAddress” AS “SupplierAddress”, “EbaySale1”, “Quantity1”, “Cost1”, “DelCost1”, “TotalCost1” AS “Item”, “Cost1” * .2 AS “VAT1”, “Cost1” + “DelCost1” + “VAT1” AS “Cost1” + “DelCost1” + “VAT1” AS “TotalCost1” FROM “tblRecInfo”

One thing; I have VAT1 set to decimal 2 places and currency in tblRecInfo, but it doesn’t show as currency in the query, so I set it as currency again but it cannot be saved.

Cost1 Vat1 and DelCost1 are all set as decimal 2 places, and are in both the table and query. There is data in the table for Cost1 and DelCost1 but not VAT1, and the query has data for all 3 fields.

I have ran it from both SQL and the query, and gives the same result of no results.

I think I know now why no results, is because the VAT1 data is not in the table, and I have just entred a couple of VAT costs in the table and there are results now, DOH!

@Ratslinger , I am getting results now as I said before I did not put any data in the VAT1 field in the table.
It must be a thing with base that it reads from a table and not a query, does it?

This is what I used for the total cost1 :
SELECT “Cost1” + “DelCost1” + “VAT1” AS “TotalCost1” FROM “tblRecInfo”
and it works ok

This is what I used to calculate the VAT : “Cost1” * .2 AS “VAT1”
And that also works ok

I wasn’t getting any results because I did not know or realise that all the required data had to be entered into the table and not the query.

Ok if it, VAT1 was to be calculated in the query, which was my original requirement, and it was, why then did running the query not update the TotalCost1 fields.

#28

Your SQL is a bit messy but as I see it you are creating an alias field VAT1 from “Cost1” * .2. You are then using VAT1 in the same query for “Cost1” + “DelCost1” + “VAT1” to be “TotalCost1”. This is not possible as the source for the query is tblRecInfo and VAT1 does not exist in this table. You have to break it into what are in effect two queries combined.

@Ratslinger shows this in

SELECT A.*, A."Cost1" + A."DelCost1" + A."VAT1" AS "TotalCost1" FROM (SELECT "tblRecInfo".*, "Cost1" * .2 AS "VAT1" FROM "tblRecInfo")A

The second part, the SELECT in brackets, creates the VAT1 alias in the alias table A. The first part uses the VAT1 from the table A which is created by the second part, using A.“VAT1”. The table A contains all the fields from tblRecInfo as it has SELECT "tblRecInfo2.* This is assuming that VAT1 is not in the tblRecInfo table.

#29

@peterwt You have brought up a mistake on my part, the duplication of “VAT1” which will cause a problem (it’s been a trying question). I will correct my answer - Thank You much!

#2

The HSQLDB guide is difficult to decipher for someone who doesn’t understand SQL. Please at least glance through the LO Base documentation especially Chapter 5 - Queries (click here). It is not very long and provides many of the basics for query construction.

If "Cost1", "DelCost1", "VAT1" are fields in table (or other query) tblRecInfo then :

SELECT "Cost1" + "DelCost1" + "VAT1" AS "TotalCost1" FROM "tblRecInfo"

will work. If this doesn’t work, please provide entire SQL statement being used. If you get an error, don’t just say you got an error, relay what the error states as this indicates what the cause might be.

Edit:

Based upon the SQL in your question, the following statement should give you the wanted result without anything in the table for “Vat1”:

SELECT A.*, A."Cost1" + A."DelCost1" + A."VAT2" AS "TotalCost1" FROM (SELECT "tblRecInfo".*, "Cost1" * .2 AS "VAT2" FROM "tblRecInfo")A

If this answers your question please click on the :heavy_check_mark: (upper left area of answer).

Edit - to explain edit as requested.

As stated in the comments area, you cannot use one calculated field in another calculated field within the same select. This is because a newly calculated field is not there until the statement is run. The statement can be looked at in two parts:

SELECT A.*, A."Cost1" + A."DelCost1" + A."VAT2" AS "TotalCost1" FROM

and

(SELECT "tblRecInfo".*, "Cost1" * .2 AS "VAT2" FROM "tblRecInfo")A

The second part runs first and creates a temp result set named A containing all the fields in table tblRecInfo plus a newly calculated field named VAT2.

The first SELECT part can now use this information but it must refer to it as table A (temporary result set). The first part selects all fields in table A (which is really all fields from tblRecInfo PLUS the calculated field VAT2). NOW VAT2 is available to use in another calculation so it is used to create the calculated field TotalCost1.

Putting the two together results in ALL the fields from tabletblRecInfo PLUS the calculated field VAT2 PLUS the calculated field TotalCost1.

The part 2 temp name is arbitrary and can be what is meaningful to you.

Again, If this answers your question please click on the :heavy_check_mark: (upper left area of answer).

#3

It is still not clear as to the field ‘type’ of "Cost1", "DelCost1", "VAT1". Are they still ‘varchar’? If you are not getting ANY records returned from this query are you certain there is data in tblRecInfo? If there are records in the table you should be getting some result. Are you running this from Queries or SQL?

#4

@Avvy65 Please use comments to respond. Appending a comment to a question or answer transmits the entire thing over again and also updates placement in the Forum. Limit editing question to necessary clarification of the actual question.

Try this as a query:

SELECT "Cost1" FROM "tblRecInfo"

If you get no results, you are not relaying something important. In that case it would be best if you could post a sample of your table (no personal/confidential info).

#5

The SQL statement provided will produce a syntax error because of:

"Cost1" + "DelCost1" + "VAT1" AS "Cost1" + "DelCost1" + "VAT1" AS "TotalCost1"

Also, using a blank or NULL field in a calculation won’t work. It’s not ‘…a thing with base…’ but SQL which is why if you are using Base, you need to understand at least the basics of SQL.

#6

Another point, if you create an output based upon a calculation ("Cost1" * .2 AS "Net") that result cannot be immediately used in the another calculation as you have tried. This requires a SELECT FROM SELECT type statement.

#7

No. “VAT1” did not have to be in the Table if it was to be calculated in the query as my edit in the answer reflects.

#8

Please use comments to respond.

#9

I was told earlier by someone to put my comments etc in my question, but I’ll respond here as well.

Ok if it, VAT1 was to be calculated in the query, which was my original requirement, and it was, why then did running the query not update the TotalCost1 fields.

#10

Yeah, I see that earlier post about comments. You should always use comments to respond unless you need to post a file or picture (cannot be done in a comment). You can also edit a question or answer to add clarity or further definition.

As for your VAT1 question, I have already answered that in my comments and edited answer. Did you not read them?

#11

Well there are a number of comments you made:
This one I didn’t use
“Cost1” + “DelCost1” + “VAT1” AS “Cost1” + “DelCost1” + “VAT1” AS “TotalCost1”

This one I didn’t try due to having to change something just for 1 field, and then change back again
SELECT “Cost1” FROM “tblRecInfo”

And this one I did indeed use the SELECT statement, I just did not put in my earlier post
(“Cost1” * .2 AS “Net”)

So which one of the above would update the TotalCost1 field from running the query which has the VAT1

#12

None of the above! Please read what is written. Your first statement (in above comment) came from YOUR query statement in your question & said it wouldn’t work. It also mentioned not using a blank or NULL field. The second was to get you working (you still had nothing at that time). The third gets to the problem; it is example of using calculated field in calculation. It also stated in order to do that you needed a SELECT FROM SELECT. That part is in the Edit portion of my answer.

#13

Just noticed, nothing stated here will UPDATE TotalCost1 field. It will output a query with a TotalCost1 resulting field but it updates nothing. Updating is a whole different story.

#14

Do you mean this statement, and if so what is A.

SELECT A., A.“Cost1” + A.“DelCost1” + A.“VAT1” AS “TotalCost1” FROM (SELECT “tblRecInfo”., “Cost1” * .2 AS “VAT1” FROM “tblRecInfo”)A

#15

Yes. This is why it is important for you to learn some SQL. It is fairly simple. As the explanation is larger than comment allows, I will append to answer.

#16

Would you mind telling me what the A is for or means in the above SELECT statement

#17

Would you mind telling me what the A is for or means in the above SELECT statement - was it not clear in the answer?

#18

From the answer “…a temp result set named A …” & " The part 2 temp name is arbitrary and can be what is meaningful to you." The temporary result has to have a way of referring to its’ results so it is given a name, in this case it is an A. Think of it as the temporary name as something which can be used to then select items from it.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.