Union Query issue

[skinny220]

I have below Union Query that work fine if all five queries are imported. Some times crosswalk-4 and crosswalk-5 do not have any data to import which cause the union query to error out. Is there a work around to have the union query resume.

SELECT Trim([F1]) AS UIC, ([F5]) AS [UNIT NAME], DLookUp("[DIR_LIST1]","DIR_LIST qUERY","[ID]=1") AS [PARENT UIC]
FROM [CROSSWALK-1]
WHERE (((Len([f2]))=3 Or (Len([f2]))=4))
UNION ALL
SELECT Trim([F1]) AS UIC, ([F5]) AS [UNIT NAME], DLookUp("[DIR_LIST1]","DIR_LIST qUERY","[ID]=2") AS [PARENT UIC]
FROM [CROSSWALK-2]
WHERE (((Len([f2]))=3 Or (Len([f2]))=4))
UNION ALL
SELECT Trim([F1]) AS UIC, ([F5]) AS [UNIT NAME], DLookUp("[DIR_LIST1]","DIR_LIST qUERY","[ID]=3") AS [PARENT UIC]
FROM [CROSSWALK-3]
WHERE (((Len([f2]))=3 Or (Len([f2]))=4))
UNION ALL
SELECT Trim([F1]) AS UIC, ([F5]) AS [UNIT NAME], DLookUp("[DIR_LIST1]","DIR_LIST qUERY","[ID]=4") AS [PARENT UIC]
FROM [CROSSWALK-4]
WHERE (((Len([f2]))=3 Or (Len([f2]))=4))
UNION ALL
SELECT Trim([F1]) AS UIC, ([F5]) AS [UNIT NAME], DLookUp("[DIR_LIST1]","DIR_LIST qUERY","[ID]=5") AS [PARENT UIC]
FROM [CROSSWALK-5]
WHERE (((Len([f2]))=3 Or (Len([f2]))=4))

 

[Micron]

I might be because Crosswalk-4 or 5 has no records, or could it be DIR_LIST qUERY? A DLookup that doesn't result in a found value returns null, so that might be what it's choking on. You could confirm that by trying only the SELECT Trim([F1]) AS UIC, ([F5]) AS [UNIT NAME] WHERE (((Len([f2]))=3 Or (Len([f2]))=4)) part. Or you could try wrapping F1 and F5 in the NZ function, specifying "" as the default. I presume both of those fields are text data type.

 

[xenou]

You can't query a table that doesn't exist so if there's no import then create the table anyway (with no data in it). I generally prefer to have the tables existing already so my imports append data rather than create tables. You can always delete all records from the data first so that the import is only the fresh data, or alternatively use a timestamp or some other means of identifying the recent data or the new batch of data.

 

[Micron]

Interesting take. I presumed there was no data because there were no records that satisfied the criteria, not that there was no table. Even after re-reading the original post, I still don't draw that conclusion, but perhaps you are correct.

 

[xenou]

Yes, but union works with empty tables, right?

Note -- as long as the datatypes are compatibles, the usual...

 

[james_lankford]

union works with empty tables

you can even to nonsensical stuff like

select 
  id, 
  first_name,
  last_name 
from 
  some_table 

union all 

select 
  null, 
  null,
  null 
from 
  some_table 
where 
  1 = 0