SQL联合在where子句中使用coalesce（？）

I am trying to incorporate old help desk records into a newer help desk app. I still have all the data from the old help desk app and it uses the same fields as the new one but its in a different file. This select statement works fine for the newer application to search all the past calls for something specific, whether its a keyword or who it is allocated to and so on.

我正在尝试将旧的帮助台记录合并到更新的帮助台应用程序中。我仍然拥有旧帮助台应用程序中的所有数据，它使用的字段与新字段相同，但它位于不同的文件中。此select语句适用于较新的应用程序，以搜索所有过去的特定事件调用，无论是关键字还是分配给谁，等等。

SELECT status, identity, description, contact, scan_text, extended_desc, allocated_to
 FROM   helpdesk.table1
 WHERE  UPPER(allocated_to) = coalesce(?, allocated_to) 
  AND   identity = coalesce(?, identity) 
  AND   description = coalesce(?, description) 
  AND   contact= coalesce(?, contact) 
  AND UPPER(scan_text) LIKE coalesce(?,scantext)
  and upper(extended_desc) like coalesce(?, extended_desc)
 ORDER by allocated, identity desc

What I am trying to do is use some kind of union so I can just have one box to search both the newer and the old records, instead of two different boxes and having to remember where the data might be stored. I was thinking something like this might work but I think my where clause is too ambiguous and I tried just about every combination of including a library in front of the fields.

我想要做的是使用某种联合，所以我可以只有一个盒子来搜索新的和旧的记录，而不是两个不同的盒子，并且必须记住数据的存储位置。我认为这样的事情可能有用，但我认为我的where子句太模糊了，我试着在字段前面包含一个库的每个组合。

Select *
From
(
select status, identity, description, contact, scan_text, extended_desc, allocated_to
 from helpdesk.table1
Union                                   
select status, identity, description, contact, scan_text, extended_desc, allocated_to
 from helpdesk.table2
)
WHERE   UPPER(allocated_to) = coalesce(?, allocated_to) 
  AND   identity = coalesce(?, identity) 
  AND   description = coalesce(?, description) 
  AND   contact= coalesce(?, contact) 
  AND UPPER(scan_text) LIKE coalesce(?,scantext)
  and upper(extended_desc) like coalesce(?, extended_desc)
 ORDER by allocated, identity desc

If I do just the union with the two selects, I get all the records from both tables but i need to be able to narrow the results to a keyword or some other kind of field like in the first block of code. If someone could point me in the right direction I would really appreciate it.

如果我只使用两个选择的联合，我从两个表中获取所有记录，但我需要能够将结果缩小到关键字或其他类型的字段，如第一个代码块。如果有人能指出我正确的方向，我会非常感激。

Also I probably should have said that it is db2 and the sql is running on a web app. So when this sql runs it generates either drop down boxes or a text field to put your own words into to narrow down the results of all the help desk calls.

另外我可能应该说它是db2并且sql在web应用程序上运行。因此，当这个sql运行时，它会生成下拉框或文本字段以放入您自己的单词以缩小所有帮助台调用的结果。

3 个解决方案

#1

If I understand correctly, you want an indicator of which table a record comes from. If so:

如果我理解正确，您需要一个记录来自哪个表的记录。如果是这样：

Select *
From (select 'old' as which, status, identity, description, contact, scan_text, extended_desc, allocated_to
      from helpdesk.table1
      Union all                                
      select 'new', status, identity, description, contact, scan_text, extended_desc, allocated_to
      from helpdesk.table2
     ) hd
WHERE UPPER(allocated_to) = coalesce(?, allocated_to) AND
      identity = coalesce(?, identity) AND
      description = coalesce(?, description) AND
      contact= coalesce(?, contact) AND
     UPPER(scan_text) LIKE coalesce(?,scantext) AND
     upper(extended_desc) like coalesce(?, extended_desc)
ORDER by allocated, identity desc;

You can then add the appropriate where logic on the which column (and rename the column to whatever you would like as well).

然后，您可以在哪一列上添加适当的where逻辑（并将列重命名为您想要的任何内容）。

#2

You can always just put the where in both select statements that you're trying to union. Might be faster than combining both tables, then filtering.

您可以随时将两个select语句中的where放在您尝试合并的位置。可能比组合两个表更快，然后过滤。

SELECT * FROM  helpdesk.table1
WHERE   UPPER(allocated_to) = coalesce(?, allocated_to) 
  AND   identity = coalesce(?, identity) 
  AND   description = coalesce(?, description) 
  AND   contact= coalesce(?, contact) 
  AND UPPER(scan_text) LIKE coalesce(?,scantext)
  and upper(extended_desc) like coalesce(?, extended_desc)
UNION ALL 
SELECT * FROM  helpdesk.table2
WHERE   UPPER(allocated_to) = coalesce(?, allocated_to) 
  AND   identity = coalesce(?, identity) 
  AND   description = coalesce(?, description) 
  AND   contact= coalesce(?, contact) 
  AND UPPER(scan_text) LIKE coalesce(?,scantext)
  and upper(extended_desc) like coalesce(?, extended_desc)
ORDER BY allocated, identity desc;

just put the order by after the second select. UPPER(allocated_to) = coalesce(?, UPPER(allocated_to)) syntax might work better also if you're worried about case.

只需在第二次选择后按顺序排序。如果您担心案例，UPPER（allocated_to）= coalesce（？，UPPER（allocated_to））语法也可能更好。

#3

I don't know if this is specific to the web app generator that I am using or if you can use it other places in different SQL, but you can reference the parameters and pass them behind the scenes with something called a parameter parameter which are the ??1, ??2, ??3 in the second half of the union. This solved the problem of having two sets of boxes for user input by just passing what you enter in the first where. Here is the final version of code that works for me

我不知道这是否特定于我正在使用的Web应用程序生成器，或者您可以在不同的SQL中使用它的其他位置，但您可以引用参数并在后面使用称为参数参数的内容传递它们在工会的下半部分，?? 1，?? 2，?? 3。这解决了通过传递你在第一个输入的内容而拥有两组用于用户输入的框的问题。这是适用于我的代码的最终版本

SELECT status, identity, description, contact, scan_text, extended_desc, allocated_to
FROM   helpdesk.table1
WHERE  UPPER(allocated_to) = coalesce(?, allocated_to) 
 AND   identity = coalesce(?, identity) 
 AND   description = coalesce(?, description) 
 AND   contact= coalesce(?, contact) 
 AND UPPER(scan_text) LIKE coalesce(?,scantext)
 and upper(extended_desc) like coalesce(?, extended_desc)

Union

SELECT status, identity, description, contact, scan_text, extended_desc, allocated_to
FROM   helpdesk.table2
WHERE  UPPER(allocated_to) = coalesce(??1, allocated_to) 
 AND   identity = coalesce(??2, identity) 
 AND   description = coalesce(??3, description) 
 AND   contact= coalesce(??4, contact) 
 AND UPPER(scan_text) LIKE coalesce(??5,scantext)
 and upper(extended_desc) like coalesce(??6, extended_desc)
ORDER by allocated, identity desc

Thank you for those that tried to help me out though.

感谢那些试图帮助我的人。

#1