Division in Base Query

[kwj]

Trying to divide the variable in a field by a constant in a new field using the formula

“Hlf_OZ” / 300

This produces an error

"The data content could not be loaded.

This function is not supported: VARCHAR in [Select “Ingredient” AS “Ingredient” , “Half_OZ” AS “Half_OZ”, “Two_OZ” AS “Two_OZ”, “Half_OZ”/300 from “Essential Oils” ORDER BY “Ingedients” ASC]

When I enter the formula “Hlf_OZ” * 300 there is no problem. ???

Your time and assistance is greatly appreciated in assisting with this issue.

~ Respect

[Ratslinger]

Hello,

You are using a character field to do division. This is not allowed in most databases. The query also will not work with +, - or * unless Run SQL command directly is on. Change the field type to Numeric or Decimal or Double or something relevant.

Edit 2018-06-28:

@kwj Did some short testing and not certain where your problem lies. Here is spreadsheet used (connected to & not copied into tables) & where the table was automatically generated:

Columns B, E and F are all set as Number->General in sheet. Columns E & F are also calculated fields (seen above as F2 is selected) and you can see each of these generate a Decimal field type in Base. SQL in Base worked without a problem using +, -, * & /. Again not sure where your problem lies with the information provided.

This was done using Mint 18.3 & LO v6.0.5.2

[kwj]

Thank you - the issue with making changing the filed type to numeric [et al] is that the table is imported from Calc. In calc the field type (column) is numeric, but guess it does not carry over to Base?
When I copy it to Base then try to change the format, Base responds that the field type could not be changed asking if it should be rewritten as a new field. When I choose yes, it delete the field data???

Appreciate the assistance and time.

[kwj]

Raslinger, True to your words, I was able to create a quick table with a couple of fields formatted as numeric and was able to perform the calculaion with no issue. So the problem is now with the import from Calc to get and retain the data. Or do I need to re-enter it into the new table with correctly formatted fields?

Respect

[Ratslinger]

@kwj One of the possibilities is to actually create Base tables from the Calc data. This is not re-entering the data but copying it from Calc to Table(s). For more info on this see my answer in this post → how to convert a ods spreadsheet to and odb table. The LO docs may also be of help → Documentation/Publications.

[Ratslinger]

As for using the data directly from Calc, I don’t have an immediate answer but will look into a bit later today. In the future, information such as that is helpful to include in your question.

[kwj]

Thank you, your time and effort is very much appreciated!

[Xoristzatziki1]

Spreadsheet as source for odb assumes that calculated cells are VARCHAR. If you need some calculations either do them in an extra column-field, or CAST the field in the SQL command.

You can see which fields are which type by right clicking on the table and selecting edit. You cannot actually edit it but you can see the types.