Union All query

Raja,
I have one one query, I have posted a new question.
Thanks

As the error mentions when we UNION ALL two queries, both queries should have same set of columns available.
Please modify your second query to have all the columns available in your first SELECT statement. Query to be modified is..

union all SELECT Route,DateTo,DateFrom FROM  [PROT]  WHERE  [InvoiceNumber]  = 'XYZ'

Select allOpen in new window

Thanks...trying..

Raja,
It is a difference table , it cannot have all fields from Table1

Thanks

Basically, I am trying to extract fields from 2 tables and have all the fields in the result

Thanks

You can use output nulls for the columns that do not exist, below query should work.

 
SELECT RequestedBy,Authority,Date,Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	             Guestname,ConfirmationNumber,ArrivalDate,ArrivalDate2,ArrivalDate3,ArrivalDate4,ArrivalDate5,ArrivalDate6,Attention,Roomno, Roomno2,Roomno3,Roomno4,Roomno5,Roomno6,
		(CASE
                 WHEN Extendedto IS NULL THEN DepartureDate
                 ELSE Extendedto
        END) AS DepartureDate,
		(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
        END) AS DepartureDate2,
		(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
        END) AS DepartureDate3,
			(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
        END) AS DepartureDate4,
		(CASE
                  WHEN Extendedto5 IS NULL THEN DepartureDate5
                  ELSE Extendedto5
        END) AS DepartureDate5,
		(CASE
                  WHEN Extendedto6 IS NULL THEN DepartureDate6
                  ELSE Extendedto6
        END) AS DepartureDate6,
        NULL Route,NULL DateTo,NULL DateFrom
        
         FROM  [CORR]  WHERE  [Ref]  = '29' AND  [Year_Ref] =24         
        union all 
        
		SELECT NULL RequestedBy, NULL Authority, Date,Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	             NULL Guestname,NULL ConfirmationNumber,NULL ArrivalDate,NULL ArrivalDate2,NULL ArrivalDate3,NULL ArrivalDate4,NULL ArrivalDate5,NULL ArrivalDate6,NULL Attention,NULL Roomno, NULL Roomno2,NULL Roomno3,NULL Roomno4,NULL Roomno5,NULL Roomno6,
	             NULL DepartureDate,NULL DepartureDate2,NULL DepartureDate3, NULL DepartureDate4, NULL DepartureDate5, NULL DepartureDate6,
	             Route,DateTo,DateFrom
	             
	    FROM  [PROT]  WHERE  [InvoiceNumber]  = 'XYZ'

Select allOpen in new window

Thanks...trying

Msg 207, Level 16, State 1, Line 37
Invalid column name 'Date'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'Ref'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'CorresType'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'SentTo'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'Regarding'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'RoomTypeRouting'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'RoomTypeRouting2'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'RoomTypeRouting3'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'RoomTypeRouting4'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'RoomTypeRouting5'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'RoomTypeRouting6'.
Msg 207, Level 16, State 1, Line 37
Invalid column name 'Faxnumber'.

These columns are not part of TABLE2 [PROT]

Thanks

As mentioned by Rufus, you can have the other column values as NULL but you don't have the columns Route, DateTo, DateFrom in your first SELECT clause and the columns in second SELECT clause is not matching.
Kindly let us know how you wish to join these 2 SELECT statements.

The requirement was selecting col1,col2 from Table1 and Col3,col4 from Table2 .
Any suggestions?
Thanks

