I am trying to order the results of 2 queries. The second query does not utilize one of the tables in the first query, so I selected a constant NULL.

I get an error when running the query. Looks like an error with the order by. The error, 'invalid identifier', points to the table name of one of the fields in the order by.


select
ta.field1,
tb.field1,
tc.field1,
where
ta.field1 = tb.field1 and
ta.field1 = tc.field1 and
some other condition
union
select
ta.field1,
NULL,
tc.field1
where
ta.field1 = tc.field1 and
some other condition
order by
ta.field1 <==== Error ''invalid identifier' points to the ta


Can I do an order by on the union of 2 queries?

Thanks,
Linn

Sorry forgot the from tables.

select
ta.field1,
tb.field1,
tc.field1,
from tablea ta, tableb tb, tablec tc
where
ta.field1 = tb.field1 and
ta.field1 = tc.field1 and
some other condition
union
select
ta.field1,
NULL,
tc.field1
from tablea ta, tableb tb, tablec tc
where
ta.field1 = tc.field1 and
some other condition
order by
ta.field1 <==== Error ''invalid identifier' points to the ta

I just ran:MS-SQL 2000 didn't complain, although I think it should have! I'll have to investigate this when I get a chance.

-PatP

Did you try:

Code:

ORDER BY 1