Union Help SQL Server 2000

[runnerlk]

I am trying to create a union join in SQL Server 2000 std ed. and do not have the same number of fields in each table, I am not sure of the syntax to add fields to the tables to make them equal. Also not sure if the field names are not the same for instance, Date vs date entered would that be an issue?

Thanks,

Lou

 

[unclerico]

can post your code??

 

[runnerlk]

SELECT "Booking_Sheet"."UCR_Code", "Booking_Sheet"."Case_Number", "Booking_Sheet"."Arrest_Date", "Booking_Sheet"."Date_of_Birth", "Booking_Sheet"."Race", "Booking_Sheet"."Sex", "Booking_Sheet"."Drug_Type", "Booking_Sheet"."USE"
FROM "WilliamsportPolice"."dbo"."Booking_Sheet" "Booking_Sheet"
WHERE ("Booking_Sheet"."Arrest_Date">={ts '2006-01-01 00:00:00'} AND "Booking_Sheet"."Arrest_Date"<{ts '2006-07-13 00:00:00'})

UNION ALL


SELECT "Non_Traffic_Citations"."UCR_Code", "Non_Traffic_Citations"."Case_Number", "Non_Traffic_Citations"."Date", "Non_Traffic_Citations"."Subject_Date_of_Birth", "Non_Traffic_Citations"."Subject_Race", "Non_Traffic_Citations"."Subject_Sex"
FROM "WilliamsportPolice"."dbo"."Non_Traffic_Citations" "Non_Traffic_Citations"
WHERE ("Non_Traffic_Citations"."Date">={ts '2006-01-01 00:00:00'} AND "Non_Traffic_Citations"."Date"<{ts '2006-07-13 00:00:00'})

UNION ALL


SELECT "FPL6"."Item", "FPL6"."Alias_1", "FPL6"."Alias_2", "FPL6"."Alias_3", "FPL6"."Alias_4", "FPL6"."Alias_5"
FROM "WilliamsportPolice"."dbo"."FPL6" "FPL6"

 

[jbenson001]

YOu don't have to use the same name for the union, it will use the column name from the first select.

 

[gmmastros]

When using union (or union all), the number of columns in each select must match AND the data types must match.

In the query you show, the first select returns 8 fields, but the other 2 queries return 6 fields.

The first query has Drug_Type and Use as the field names being returned, but the second and 3 selects don't. You can 'hard code' a value for those other 2 selects, like this...

SELECT "Booking_Sheet"."UCR_Code", "Booking_Sheet"."Case_Number", "Booking_Sheet"."Arrest_Date", "Booking_Sheet"."Date_of_Birth", "Booking_Sheet"."Race", "Booking_Sheet"."Sex", "Booking_Sheet"."Drug_Type", "Booking_Sheet"."USE"
 FROM   "WilliamsportPolice"."dbo"."Booking_Sheet" "Booking_Sheet"
 WHERE  ("Booking_Sheet"."Arrest_Date">={ts '2006-01-01 00:00:00'} AND "Booking_Sheet"."Arrest_Date"<{ts '2006-07-13 00:00:00'})

UNION ALL


 SELECT "Non_Traffic_Citations"."UCR_Code", "Non_Traffic_Citations"."Case_Number", "Non_Traffic_Citations"."Date", "Non_Traffic_Citations"."Subject_Date_of_Birth", "Non_Traffic_Citations"."Subject_Race", "Non_Traffic_Citations"."Subject_Sex"[!], NULL, NULL[/!]
 FROM   "WilliamsportPolice"."dbo"."Non_Traffic_Citations" "Non_Traffic_Citations"
 WHERE  ("Non_Traffic_Citations"."Date">={ts '2006-01-01 00:00:00'} AND "Non_Traffic_Citations"."Date"<{ts '2006-07-13 00:00:00'})

UNION ALL


 SELECT "FPL6"."Item", "FPL6"."Alias_1", "FPL6"."Alias_2", "FPL6"."Alias_3", "FPL6"."Alias_4", "FPL6"."Alias_5"[!], NULL, NULL[/!]
 FROM   "WilliamsportPolice"."dbo"."FPL6" "FPL6"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[runnerlk]

