SELECT WHERE 条件中的默认比较操作数 [英] Default comparison operand in SELECT WHERE condition

问题描述

我有一个表CategoryColours，如果没有找到一个类别，它应该返回默认类别*"的颜色.

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

如果我搜索类别1"，则查询应获得 4 种颜色.

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

如果我搜索表中没有记录的类别2"，则查询应该从类别*"中获取2种颜色.

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

是否可以使用 OpenSQL 在单个语句中获取我需要的确切列表?

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

我尝试使用 CASE 和子查询 (EXIST)，但我没有成功.

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.

推荐答案

我认为您可以使用 UNION.我没有尝试代码，它可能包含类型错误.

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).

这篇关于SELECT WHERE 条件中的默认比较操作数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！