Ask Your Question
0

Division in Base Query

asked 2018-06-28 06:20:18 +0200

kwj gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-06-28 17:25:07 +0200

Xoristzatziki gravatar image

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.

edit flag offensive delete link more
0

answered 2018-06-28 06:56:56 +0200

Ratslinger gravatar image

updated 2018-06-29 01:09:28 +0200

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:

image description

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

edit flag offensive delete link more

Comments

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 gravatar imagekwj ( 2018-06-28 12:16:23 +0200 )edit

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

kwj gravatar imagekwj ( 2018-06-28 13:19:21 +0200 )edit

@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 gravatar imageRatslinger ( 2018-06-28 15:28:28 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-06-28 15:30:29 +0200 )edit

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

kwj gravatar imagekwj ( 2018-06-28 16:18:59 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-28 06:20:18 +0200

Seen: 69 times

Last updated: Jun 29 '18