Manually Add a Row to a Resultset

[Potter]

I am querying a DB2 database via Sequelink and I want to manually add a
row to my recordset. In SQL Server, I would use a UNION clause like
this:

SELECT
Field1 as ValueField
,Field2 as DescField
FROM TableA
UNION
SELECT
Null as ValueField
,'All' as DescField

Is there similar syntax for DB2?

Thanks in advance.

[Jan M. Nelken]

Potter wrote:

I am querying a DB2 database via Sequelink and I want to manually add a
row to my recordset. In SQL Server, I would use a UNION clause like
this:

SELECT
Field1 as ValueField
,Field2 as DescField
FROM TableA
UNION
SELECT
Null as ValueField
,'All' as DescField

Is there similar syntax for DB2?

Thanks in advance.

That should not work as second SELECT does not have FROM clause.

However - in DB2 I could use this construct:

SELECT
field1 AS ValueField,
field2 AS DescField
FROM
tablea
UNION
SELECT
99 AS ValueField,
'All' AS DescField
FROM
sysibm.sysdummy1;

I could also use this:

SELECT
field1 AS ValueField,
field2 AS DescField
FROM
tablea
UNION
VALUES
99,
'All';

Jan M. Nelken

[Mark A]

"Potter" <dr********@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

I am querying a DB2 database via Sequelink and I want to manually add a
row to my recordset. In SQL Server, I would use a UNION clause like
this:

SELECT
Field1 as ValueField
,Field2 as DescField
FROM TableA
UNION
SELECT
Null as ValueField
,'All' as DescField

Is there similar syntax for DB2?

Thanks in advance.

Yes, you but you need a FROM table-name in the 2nd part of the UNION.
Assuming that you want only one row in the second part of the UNION, then
use the table SYSIBM.SYSDUMMY1 (which is guaranteed to only contain one
row). You also need to cast the null as the same data type as ValueField.

Assuming that ValueField is a decimal, then it would be:

SELECT
Field1 as ValueField
,Field2 as DescField
FROM TableA
UNION
SELECT
cast (null as decimal) as ValueField
,'All' as DescField
FROM SYSIBM.SYSDUMMY1

Keep in mind that UNION will eliminate duplicates, and UNION ALL should be
used if you know there are no duplicate rows in the result set.

[Mark A]

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message
news:qK******************************@rogers.com.. .

Potter wrote:

I am querying a DB2 database via Sequelink and I want to manually add a
row to my recordset. In SQL Server, I would use a UNION clause like
this:

SELECT
Field1 as ValueField
,Field2 as DescField
FROM TableA
UNION
SELECT
Null as ValueField
,'All' as DescField

Is there similar syntax for DB2?

Thanks in advance.

That should not work as second SELECT does not have FROM clause.

However - in DB2 I could use this construct:

SELECT
field1 AS ValueField,
field2 AS DescField
FROM
tablea
UNION
SELECT
99 AS ValueField,
'All' AS DescField
FROM
sysibm.sysdummy1;

I could also use this:

SELECT
field1 AS ValueField,
field2 AS DescField
FROM
tablea
UNION
VALUES
99,
'All';

Jan M. Nelken

For a null try the following:

SELECT
cast (null as decimal) as ValueField
,'All' as DescField
FROM SYSIBM.SYSDUMMY1

In the "null as decimal" you should use the same datatype (decimal, integer)
as exists in the table.

[Potter]

Jan,

Thank you for the help. My sample SELECT without a FROM clause is
valid SQL Server syntax. The necessity of having a FROM clause in DB2
is what I was trying to work around.

I used the VALUES function and was able to achieve what I was after. I
do not have rights to query the SYS tables, but Value worked out
perfect.

Thanks for your time and help.

Andy

[Serge Rielau]

Potter wrote:

Jan,

Thank you for the help. My sample SELECT without a FROM clause is
valid SQL Server syntax. The necessity of having a FROM clause in DB2
is what I was trying to work around.

I used the VALUES function and was able to achieve what I was after. I
do not have rights to query the SYS tables, but Value worked out
perfect.

Thanks for your time and help.

Andy

Andy,

Note that unlike Sybase/SQL Server VALUES allows you to add more than
one row. So there is no need to add more than one UNION [ALL]

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Potter]

Serge,

Can you post a sample of generating multiple rows using VALUES rather
than multiple UNIONS?

Thank you,

Andy

[Brian Tkatch]

DECLARE GLOBAL TEMPORARY TABLE A (A INT, B INT)
INSERT INTO SESSION.A VALUES (1,2), (3,4)
SELECT A, B FROM SESSION.A UNION VALUES (5,6), (7,8), (9,0) ORDER BY 1
DROP TABLE SESSION.A
COMMIT

HTH,
B.

[Serge Rielau]

Potter wrote:

Serge,

Can you post a sample of generating multiple rows using VALUES rather
than multiple UNIONS?

Thank you,

Andy

SELECT x, y FROM T
UNION [ALL]
VALUES (1, 2), (3, 4), (5, 6)

You can use multi row VALUES anywhere you wish, including INSERT or a
JOIN (such as to PIVOT columns into rows)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab