使用 Oracle 子查询替换 CASE 语句
大家好,
有人可以帮我解决 Oracle 数据库 10g 中的子查询吗? 我需要提取第一个表中的列的值作为第二个表中另一列的值。 我目前使用这个语句:
SELECT
CASE WHEN A.column1 = 'A' THEN 'aaa'
WHEN A.column1 = 'B' THEN 'bbb'
.......
WHEN A.column1 = 'X' THEN 'xxx'
ELSE 'bad' END AS COLUMN1, A.*
FROM TRANSACTION_TABLE A, CATEGORY_TABLE B
WHERE A.column1 IS NOT NULL
AND A.column1 <> ' '
这不是一个优雅的方法,所以我尝试使用 CATEGORY_TABLE B 中的子选择,如下所示:
SELECT A.column1, A.*
FROM TRANSACTION_TABLE A, CATEGORY_TABLE B
WHERE A.column1 IS NOT NULL
AND A.column1 = B.column_b_1
AND A.column1 <> ' '
AND A.column1 IN (SELECT B.column_b_1_descr FROM CATEGORY_TABLE B
WHERE B.FIELDNAME = 'column1' AND A.column1 = B.column_b_1)
所以,我无法通过使用子查询获得任何结果,并且不想继续使用 CASE在许多情况下,只想将 A.column1 值替换为 B.column_b_1_descr 中的描述性值,因为它们更易于阅读。 如果有任何反馈,我将不胜感激。 谢谢
Hy guys,
can anybody please help me with a subquery in Oracle database 10g? I need to extract the values for a column in the first table as value of another column in the second table.
I currently use this statement:
SELECT
CASE WHEN A.column1 = 'A' THEN 'aaa'
WHEN A.column1 = 'B' THEN 'bbb'
.......
WHEN A.column1 = 'X' THEN 'xxx'
ELSE 'bad' END AS COLUMN1, A.*
FROM TRANSACTION_TABLE A, CATEGORY_TABLE B
WHERE A.column1 IS NOT NULL
AND A.column1 <> ' '
This is not an elegant approach, so I'm trying to use a subselect from CATEGORY_TABLE B like the following:
SELECT A.column1, A.*
FROM TRANSACTION_TABLE A, CATEGORY_TABLE B
WHERE A.column1 IS NOT NULL
AND A.column1 = B.column_b_1
AND A.column1 <> ' '
AND A.column1 IN (SELECT B.column_b_1_descr FROM CATEGORY_TABLE B
WHERE B.FIELDNAME = 'column1' AND A.column1 = B.column_b_1)
So, I cannot get any results by using the subquery and don't want to continue using the CASE against many conditions, just want to replace the A.column1 values with the descriptive values from B.column_b_1_descr , as they're easier to read.
I would appreciate any feedback.
Thanks
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
除非我误解了你的问题...
或者
不需要 where 子句,因为内部联接会自动排除具有空值或不匹配的行。
Unless I'm misunderstanding your question...
or
The where clause isn't needed, since an inner join automatically excludes rows with null values or no matches.