ORA-00913: 使用 case 时的值太多 [英] ORA-00913: too many values while using case when

问题描述

我有一个要求，如果一个条件成立，我应该在查询 Q1 上执行，如果该条件失败，我应该执行另一个查询 Q2.该查询结果是用户进行搜索的记录.我在 if 条件下使用 case when 语句，因为 Q1 和 Q2 有不止一列要检索，我收到 ORA-00913: too many values .我开始知道在检索数据时无法使用更多列执行查询的情况.任何人都可以建议如何实现这种类型的要求.

I have a requirement such that, if one condition is true i should execute on query Q1 if that condition fails, i should execute another query Q2. This queries result is the records of search performed by the user. I am using case when statement for if condition, as Q1 and Q2 have more than one column to retrieve, I am getting ORA-00913: too many values . I came to know that case when cannot execute queries with more columns in retrieving data. Can anyone suggest how to achieve this type requirement.

更新:

我无法给出准确的查询，但可以提供伪代码

I cannot give exact query, but can provide pseudo code

select case when c1='1' then 
select c1,c2,c3 from table1
else select c1,c2,c3 from table2  end
from table1;

我在这里提供示例数据.

表 1

C1      C2     C3
1       null    1
1       2       null

表 2

C1      C2     C3
1       4       1
1       3       5
2       9       null

当我运行您提供的查询时，输出将如下所示.

When i run query you provided, the output will be as below.

select
    coalesce(table2.c1, table1.c1) c1,
    coalesce(table2.c2, table1.c2) c2,
    coalesce(table2.c3, table1.c3) c3
from table1
    left outer join table2
       on (your keys here)
           and table1.c1 <> '1' -- This gets table1 if c1 = '1';

输出:

C1    C2    C3
1     4      1
1     2      5
2     9      null

但是我期待的输出是

C1    C2    C3
1     null   1
1     2      null
2     9      null

希望我解释清楚.

推荐答案

当你使用 case 时，你必须只返回一条记录——不超过 1.为了达到你想要的结果，我将使用左外连接(假设您有办法将 table1 连接到 table2)，但将 table1.c1 上的检查添加到连接条件中，以便 table2 值仅在 c1 <> '1'

When you use a case, you must return only a single record - not more than 1. To achieve your desired result, I would use a left outer join (assuming you have a way to join table1 to table2), but add your check on table1.c1 into the join condition so that table2 values would only be present if c1 <> '1'

select
    coalesce(table2.c1, table1.c1) c1,
    coalesce(table2.c2, table1.c2) c2,
    coalesce(table2.c3, table1.c3) c3
from table1
    left outer join table2
       on (your keys here)
           and table1.c1 <> '1' -- This gets table1 if c1 = '1';

此解决方案假定 table1 和 table2 相关.如果您无法将它们关联起来，那么听起来您几乎可以使用并集 all，其中您从 table1 中获取所有值，其中 c1 = '1' 并将这些值与所有 table2 行合并.如有必要，您只能在 c1 <> '1' 时包含 table2 值.

This solution assumes table1 and table2 relates. If you can't relate them, then it sounds almost like you can use a union all in which you take all the values from table1 where c1 = '1' and union those to all table2 rows. If necessary, you could only include table2 values if c1 <> '1'.

select c1, c2, c3 from table1 where c1 = '1'
union all
select c1, c2, c3 from table2; -- where c1 <> '1' -- if necessary

更新

根据您的示例数据和预期输出，请使用上面的第二个查询:

Based on your sample data and expected output, please use the second query above:

select c1, c2, c3 from table1 where c1 = '1'
union all
select c1, c2, c3 from table2 where c1 <> '1'

SQL 小提琴:http://www.sqlfiddle.com/#!4/710f0/1/0

这篇关于ORA-00913: 使用 case 时的值太多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！