Microsoft KB Archive/109301

{| = PRB: SQL AS Clause Is Ignored in UNION ALL Statement =
 * width="100%"|

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.

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
 * }