oracle CASE WHEN after THEN 或 Select value from other table
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26842857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE WHEN after THEN or Select value from other table
提问by Wrymn
I need to do this:
我需要这样做：
SELECT
COLUMN1,
COLUMN2,
CASE SOMETHING WHEN 0 THEN 'SOMETHING'
ELSE
CASE SOMETHING1 WHEN 'SOMETHING2' THEN (Here I need my value from other table)
ELSE
...
ELSE
...
END
END
AS SOMETHINGSPECIAL
...
...
...
Entire select is horribly complicated sorry.
整个选择非常复杂，抱歉。
In the place after THEN in () I need to take out specific value from other table.
I have tried almost everything there is from joins, to put there SELECT WHEREor CASE WHENstatement it always end up with some error.
Keyword missing etc.
在 THEN in () 之后的地方，我需要从其他表中取出特定值。我已经尝试了几乎所有从连接到放置SELECT WHERE或CASE WHEN声明的内容，它总是以一些错误告终。关键字丢失等
Also maybe problem is inside () there is long concatenate: ''
也可能问题出在 () 里面有很长的连接：''
I need to put that specific value from other table into that concatenate.
It either doesn't want to allow me to use other CASE WHENafter that THENor I'm doing something wrong.
我需要将其他表中的特定值放入该连接中。它要么不想让我CASE WHEN在那之后使用其他，THEN要么我做错了什么。
EDIT (sorry cant post entire query dont wanna have problems in work):
编辑（抱歉不能t post entire query don在工作中遇到问题）：
SELECT
A.SOMETHING
CASE WHEN A.LIST_ID IN ('something','something') THEN '<A HREF="something?thresholdId=something' || GET_SITE_SUFFIX() || chr(38) || 'task=runSQL' || chr(38) || 'parseParams=true' || chr(38) || 'list_id=' || A.LIST_ID || chr(38) || 'list_name=' || A.LIST_NAME || '"> (MY VALUE FROM OTHER TABLE HERE) </A>'
END
AS SOMETHINGSPECIAL
FROM SOMETABLE
...
(MY VALUE FROM OTHER TABLE HERE) I tried to put there Select statement condition, Case statement to take out that one value from other table but it just gives me error.
（我在其他表中的值）我试图在那里放置 Select 语句条件，Case 语句以从其他表中取出一个值，但它只是给了我错误。
回答by Gordon Linoff
You can use a correlated subquery:
您可以使用相关子查询：
(CASE SOMETHING
WHEN 0 THEN 'SOMETHING'
ELSE (CASE SOMETHING1
WHEN 'SOMETHING2' THEN (select value from othertable ot where ot.col = x.col)
ELSE . . .
Do note that you don't need nested cases. You could write this as:
请注意，您不需要嵌套案例。你可以这样写：
(CASE WHEN SOMETHING = 0 THEN 'SOMETHING'
WHEN SOMETHING1 = 'SOMETHING2' THEN (select value from othertable ot where ot.col = x.col)
ELSE . . .
END)
回答by Ronny Mahlangu
You could try joining the table that the other values should come from, that's if there is a link between those tables, so these is what you should do
您可以尝试加入其他值应该来自的表，如果这些表之间存在链接，那么这就是您应该做的
SELECT
T1.COLUMN1,
T2.COLUMN1,
T.The-column-you-want-from-table2
iNNER JOIN TABLE2 T2 ON T2.cOUMN1 = T1.COLUMN1
So there is no need for a case statement, because from what I can gather from your question, you want a representation value of a value from another table
所以不需要 case 语句，因为从我从你的问题中可以收集到的信息，你想要一个来自另一个表的值的表示值
Here is another more specific example,
这是另一个更具体的例子，
Select
Transaction.OrderId
Transaction.OrderDate
Order.StatusDescription
From Transaction
Inner Join Order on Order.OrderID = Transaction.OrderID
In the above example, the StatusDescription is what need from the "other" table, this can be anything from paid,out-of-stock or whatever status. This status in your first table is represented by a number like 1 for paid, 2 for out of stock etc.....
在上面的例子中，StatusDescription 是“other”表中需要的，可以是已付款、缺货或任何状态。您的第一个表格中的此状态由一个数字表示，例如 1 表示已付款，2 表示缺货等.....
Hope this helps
希望这可以帮助
