Query Producing Blank Rows? Fault IIf Logic?

[kestefon]

The query below looks at two existing tables, and based on the "Geography" column in each, it adds a "ReportType" column that tells you whether you're looking at National or Local data. The first part of the query works fine, but the second part is producing hundreds of blanks rows with "Local" in the ReportType column. I can't figure out why, because nat_tv.Geography only has "National"...there shouldn't be any "Local" data in it. Any ideas? I'm wondering if my IIf logic is faulty...

SELECT IIf(loc_tv.Geography="National","National","Local") AS ReportType, loc_tv.Geography, loc_tv.Indication, loc_tv.[Variable Name], loc_tv.[Variable Value]
FROM Localization_NatTV AS loc_tv
UNION ALL SELECT IIf(nat_tv.Geography="National","National","Local") AS ReportType, nat_tv.Geography, nat_tv.Indication, nat_tv.[Variable Name], nat_tv.[Variable Value]
FROM data_TVNational AS nat_tv;

[ssanfu]

Syntax looks correct.

Try doing a quick test: use "UNION" instead of "UNION ALL"


Edit:

The SQL UNION query is a SQL query to combine the result sets of 2 or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements.
The SQL UNION ALL query does not remove duplicates.

See:
http://www.techonthenet.com/sql/union.php
http://www.techonthenet.com/sql/union_all.php

[kestefon]

Thanks, but unfortunately, I get the blank "Local" rows even when I run the second part of the query alone (i.e. without the UNION to the first query)...

[ssanfu]

I get the blank "Local" rows even when I run the second part of the query alone (i.e. without the UNION to the first query)...

So you have a lot of records in the table "data_TVNational" that are NULL? (data_TVNational.Geography is NULL??)
How many records?

How many records are returned using:

SELECT IIf(nat_tv.Geography="National","National","Local") AS ReportType, nat_tv.Geography, nat_tv.Indication, nat_tv.[Variable Name], nat_tv.[Variable Value]
FROM data_TVNational AS nat_tv
WHERE nat_tv.Geography IS NOT NULL;

[June7]

Want to provide sample data? Follow instructions at bottom of my post.

[kestefon]

Thanks, that works. I'm still stumped as to where the null value is, but this will be a great fix for the time being.

[ssanfu]

Whew, finally!!

I'm still stumped as to where the null value is

As a test, you could try:

SELECT * FROM data_TVNational WHERE data_TVNational.Geography IS NULL

just to see what records are returned.

Good luck with your project.