Default comparison operand in SELECT WHERE condition

Question

I have a table CategoryColours, if a category is not found, it should return the colors from the default category "*".

Example if the table contains these lines:

Category  Color
*         white
*         black
1         red
1         blue
1         green
1         black

If I search the category "1", the query should get the 4 colors.

If I search the category "2", which has no records in the table, the query should get the 2 colors from the category "*".

Is it possible to use OpenSQL to get the exact list that I need in a single statement?

I tried with CASE and subqueries (EXIST) but I didn't manage.

It's not a stopper for my code, since I can just check if my category has records first or select my category + the default always and then remove the default if the other has records.

could you please add some SQL code you tried to far and try to add formated, readable example data to your question. Sounds like you could use the coalesce function. — konstantin

mkysoft mkysoft · Accepted Answer · 2019-09-25T08:23:26

I think you can use UNION. I didn't try code, it can include type errors.

SELECT
      category,
      color
    FROM CategoryColours
   WHERE category = lv_category
UNION
SELECT
  category,
  color
FROM CategoryColours
WHERE category eq '*'
 AND NOT EXISTS ( SELECT color 
                    FROM CategoryColours 
                   WHERE category = lv_category
                 )
INTO TABLE @DATA(lt_itab).

Default comparison operand in SELECT WHERE condition

1 Answers