Ask Your Question
0

Query calculation in Base

asked 2015-02-08 04:22:18 +0200

bluegator4 gravatar image

Hello, I am using Libre Office 4.2.6.3 on Windows 7 64 bit.

I have a table of grocery products ("Products"), a price history table ("Prices") and a table ("Stores") for the locations where the items are purchased. See my query design, below, for the relationships.

For each price of a product entered in "Prices", I want the query to calculate the product price per quantity (ounces, pounds, etc), by dividing the price by the quantity. The result is to be stored in an "alias" called "PriceUOM."

The query, as designed, produces the error message below. I have noted that this method of designating fields in a calculation, with double quotes and a period (example: "Prices"."Price") works if the fields in the calculation are in the same table, but apparently fields from different tables don't work -- or else I'm missing something.

I'm in the process of moving from Access to Base, and queries like this work just fine in Access, so I'm confused here.

Trust me, I HAVE googled this problem extensively and so far haven't found anyone else struggling with this.

Any enlightenment would be appreciated. Thank you!

Query design: image description

Query result: image description

edit retag flag offensive close merge delete

Comments

anything useful under the 'more' button? are all the text fields VARCHAR? No CLOB or CHAR?

doug gravatar imagedoug ( 2015-02-08 04:54:32 +0200 )edit

JohnSUN, you may as well convert your comment to an answer. I think you nailed it in one. And if not, you can always edit your answer. (I was going to upvote it because you said exactly what I was thinking.)

Wildcard gravatar imageWildcard ( 2015-02-08 10:10:52 +0200 )edit

1 Answer

Sort by » oldest newest most voted
2

answered 2015-02-08 09:52:01 +0200

JohnSUN gravatar image

updated 2015-02-08 10:47:26 +0200

I suspect that the field type of Price or of Quo (or both) is not specified as numeric, but as text. In this case, can work as a data transformation

CAST( REPLACE( "Prices"."Price", ',', '.' ) AS DECIMAL ) / "Products"."Qty" "PriceUOM"

or

"Prices"."Price" / CAST( REPLACE( "Products"."Qty", ',', '.' ) AS DECIMAL ) "PriceUOM"

or

CAST( REPLACE( "Prices"."Price", ',', '.' ) AS DECIMAL ) / CAST( REPLACE( "Products"."Qty", ',', '.' ) AS DECIMAL ) "PriceUOM"
edit flag offensive delete link more

Comments

Yes, in fact, "Qty" was 'Text[VARCHAR]' so that was the problem! I changed it to 'DECIMAL' to match the type of "Price", and that fixed it. Thank you for your help, JohnSUN!

bluegator4 gravatar imagebluegator4 ( 2015-02-09 00:41:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-02-08 04:22:18 +0200

Seen: 1,079 times

Last updated: Feb 08 '15