views across databases with Union ALL Statements

• Craig Stonehouse

  SSChasing Mays

  Points: 602

  More actions

  April 4, 2007 at 6:28 pm

  #123533

  Hi All,

  I've been having some problems getting a union all statement to work. we have to databases that i've created crosstabed view on some reference tables that the users can add or remove the refernce columns from within the app. the problem is that not all the reference fields in the databases are the same. I can't change the way that the app work because it a thrid-party app.

  Now, I've created and script that looks at the two views from each database a works out which columns are missing from which view so that when the script creates the union all view is can add the a the column name as a null column, but this only works if the missing column is a string column.

  Does anyone have any ideas how i can get SQL to union a null column in one select to a non-string column.

  eg.

  SELECT

  a.column_a

  ,NULL AS column_b --varchar(10)

  ,a.column_c --DECIMAL(18,8)

  ,a.column_d --DECIMAL(18,8)

  FROM databases1.dbo.view1 AS a

  UNION ALL

  SELECT

  b.column_a

  b.Column_b

  ,NULL AS Column_C

  ,NULL AS Column_d

  FROM databases2.dbo.view2 AS b

  and when i execute this statement i get the only error message.

  Msg 8114, Level 16, State 5, Line 1

  Error converting data type varchar to numeric.

  Warning: Null value is eliminated by an aggregate or other SET operation.

  Thanks

  Craig

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  April 4, 2007 at 7:18 pm

  #698691

  ...

  , CONVERT(varchar(10), NULL) AS column_b

  ...

  _____________
  Code for TallyGenerator