PRB: SQL AS Clause Is Ignored in UNION ALL Statement

ID: Q109301

The information in this article applies to:

SYMPTOMS

The AS clause associated with the second SELECT statement in a UNION ALL statement is ignored. However, the AS clause associated with the first SELECT statement can be used to specify the desired column header; the AS clause also works as expected in a UNION statement.

RESOLUTION

For a demonstration of how to work around this behavior, see "Workaround" in the "More Information" section below.

MORE INFORMATION

Steps to Reproduce Behavior

1. Use this code to create and fill the tables:

      CREATE TABLE customer ;
         ( cno C(5), company C(35), contact C(20), ;
         address C(30), city C(15), state C(2), zip C(5), ;
         phone C(12), ono C(1), ytdpurch N(8,2), lat N(7,4), ;
         long N(8,4) )

      CREATE TABLE invoices ;
         ( ino N(4), cno C(5), idate D, itotal N(8), ;
         salesman C(3) )

      INSERT INTO customer (cno, company, contact, address, city, state,
      zip, ;
         phone, ono, ytdpurch, lat, long) ;
         VALUES ('a123', '1st Company', 'No Name', 'One Microsoft Way', ;
         'Redmond', 'WA', '98052', '1206123456', '1', ;
         1000.99, 100.999, 100.999)

      INSERT INTO invoices (ino, cno, idate, itotal, salesman) ;
         VALUES (9999, 'A123', {09/01/93}, 1000.99, 'Bob')

2. Use this code to reproduce the problem:

      SELECT customer.cno, invoices.ino ;
         FROM customer, invoices;
         WHERE invoices.cno = customer.cno ;
         UNION ALL ;
         SELECT customer.cno, invoices.ino AS B ;
         FROM customer, invoices ;
         INTO CURSOR new

Workaround

The following code works around the problem:

   SELECT customer.cno, invoices.ino AS B ;
      FROM customer, invoices;
      WHERE invoices.cno = customer.cno ;
      UNION ALL ;
      SELECT customer.cno, invoices.ino ;
      FROM customer, invoices ;
      INTO CURSOR new

Additional reference words: FoxWin FoxDos 2.50 2.50a 2.50b alias ignore broken KBCategory: kbprg kbprb KBSubcategory: FxprgGeneral

Last Reviewed: June 27, 1995