CODEWITHSUNDEEP
sqlsql-serverunion
Vaibhav
Vaibhav

Reputation: 105

Error while using Union in SQL Server query

I am trying to combine same information from 2 views and both queries individually are producing results. However, as soon as I use Union to combine them, it gives error:

Error converting data type varchar to bigint.

I have tried using alias for table name as well but it shows the same error.

Here is my query

SELECT DISTINCT
       syn.sourcename,
       syn.propertyname,
       syn.IATA,
       syn.iataagencyname,
       syn.iataagencyaffiliation,
       syn.nightsstayed,
       syn.reservationstatus,
       syn.departuredate,
       syn.reservationroomrate
FROM dbo.vwIATAfactreservationSynXis syn
WHERE syn.reservationstatus != 'C'
  AND syn.departuredate >= CONVERT(datetime, '01/01/2020')
  AND syn.departuredate <= CONVERT(datetime, GETDATE())
UNION
SELECT DISTINCT
       ws.sourcename,
       ws.propertyname,
       ws.IATA,
       ws.iataagencyname,
       ws.iataagencyaffiliation,
       ws.nightsstayed,
       ws.reservationstatus,
       ws.departuredate,
       ws.reservationroomrate
FROM dbo.vwIATAfactreservationSHR ws
WHERE ws.reservationstatus != 'C'
  AND ws.departuredate >= CONVERT(datetime, '01/01/2020')
  AND ws.departuredate <= CONVERT(datetime, GETDATE());

Upvotes: 1

Views: 1117

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You need to figure out which columns are not the same . . . and then convert them. You can use INFORMATION_SCHEMA.COLUMNS for this purpose:

select column_name, data_type, count(*)
from information_schema.columns c
where table_name in ('vwIATAfactreservationSynXis', 'vwIATAfactreservationSHR')
group by column_name, data_type
having count(*) <> 2
order by column_name;

This should be close enough (unless the two views have zillions of columns not mentioned in the query). If there are lots of singleton columns, then filter on the columns used in the original query. Then, whatever columns have different types -- well, the integer should be converted to a string.

If you don't want to go through that bother, you can just convert all columns in both subqueries -- or all suspect columns -- to strings:

SELECT CONVERT(VARCHAR(255), syn.sourcename) as sourcename,
       CONVERT(VARCHAR(255), syn.propertyname) as proprtyname,
       . . .

Upvotes: 1

Hadi Ardebili
Hadi Ardebili

Reputation: 109

try casting the fields in both select statements to string one by one to find the field that is making the problem like the sample below :

SELECT DISTINCT
       cast(syn.sourcename as nvarchar(max)),
       syn.propertyname,
       syn.IATA,
       syn.iataagencyname,
       syn.iataagencyaffiliation,
       syn.nightsstayed,
       syn.reservationstatus,
       syn.departuredate,
       syn.reservationroomrate
FROM dbo.vwIATAfactreservationSynXis syn
WHERE syn.reservationstatus != 'C'
  AND syn.departuredate >= CONVERT(datetime, '01/01/2020')
  AND syn.departuredate <= CONVERT(datetime, GETDATE())
UNION
SELECT DISTINCT
       cast(syn.sourcename as nvarchar(max)),
       ws.propertyname,
       ws.IATA,
       ws.iataagencyname,
       ws.iataagencyaffiliation,
       ws.nightsstayed,
       ws.reservationstatus,
       ws.departuredate,
       ws.reservationroomrate
FROM dbo.vwIATAfactreservationSHR ws
WHERE ws.reservationstatus != 'C'
  AND ws.departuredate >= CONVERT(datetime, '01/01/2020')
  AND ws.departuredate <= CONVERT(datetime, GETDATE());

Upvotes: 1

Related Questions