Union pass through query returns null records

[ragtopcaddy via AccessMonster.com]

I have the following Union query set up as a pass through query:

SELECT Host, "Service_Tier" AS Missing FROM dbo.table WHERE service_tier Is
Null AND created In (SELECT Max([created]) FROM dbo.table)

Union SELECT Host, "capacity_type" FROM dbo.table WHERE capacity_type Is Null
AND created In (SELECT Max([created]) FROM dbo.table)

Union SELECT Host, "city" FROM dbo.table WHERE city Is Null AND created In
(SELECT Max([created]) FROM dbo.table) ORDER BY Host;

It returns the following:

Host Missing
sstm0647pap
sstm0648psy
sstm0692por
sstm1468psy
sstm1833psy
sstm1847psy
sstm2924psy
sstm2978por
sstm3478psy
sstm8155por
sstmc106pn1
sstmc106pn2
xstm0663psy
xstm0664psy
xstm1497psy
xstm2328por
xstm2999por

I was expecting a list of hosts, perhaps 3 rows for each, with either
"service_tier", "capacity_type", or "city" in the missing column. Apparently
the query found records satisfying the criteria, but didn't return any values
for Missing.

 

[ragtopcaddy via AccessMonster.com]

I found my error, but posted anyway in case someone else has the same problem.


The view I'm getting my data from has the following SQL Server parameter set:

SET QUOTED_IDENTIFIER ON

This means that text must use the single quote('), rather than the double
quote("). When I edited my union query as follows:

SELECT Host, 'Service_Tier' AS Missing FROM dbo.table WHERE Service_Tier Is
Null AND created In (SELECT Max([created]) FROM dbo.table)

Union SELECT Host, 'capacity_type' FROM dbo.table WHERE capacity_type Is Null
AND created In (SELECT Max([created]) FROM dbo.table)

Union SELECT Host, 'city' FROM dbo.table WHERE city Is Null AND created In
(SELECT Max([created]) FROM dbo.table) ORDER BY Host;

I got the expected results.

 

[John Spencer]

Try using single quotes instead of double quotes.

MS SQL Server does not use double quotes to delimit strings.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..