Apologies. I missed few columns in my previous query.
Try the below query...

  SELECT RequestedBy,Authority,Date,Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	             Guestname,ConfirmationNumber,ArrivalDate,ArrivalDate2,ArrivalDate3,ArrivalDate4,ArrivalDate5,ArrivalDate6,Attention,Roomno, Roomno2,Roomno3,Roomno4,Roomno5,Roomno6,
		(CASE
                 WHEN Extendedto IS NULL THEN DepartureDate
                 ELSE Extendedto
        END) AS DepartureDate,
		(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
        END) AS DepartureDate2,
		(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
        END) AS DepartureDate3,
			(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
        END) AS DepartureDate4,
		(CASE
                  WHEN Extendedto5 IS NULL THEN DepartureDate5
                  ELSE Extendedto5
        END) AS DepartureDate5,
		(CASE
                  WHEN Extendedto6 IS NULL THEN DepartureDate6
                  ELSE Extendedto6
        END) AS DepartureDate6,
        NULL Route,NULL DateTo,NULL DateFrom
        
         FROM  [CORR]  WHERE  [Ref]  = '29' AND  [Year_Ref] =24         
        union all 
        
		SELECT NULL RequestedBy, NULL Authority,NULL  Date,NULL Ref,NULL CorresType, NULL Comments,NULL SentTo,NULL Regarding,NULL RoomTypeRouting,NULL RoomTypeRouting2,NULL RoomTypeRouting3,NULL RoomTypeRouting4,NULL RoomTypeRouting5,NULL RoomTypeRouting6,NULL Faxnumber,
	             NULL Guestname,NULL ConfirmationNumber,NULL ArrivalDate,NULL ArrivalDate2,NULL ArrivalDate3,NULL ArrivalDate4,NULL ArrivalDate5,NULL ArrivalDate6,NULL Attention,NULL Roomno, NULL Roomno2,NULL Roomno3,NULL Roomno4,NULL Roomno5,NULL Roomno6,
	             NULL DepartureDate,NULL DepartureDate2,NULL DepartureDate3, NULL DepartureDate4, NULL DepartureDate5, NULL DepartureDate6,
	             Route,DateTo,DateFrom
	             
	    FROM  [PROT]  WHERE  [InvoiceNumber]  = 'XYZ'

Select allOpen in new window

>> The requirement was selecting col1,col2 from Table1 and Col3,col4 from Table2 .

just wondering, why do you need to join 2 set of non matching columns to a single result set..
Kindly request you to clarify your exact requirement so that we can guide you accordingly..

Table1
Col1 Col2 Col3

Table2
Col4 Col5 Col6

Result :

Col1 Col2 Col3 Col4 Col5 Col6

Thanks

Raja,
That null worked but I am getting error:
Conversion failed when converting date and/or time from character string.

Requirement :
Table1

Columns:
Refno , Dateom,dateto

Table 2
Columns:

route,Datefrom,Dateto


Result is the output of all columns:
Refno , Dateom,dateto,route,Datefrom,Dateto




Thanks

Rufus,
How do i solve this error in your query :
Conversion failed when converting date and/or time from character string.

is Union All the only option ?

Generally, if you want to merge two datasets into a single set of records, then you either use UNION (filters out duplicate records), UNION ALL, or some form of JOIN syntax (if there are fields that the 2nd table has in common with the first table).

Ok ,Dale thanks will try your query

RIAS,

What you have to keep in mind is that:
1.  The number of columns defined in each part (there can be more than two) of a union query must match
2.  The data types of all of each of the fields in a UNION or UNION All query must also match, so if the first column in part 1 is numeric, then the first column in part 2 must also be numeric, ...

When I do a union query, I frequently add an additional column to each "part", so that I know where each row came from, something like:

SELECT 1 as Source, Field1, Field2, ..., FieldN FROM Table1
UNION
SELECT 2 as Source, Field1, Field2, ..., FieldN FROM Table2

Select allOpen in new window

Thanks Rufus!!

I will try and brb

how to convert the fields ',DateTo,DateFrom'  to null if there is no date in the result returned. Please find it highlighted



SELECT NULL RequestedBy, NULL Authority,NULL  Date,NULL Ref,NULL CorresType, NULL Comments,NULL SentTo,NULL Regarding,NULL RoomTypeRouting,NULL RoomTypeRouting2,NULL RoomTypeRouting3,NULL RoomTypeRouting4,NULL RoomTypeRouting5,NULL RoomTypeRouting6,NULL Faxnumber,
                   NULL Guestname,NULL ConfirmationNumber,NULL ArrivalDate,NULL ArrivalDate2,NULL ArrivalDate3,NULL ArrivalDate4,NULL ArrivalDate5,NULL ArrivalDate6,NULL Attention,NULL Roomno, NULL Roomno2,NULL Roomno3,NULL Roomno4,NULL Roomno5,NULL Roomno6,
                   NULL DepartureDate,NULL DepartureDate2,NULL DepartureDate3, NULL DepartureDate4, NULL DepartureDate5, NULL DepartureDate6,
                   Route,DateTo,DateFrom

