SQL Case语句错误无法选择多个列

Question

我需要从Case语句内的SQL Select语句获取供应商ID。一旦我把A.SUPPLIER_ID选择语句，我得到一个错误。这个怎么做？

Select 
     CASE 
     WHEN TYPE = 1 THEN 
      (
       SELECT A.name 
       from BIZZXE_V2_SCH.SUPPLIERS A 
       where A.SUPPLIER_ID = 30 
      ) 
     ELSE 
      (
      SELECT A.name 
      from BIZZXE_V2_SCH.STOCK_SUPPLIER A 
      where A.SUPPLIER_ID = 31 
     ) 
     END name 
from DUAL; 

Answer 1

您无法将完整的查询放入case语句中。但这应该工作

SELECT name 
from BIZZXE_V2_SCH.SUPPLIERS 
where SUPPLIER_ID = 30 and TYPE = 1 
union all 
SELECT name 
from BIZZXE_V2_SCH.STOCK_SUPPLIER 
where SUPPLIER_ID = 31 and TYPE <> 1 

Answer 2

你应该能够在你的WHERE语句中处理这个，就像这样。

SELECT A.name 
FROM BIZZXE_V2_SCH.STOCK_SUPPLIER A 
WHERE (A.SUPPLIER_ID = 30 AND TYPE = 1) OR 
A.SUPPLIER_ID = 31 

Answer 3

使用IF/ELSE声明

DECLARE @type NUMBER; 
SELECT TYPE INTO @type FROM DUAL; -- Make sure it always returns one row 

IF @type = 1 THEN 
    SELECT A.name 
    FROM BIZZXE_V2_SCH.SUPPLIERS A 
    WHERE A.SUPPLIER_ID = 30; 
ELSE 
    SELECT A.name 
    FROM BIZZXE_V2_SCH.STOCK_SUPPLIER A 
    WHERE A.SUPPLIER_ID = 31 
END IF;