Creating conditional calculated fields

English
#1

I have searched and searched, and I can’t create conditional calculated fields.

HSQLDB
Writing a query to later use in a form.

The main fields i need to use is
Weight
Quantity
Sellby with one of 3 entries which are PC, EA and SET
Wholesale$ and Retail$

WholesalePrice needs to be conditionally calculated.

Calculated fields to create are WholesalePrice and RetailPrice but I am going to address just one as the second calculated field is basically the same

IF Sellby ="PC" the WholesalePrice - Weight * Wholesale$
If Sellby = EA the WholesalePrice is Quantity * Wholesale$
IF Sellby = SET WholesalePrice is Wholesale$

I have tried everything i can find.
Any help will be appreciated.

Thanks,
Randy

#2

Might be I understood wrong, but seems to be you are looking for CASE WHEN

… CASE WHEN "Sellby" = 'PC' THEN "Weight" * "Wholesale$" 
WHEN "Sellby" = 'EA' THEN "Quantity" * "Wholesale$" 
ELSE "Wholesale$" END AS "WholesalePrice" …

But I don’t know which fields you want to add, subtract, multiplicate. Have tried it with help of @karolus post.

#3

@RobertG: I’ve edit OP for clarification.

#4

Woops it should be
IF Sellby =“PC” THEN WholesalePrice = Weight * Wholesale$ ELSE IF Sellby = “EA” then Wholesaleprice = Quantity * Wholesale$ ELSE Wholesaleprice = Wholesale$

#5

This look correct but i get an error. Still missing something.

Column not found: Sellby in statement [SELECT “Gem” AS “Gem”, “Tray” AS “Tray”, “Holder” AS “Holder”, “Cut” AS “Cut”, “SizeMM” AS “SizeMM”, “Color” AS “Color”, “Quantity” AS “Quantity”, “SellBy” AS “SellBy”, “Weight” AS “Weight”, “Wholesale$” AS “Wholesale$”, “Retail$” AS “Retail$”, “DateOnEtsy” AS “DateOnEtsy”, “ListedPrice” AS “ListedPrice”, “DateSold” AS “DateSold”, “SoldAmount” AS “SoldAmount”, “FollowUp” AS “FollowUp”, CASE WHEN “Sellby” = ‘PC’ THEN “Weight” * “Wholesale$” WHEN “Sellby” = ‘EA’ THEN “Quantity” * “Wholesale$” ELSE “Wholesale$” END AS “WholesalePrice” FROM “Gems” ORDER BY “Gem” ASC, “Tray” ASC, “Holder” ASC]
.
.
SELECT “Gem” AS “Gem”, “Tray” AS “Tray”, “Holder” AS “Holder”, “Cut” AS “Cut”, “SizeMM” AS “SizeMM”, “Color” AS “Color”, “Quantity” AS “Quantity”, “SellBy” AS “SellBy”, “Weight” AS “Weight”, “Wholesale$” AS “Wholesale$”, “Retail$” AS “Retail$”, “DateOnEtsy” AS “DateOnEtsy”, “ListedPrice” AS “ListedPrice”, “DateSold” AS “DateSold”, “SoldAmount” AS “SoldAmount”, “FollowUp” AS “FollowUp”, CASE WHEN “Sellby” = ‘PC’ THEN “Weight” * “Wholesale$” WHEN “Sellby” = ‘EA’ THEN “Quantity” * “Wholesale$” ELSE “Wholesale$” END AS “WholesalePrice” FROM “Gems” ORDER BY “Gem” ASC, “Tray” ASC, “Holder” ASC

#6

Spelling is different. B vs b. Is this ignored by your database?

#7

Field is written in the database as “SellBy” and is written in the code example as “Sellby”. Correct the code example as the error reports. “Sellby” is unknown, “SellBy” is known.

#8

It was the case sensitive Sellby vs SellBy
THank you

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