2

I have an Access 2013 database with a table named [carInsurance] as follows:

enter image description here

Now I want to display all the data in this table along with an "empty" row full of NULL values. So I use a UNION query to accomplish that as follows: 

select NULL as insId, NULL as carId, NULL as insFromDate from carInsurance 
UNION 
select insId, carId, insFromDate from carInsurance

However, the results show up like this

enter image description here

and when I use UNION ALL as follows: 

select NULL as insId, NULL as carId, NULL as insFromDate from carInsurance 
UNION ALL 
select insId, carId, insFromDate from carInsurance

I get all results correct except that I get an empty line for every result. How can I solve this problem?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Hilal Al-Rajhi
  • 437
  • 6
  • 25
  • 49

1 Answers1

3

I'm going to go on a hunch. I think it is because of different data types being chosen when you give it just NULL. Beats me why it would be different for UNION vs UNION ALL.

Try this:

Select 
    cast (NULL as int) as insId, 
    cast (NULL as int) as carId, 
    cast (NULL as Date) as insFromDate 
from carInsurance 
UNION  
Select insId, carId, insFromDate from carInsurance

Extending the same line of thought, I would suggest is reversing the order of your SELECTs:

Select insId, carId, insFromDate from carInsurance 
UNION  
Select NULL as insId, NULL as carId, NULL as insFromDate from carInsurance
Raj More
  • 47,048
  • 33
  • 131
  • 198
  • I get the following message: Syntax Error(missing operator) in query expression 'cast (NULL as int) – Hilal Al-Rajhi Feb 06 '14 at 19:40
  • All right, your second solution works perfectly without any problem. Thank you Mr. Raj – Hilal Al-Rajhi Feb 06 '14 at 19:42
  • Mr. Raj, any idea why when I try to save data using adapter.update(ds,"table") it show the following error: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information. – Hilal Al-Rajhi Feb 07 '14 at 07:01
  • Are you querying a table or a view? If table, does your table have a Primary Key? – Raj More Feb 10 '14 at 16:12