Using CASE expression with codeless

General
1

Hi Backendless Team,

I have question regarding WHERE clause I am building.
Currently i load grouped table objects where i am looking for price item that has matching productId and storeId from lists

image
image847×652 32.5 KB

Is it possible to use CASE expression with codeless to cover case where i don’t have matching price from initial productId list and storeId list, and in that case I would use fallback storeId list.
From discussion with chatgpt it would look something like this:

WHERE 
  (productId IN ('productId1', 'productId2', 'productId3') AND storeId IN ('storeId1', 'storeId2', 'storeId3'))
  OR 
  (productId IN ('productId1', 'productId2', 'productId3') AND storeId IN ('fallbackStoreId1', 'fallbackStoreId2'))
ORDER BY 
  CASE 
    WHEN storeId IN ('storeId1', 'storeId2', 'storeId3') THEN 1
    ELSE 2
  END
LIMIT 1

Goal is to get latest price from product/store combination, and if particular storeId does not have the price, would look up price in an alternative store list.

2

Hello @Uldis_Borkus,

I see what you need. Unfortunately, we do not support the CASE operator. You would need to write an additional custom logic to handle this case.

Regards,
Stanislaw

3

Hi @stanislaw.grin
I stumbled into another topic, while it’s about generated column, i just wanted to doublecheck, if this is still the case, that
SQL CASE is possible with generated column expression, but it does not work with codeless blocks?

topic: CASE Statement in Generated Column - #3 by James_Hereford
article: Functions and Examples of Generated Columns | Backendless

4

Hi Uldis,

You can use a CASE statement in generated columns, however, the where clause syntax we have does not support that construct.

Regards,
Mark

5

Got it, Thank you!