Thanks jbenson001,

How do i pad the tables so they all have the same number of fields. A union must have the same number.

 

[gmmastros]

In addition to my most recent post, I wanted to mention the 'format' of your query. Instead of using quotes around your table names and field names, it is better to use square brackets. For example...

SELECT [FPL6].[Item], 
       [FPL6].[Alias_1], 
       [FPL6].[Alias_2], 
       [FPL6].[Alias_3], 
       [FPL6].[Alias_4], 
       [FPL6].[Alias_5], 
       NULL, 
       NULL
FROM   [WilliamsportPolice].[dbo].[FPL6] [FPL6]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[runnerlk]

Thanks George!! What will happen if the data types are not correct?

 

[gmmastros]

You will get an error because it tries to convert the data types for you.

Sometimes it will appear to work, but it could also be that you are getting lucky. You should never rely on luck. If you *really* need to combine different data types, then you should probably convert the data to a varchar data type. However, if you have different meanings for the data from each of the tables, then you really shouldn't be using a union. Instead, you should probably be returning different fields.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[runnerlk]

OK, understood. But if I have, let's say a varchar(25) and a char(25). I really don't want to modify the tables. I am creating the union to provide a data source for Crystal Reports.

Thanks,

Lou

 

[gmmastros]

A varchar(25) and a char(25) will always convert for you (because the data types are very similar).

Of bigger concern would be dates and numbers. If you really need to put both data types in to the same union query, then you should convert the dates and numbers to a varchar data type in the individual selects. Ex...

SELECT Booking_Sheet.UCR_Code, 
       Booking_Sheet.Case_Number, 
       [!]Convert(VarChar(10, [/!]Booking_Sheet.Arrest_Date[!], 101) As Arrest_Date[/!], 
       Booking_Sheet.Date_of_Birth, 
       Booking_Sheet.Race, 
       Booking_Sheet.Sex, 
       Booking_Sheet.Drug_Type, 
       Booking_Sheet.[USE]
FROM   WilliamsportPolice"."dbo"."Booking_Sheet" "Booking_Sheet"
WHERE  ("Booking_Sheet"."Arrest_Date">={ts '2006-01-01 00:00:00'} AND "Booking_Sheet"."Arrest_Date"<{ts '2006-07-13 00:00:00'})

UNION ALL

SELECT "Non_Traffic_Citations"."UCR_Code", 
       "Non_Traffic_Citations"."Case_Number", 
       [!]Convert(VarChar(10), [/!]"Non_Traffic_Citations"."Date"[!], 101)[/!],
       "Non_Traffic_Citations"."Subject_Date_of_Birth", 
       "Non_Traffic_Citations"."Subject_Race", 
       "Non_Traffic_Citations"."Subject_Sex", 
       NULL, 
       NULL
FROM   "WilliamsportPolice"."dbo"."Non_Traffic_Citations" "Non_Traffic_Citations"
WHERE  ("Non_Traffic_Citations"."Date">={ts '2006-01-01 00:00:00'} AND "Non_Traffic_Citations"."Date"<{ts '2006-07-13 00:00:00'})

UNION ALL

SELECT "FPL6"."Item", 
       "FPL6"."Alias_1", 
       "FPL6"."Alias_2", 
       "FPL6"."Alias_3", 
       "FPL6"."Alias_4", 
       "FPL6"."Alias_5", 
       NULL, 
       NULL
FROM   "WilliamsportPolice"."dbo"."FPL6" "FPL6"

This modification only affects the 3rd field returned, I'll leave it up to you to modify other fields as needed. The point I'm trying to make is that the 3 field returned by this query appears to get a date field in the first 2 selects, but is probably a varchar/char field in the 3rd select. Since all dates can be converted to a string, then converting the data from the first to selects should allow this to work.

Stated another way...

The union query combines results from the 3 selects. The first select uses booking_sheet.Arrest_date (presumably stored as a date). The second select uses Non_Traffic_Citations.Date (also a date). The third uses FPL6.Alias_2 which appears to be a string (varchar/char). By converting the data in the first 2 selects to be varchar, you should be able to combine the data from the 3rd table to it.

I hope I'm making myself clear. If not, post back and I will explain further.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom