Problem with view

[Chris]

I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.

Here is the t-sql code I used to create the view:

CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''

Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?

[Plamen Ratchev]

When you have UNION the columns in the first SELECT statement define the
datatype of the columns. The columns in the following SELECT statements must
match the datatype or be implicitly convertible. In your case implicit
conversion from varchar to float is allowed. Then the problem is that a
column in your first SELECT statement is of type float and the same column
in the second SELECT is a varchar, but the varchar column has some
characters that cannot be converted to float.

Without seeing the table definitions and just guessing it seems that your
_tblLeads.Zip might be of type float and tblClients.Zip is of type varchar.
Most likely tblClients.Zip has some invalid characters that cannot be
converted to float. One solution is to replace the column
[dbo].[_tblLeads].[Zip] in your first SELECT (if that is the correct column)
with an explicit conversion to varchar, like CAST([dbo].[_tblLeads].[Zip] AS
varchar). To be more precise you can convert to varchar(n) where n is the
exact size of the column.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[harry9katz]

Hi
What are the field format at each table?
Harry

On Jan 23, 1:34 am, "Chris" <[e-mail]:

I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.

Here is the t-sql code I used to create the view:

CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''

Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?