Same problem : Conversion failed when converting date and/or time from character string.

do you have any other date or time columns in either the 1st part or 2nd part of the query?  You will need to use the syntax mentioned by Rufas for each of those date or time columns.

checking...and brb

best way to check is to highlight the first part of the query and run it, without the union, and review all of the resulting columns.  Then make the appropriate changes to the NULLs in the 2nd part of the UNION query.

ok

Tried everything same ....

SELECT RequestedBy,Authority,[Date],Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	             Guestname,ConfirmationNumber,CAST(null as date) as  ArrivalDate,CAST(null as date) as ArrivalDate2,CAST(null as date) as  ArrivalDate3,CAST(null as date) as ArrivalDate4,CAST(null as date) as ArrivalDate5,CAST(null as date) as  ArrivalDate6,Attention,Roomno, Roomno2,Roomno3,Roomno4,Roomno5,Roomno6,(CASE
                 WHEN Extendedto IS NULL THEN DepartureDate
                 ELSE Extendedto
        END) AS DepartureDate,
		(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
        END) AS DepartureDate2,
		(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
        END) AS DepartureDate3,
			(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
        END) AS DepartureDate4,
		(CASE
                  WHEN Extendedto5 IS NULL THEN DepartureDate5
                  ELSE Extendedto5
        END) AS DepartureDate5,
		(CASE
                  WHEN Extendedto6 IS NULL THEN DepartureDate6
                  ELSE Extendedto6
        END) AS DepartureDate6 FROM  [Corr]  WHERE  [Ref]  = '179' AND  [Year_Ref] =24
		 union all 
		 
   SELECT null as RequestedBy ,null as Authority,CAST(NULL as date),null as Ref,null as CorresType, null as Comments,null as SentTo,null as Regarding,null as RoomTypeRouting,null as RoomTypeRouting2,null as RoomTypeRouting3,null as RoomTypeRouting4,null as RoomTypeRouting5,null as RoomTypeRouting6,null as Faxnumber,
	      null as Guestname,null as ConfirmationNumber,CAST(NULL as date),CAST(NULL as date),CAST(NULL as date),CAST(NULL as date),CAST(NULL as date),CAST(NULL as date),null as Attention,null as Roomno, null as Roomno2,null as Roomno3,null as Roomno4,null as Roomno5,null as Roomno6 ,CAST(NULL as date),CAST(NULL as date) ,CAST(NULL as date),	  
	    [Route],   CAST(null as datetime) as datefrom ,  CAST(null as datetime) as dateto  FROM  [PROT]  WHERE  [InvoiceNumber]  = 'XYZ'

Select allOpen in new window

Thanks

It worked great! Thanks experts . I need to accept two answers here, at the moment it allows me to accept only one answer .
Any suggestions?

Thanks

I think you misunderstood the issue of CAST(NULL as Date).  You don't need to do that in the upper half of the query.  Those fields are already defined as dates and if they do not have a value, they will already contain NULLs, so replace:

CAST(null as date) as  ArrivalDate

with:

ArrivalDate

Same what? That you don't have the same number of columns?   If so, I would remove the UNION ALL line and run these two queries at the same time.  This will help you do a side-by-side comparison of the two halves of the query to determine what fields are missing from each half of the query.

But before you do that, go to the bottom half of the query, and name each of the columns.  For example, in the 2nd part of the query, your 3rd column is defined as:

CAST(NULL as date)

Change this to read:

CAST(NULL as date) as [Date]

Do this with all of the date columns in the 2nd half of the query, so that when you do a comparison of the field names between the two halves, they all have the same column names.

You should be able to select best solution, and then go to the other solution and select Assisted Solution.

It gives me the option to select only the best solution .

This is probably the new (user very unfriendly) interface.  If you mark a comment as helpful it should automatically be an assist when you close.

Sorry Experts,
One more error:


String or binary data would be truncated"

