Select and union error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select and union error

Author

Topic

p3n
Starting Member

2 Posts

Posted - 2006-03-13 : 16:41:49

Alright, I have 4 tables I'm selecting from with 3 select statments

select id, firstName, lastName, startDate, endDate from table1, table2 where table1.id = table2.id

UNION

select id, firstName, lastName, startDate, NULL AS endDate from table3

UNION

select id, firstName, lastName, startDate, endDate from table4

ORDER BY lastName, firstName

Basically 4 fields from each table.

Problem is one table doesn't have on of the fields and I know when using UNION I have to have the same fields for each statemtn. I don't want to add an empty column to the table if I don't have to.

I keep getting either, MS Sql Server btw, ..:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

when I put Null AS EndDate

or

"Incorrect syntax near the keyword 'NULL'."

when I put SqlDateTime.Null AS EndDate

or

"Syntax error converting datetime from character string."

when I put 'NA'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-13 : 16:44:12

Why do you want to select the data from the 4 tables in one SQL statement? You are going to need to convert the data types where the conversions aren't implicit plus add columns like you've seen. Why do you even have the data spread across 4 tables? Is the database not normalized?

Tara Kizer
aka tduggan

p3n
Starting Member

2 Posts

Posted - 2006-03-14 : 14:32:40

Yeah, it's not normalized. I didn't create this database. I came into it. It's basically 2 tables for the new db, and a table for each of the other old databases.

So how do I select (using the union) a column that doesn't exist but store a NULL value as the column?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-14 : 14:59:53

p3n,

I tried the following and succeeded

Create table #t1 (F1 varchar(10), F2 datetime)
Create table #t2 (F1 varchar(10), F2 datetime)
Create table #t3 (F1 varchar(10), F2 datetime)

Insert Into #t1 (F1, F2)
Select 'aa', getdate()  
Union all
Select 'bb', getdate() + 1
Union all
Select 'cc', getdate() + 2
Union all
Select 'dd', getdate() + 3

Insert Into #t2 (F1)
Select 'ww'
Union all
Select 'xx'
Union all
Select 'yy'
Union all
Select 'zz'

Insert Into #t3 (F1, F2)
Select 'pp', getdate()  
Union all
Select 'qq', getdate() + 1
Union all
Select 'rr', getdate() + 2
Union all
Select 'ss', getdate() + 3


Select F1, F2 as Mydate from #T1
union all
Select F1, F2 as Mydate from #T2
union all
Select F1, F2 as Mydate from #T3

-- Edited - Added the following
-- or 

Select F1, F2 as Mydate from #T1
union all
Select F1, Null as Mydate from #T2
union all
Select F1, F2 as Mydate from #T3
--------------




Drop table #T1
Drop table #T2
Drop table #T3

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-14 : 15:41:09

select id, firstName, lastName, startDate, endDate from table1, table2 where table1.id = table2.id
UNION
select id, firstName, lastName, startDate, NULL AS endDate from table3
UNION
select id, firstName, lastName, startDate, endDate from table4

Are you sure the error comes from that line?
try
select id, firstName, lastName, startDate, endDate from table1, table2 where table1.id = table2.id
UNION
select id, firstName, lastName, startDate, endDate from table4

The query will get the datatypes from the first query so startdate and enddate will probably be datetimes. null will happily work in this column (are you sure you didn't type 'null'?).
Another option is that startdate in table3 is a character column and contains data that can't be converted to datetime implicitely.

Try removing queries and columns from the statement to find out what is causing the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-14 : 16:02:33

quote:
Originally posted by p3n

Alright, I have 4 tables I'm selecting from with 3 select statments

select id, firstName, lastName, startDate, endDate from table1, table2 where table1.id = table2.id

For that first batch, you are selecting columns from two tables but not indicating which table any of them come from. At least one column, ID, exists in both tables. that will give an error.

You should really use JOIN syntax also.

Also, is the EndDate column a DateTime datatype in both of the tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources