向SQL查询的结果添加静态值 [英] Adding a static value to the results of an SQL query

问题描述

我有一个表，让我们称之为LISTOFTHINGS，它有两个字段感兴趣的ID和NAMEOFTHING

我想要做的是构造一个查询，以便返回的是此查询的结果：

  SELECT ID，NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING 
  

，并在上述查询的第一行之前添加一行，该列具有-1，ALL THING作为值。

所以如果表格有以下三个条目：

  1，'THING 1'
 3，'THING 3'
 2，'THING 2'
  

然后我想要的结果如下所示： p>

  -1，'ALL THINGS'
 1，'THING 1'
 2，'THING 2'
 3，'THING 3'
  

我知道我可以做查询并创建列表与代码，但在VB6中ogram在哪里使用这个，我有一个第三方应用程序（我没有代码），这个查询用来填充一个ACTIVEX表控件的结果。我没有挂钩来添加静态值。

我也知道我可以在表中记录-1所有的事情，但问题是，如果我这样做，我将需要改变程序中的很多地方，以便在处理时忽略该记录。

'ALL THINGS'值是一种伪记录，用于处理程序一部分的特殊情况。

解决方案

你可以在查询中做一个联合吗？

  SELECT -1 AS ID，从DUAL / *FROM DUAL中的所有事情都是Oracle的东西，
不知道你是否需要
在DB2 * / 
 UNION 
 SELECT ID，NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING 
  

< hr>

显然，这是DB2应该如何做的。

  SELECT  - 1 AS ID，'ALL THINGS'AS NAMEOFTHING FROM SYSIBM.SYSDUMMY1 
 UNION 
 SELECT ID，NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING 
  

I'm wondering if there is a way to accomplish this with an SQL query.

I have a table, lets call it "LISTOFTHINGS" that has two fields of interest "ID" and "NAMEOFTHING"

What I want to do is construct a query such that what gets returned is the results of this query:

SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING

and adds a row before the first row of the above query that has " -1, 'ALL THINGs' " as the values.

So if the table has the following three entries:

1, 'THING 1'
3, 'THING 3'
2, 'THING 2'

Then the result that I want looks like this:

-1, 'ALL THINGS'
1, 'THING 1'
2, 'THING 2'
3, 'THING 3'

I know that I can do the query and create the list with code, but inside the VB6 program where I am using this, I have a 3rd party app (which I don't have the code for) that takes the query to populate an ACTIVEX table control with the results. I don't have the hooks to go in to add the static value.

I also know that I could just put a record in the table for " -1, 'ALL THINGS' " but the problem is, if I do that, I will need to change a lot of places in the program to ignore that record when doing processing.

The 'ALL THINGS' value is sort of a pseudo record that handles a special case for one part of the program.

解决方案

Could you do a union in the query?

SELECT -1 AS ID , 'ALL THINGS' AS NAMEOFTHING FROM DUAL /*'FROM DUAL' is an Oracle thing,
                                                       not sure if you need to do 
                                                       something like that in DB2*/
UNION 
SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING

---

Apparently, this is how it should be done for DB2

SELECT -1 AS ID , 'ALL THINGS' AS NAMEOFTHING FROM SYSIBM.SYSDUMMY1
UNION 
SELECT ID, NAMEOFTHING FROM LISTOFTHINGS ORDER BY NAMEOFTHING

这篇关于向SQL查询的结果添加静态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！