ALTER PROCEDURE  [dbo].[Wrt]
(
     @tableName varchar(255) = null,
	 @ColumnName1 varchar(255) = null,
	 @Value varchar(255) = null,
	 --Table2
	 @tableName2 varchar(255) = null,
	 @ColumnName2 varchar(255) = null,
	 @Value2 varchar(255) = null


)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(max) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]
	   DECLARE @Year_Ref smallint
		SET @Year_Ref = (SELECT [Year_Ref] FROM YEAR_LOOKUP  WHERE [Year_no]= YEAR(GETDATE()))

	

	
		
	SET @SQL = '  SELECT RequestedBy,Authority,[Date],Ref,CorresType, Comments,SentTo,Regarding,RoomTypeRouting,RoomTypeRouting2,RoomTypeRouting3,RoomTypeRouting4,RoomTypeRouting5,RoomTypeRouting6,Faxnumber,
	      Guestname,ConfirmationNumber , ArrivalDate , ArrivalDate2,  ArrivalDate3,ArrivalDate4, ArrivalDate5,  ArrivalDate6 ,Attention,Roomno, Roomno2,Roomno3,Roomno4,Roomno5,Roomno6,
		(CASE
                 WHEN Extendedto IS NULL THEN DepartureDate
                 ELSE Extendedto
        END) AS DepartureDate,
		(CASE
                  WHEN Extendedto2 IS NULL THEN DepartureDate2
                  ELSE Extendedto2
        END) AS DepartureDate2,
		(CASE
                  WHEN Extendedto3 IS NULL THEN DepartureDate3
                  ELSE Extendedto3
        END) AS DepartureDate3,
			(CASE
                  WHEN Extendedto4 IS NULL THEN DepartureDate4
                  ELSE Extendedto4
        END) AS DepartureDate4,
		(CASE
                  WHEN Extendedto5 IS NULL THEN DepartureDate5
                  ELSE Extendedto5
        END) AS DepartureDate5,
		(CASE
                  WHEN Extendedto6 IS NULL THEN DepartureDate6
                  ELSE Extendedto6
        END) AS DepartureDate6' 
	 
	 
	  SET @SQL = @SQL + ' FROM  [' + @tableName + '] '   + ' WHERE  [' + @ColumnName1 + ']  = ''' +   '' +  @Value +''''+ ' AND  [Year_Ref] =' + convert(varchar,@Year_Ref)
	
	  SET @SQL = @SQL 
	  
	  

	  IF  @Value2  <> ''		
	  SET @SQL = @SQL +  'union all '
	  IF  @Value2  <> ''		
	 SET @SQL = @SQL +  ' SELECT null as RequestedBy ,null as Authority,CAST(NULL as date),null as Ref,null as CorresType, null as Comments,null as SentTo,null as Regarding,null as RoomTypeRouting,null as RoomTypeRouting2,null as RoomTypeRouting3,null as RoomTypeRouting4,null as RoomTypeRouting5,null as RoomTypeRouting6,null as Faxnumber,
	      null as Guestname,null as ConfirmationNumber,	 CAST(null as date) as ArrivalDate ,CAST(null as date) as ArrivalDate2,CAST(null as date) as  ArrivalDate3,CAST(null as date) as ArrivalDate4,CAST(null as date) as ArrivalDate5,CAST(null as date) as  ArrivalDate6,null as Attention,null as Roomno, null as Roomno2,null as Roomno3, null as Roomno4,null as Roomno5,null as Roomno6, null as  DepartureDate,CAST(null as date) as  DepartureDate2,
		  CAST(null as date) as   DepartureDate3, CAST(null as date)  as  DepartureDate4, CAST(null as date) as   DepartureDate5, CAST(null as date) DepartureDate6 ' 	
	  IF  @Value2  <> ''		 
	  SET @SQL = @SQL + ' FROM  [' + @tableName2 + '] '   + ' WHERE  [' + @ColumnName2 + ']  = ''' +   '' +  @Value2 +''''	
	  SET @SQL = @SQL  
	  

Select allOpen in new window

This should probably be another question, about how to format a dynamic SQL string, since you got the Union query to work already.

Take all of that code you have written for the dynamic SQL and put it in a query.

Then, remark out the last line and replace it with

SELECT @SQL

This should return your SQL string as a string of text which you can then cut and paste into a query to examine it easier.

BTW, when you paste that into a query, wrap the text so that you can see it all without having to scroll to the right.

Dale

Thanks experts!!!!