How do I use aliases in a Union Query-CodePudding

The following query works without problem

SELECT [TblA].fieldA, 
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<1,[fieldA],Left([fieldA],InStr([fieldA],",")-1))) AS a, 
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<1,'',IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))=1,Right([fieldA],len([fieldA])-len([a])-1),           Mid([fieldA],Len([a]) 2,Instr(Len([a]) 2,[fieldA],",")-Len([a])-2)))) AS b, 
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<2,'',IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))=2,Right([fieldA],len([fieldA])-len([a])-len([b])-2),  Mid([fieldA],Len([a]) Len([b]) 3,Instr(Len([a]) Len([b]) 3,[fieldA],",")-Len([a])-Len([b])-3)))) AS c, 
iif(isnull([fieldA]),"",IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))<3,'',IIF(Len([fieldA]) - Len(Replace([fieldA], ",", ""))=3,Right([fieldA],len([fieldA])-len([a])-len([b])-len([c])-3),  ""))) AS d
FROM TblA;

However, when I am trying to do the same in a Union Query it does not work ( Enter Parameter Value for a )

SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<1,[fldA],Left([fldA],InStr([fldA],",")-1))) AS a FROM tblA
UNION
SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<1,'',IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))=1,Right([fldA],len([fldA])-len([a])-1),Mid([fldA],Len([a]) 2,Instr(Len([a]) 2,[fldA],",")-Len([a])-2)))) AS b FROM tblA
UNION
SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<2,'',IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))=2,Right([fldA],len([fldA])-len([a])-len([b])-2),Mid([fldA],Len([a]) Len([b]) 3,Instr(Len([a]) Len([b]) 3,[fldA],",")-Len([a])-Len([b])-3)))) AS c FROM tblA
UNION
SELECT iif(isnull([fldA]),"",IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))<3,'',IIF(Len([fldA]) - Len(Replace([fldA], ",", ""))=3,Right([fldA],len([fldA])-len([a])-len([b])-len([c])-3),  ""))) As d FROM tblA

My question is:
Is there a way to use aliases in Union queries (e.g. As a )

CodePudding user response：

Cannot reference field (alias or not) from one SELECT in another SELECT. These are not nested statements. Also, only the first SELECT defines field names. The subsequent AS clauses are ignored and can be removed.

Build the first query object where these calculations occur. Then build UNION with first query as source.

SELECT a AS Data, "A" AS Category FROM query
UNION SELECT b, "B" FROM query
UNION SELECT c, "C" FROM query
UNION SELECT d, "D" FROM query;

UNION will not allow duplicate rows. Use UNION ALL to include all records, even if there are duplicates.