Board index » delphi » UNION clause in SQL select

UNION clause in SQL select

Can anyone give me a simple example of using union clause in
SQL statement as Local SQL  help file is very  vague on the subject

Thanks

 

Re:UNION clause in SQL select


The SQL statement is as follows:

SELECT FIELD1 FROM TABLE WHERE FIELD2 = 'A'
UNION
SELECT FIELD1 FROM TABLE WHERE FIELD2 = 'B';

but, I don't know wether BDE Local SQL supports UNION statements. BDE
2.5x does not!

Yours Volker

Rumen Lilov schrieb:

Quote

> Can anyone give me a simple example of using union clause in
> SQL statement as Local SQL  help file is very  vague on the subject

> Thanks

--
Volker Fraenkle                         CS Controlling
Technical Consultant                    Software Systeme GmbH

E-Mail: VFraen...@cs-controlling.de
Phone:  +49 7144 8144-0                 Riedbachstrasse 5
Fax:    +49 7144 8144-10                D-74385 Pleidelsheim

Re:UNION clause in SQL select


On Fri, 28 Aug 1998 14:43:35 +0300, Rumen Lilov <rli...@mbox.digsys.bg>
wrote:

Quote
>Can anyone give me a simple example of using union clause in
>SQL statement as Local SQL  help file is very  vague on the subject

UNION Join
----------

Concatenates the rows of one table to the end of another table.

  SELECT col_1 [, col_2, ... col_n]
  FROM table_reference
  UNION [ALL]
  SELECT col_1 [, col_2, ... col_n]
  FROM table_reference

Description
-----------

Use the UNION join to add the rows of one table to the end of another
similarly structured SELECT query result sets. The SELECT statement for the
source and joining tables must include the same number of columns for them
to be UNION compatible. The table structures themselve need not be the same
as long as those column included in the SELECT statements are.

  SELECT custno, company
  FROM customers
  UNION
  SELECT custno, company
  FROM old_customers

When columns in the same column position of a UNION join have different
names across the various tables, the name for the column from the first
table is used.

If columns in the same column position are of differing data types in the
various tables, use the CAST function to convert the values in conflicting
tables so that all SELECT queries in the UNION join produce the same data
type for the columns. In the statement below, the column J.id is converted
from its original INTEGER type to CHAR to match S.id.

  SELECT S.id, name
  FROM source S
  UNION ALL
  SELECT CAST(J.id AS CHAR(10)), name
  FROM joiner J

By default, non-distinct rows are aggregated into single rows in a UNION
join. Use ALL to retain non-distinct rows.

To join two tables with UNION where one table does not have a column
included by another, a compatible literal or expression may be used instead
in the SELECT statement missing the column. For example, if there is no
column in the JOINING table corresponding to the NAME column in SOURCE an
expression is used to provide a value for a pseudo JOINING.NAME column.
Assuming SOURCE.NAME is of type CHAR(10), the CAST function is used to
convert an empty character string to CHAR(10).

  SELECT S.id, name
  FROM source S
  UNION ALL
  SELECT J.id, CAST("" AS CHAR(10))
  FROM joiner J

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)

Other Threads