SQL Union with coalesce(?) in where clause
<p>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.</p>

<pre><code>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
</code></pre>

<p>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.</p>

<pre><code>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
</code></pre>

<p>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.</p>

<p>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.</p>