PRB: SQL AS Clause Is Ignored in UNION ALL Statement

ID: Q109301

The information in this article applies to:

• Microsoft FoxPro for Windows, versions 2.5, 2.5a, and 2.5b
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, and 2.5b

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')

      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