CODEWITHSUNDEEP
sql
Farid Arshad
Farid Arshad

Reputation: 334

SQL Union 2 table with union with one table having column as null

For given example , i am planing to do a union of 2 tables

table A
country_code  | arrg_id  | arrg_desc
sg              test1       est_desc
..               ..          ..

table B
country_code  | arrg_desc 
sg              test_2

given an example above, i would like to union both of this 2 table.

i have came out with query

select Business as 'Retail' , country_code as country , arrg_id as arrg_id from table_A
union
select Business as 'Retail' , country_code as, 'NA' as arrg_id from table_b

as i run the query specified, i was receiving error on : error: (HY000, None) AnalysisException: Incompatible return types 'DOUBLE' and 'STRING'

However if i cast as string, this would work

select Business as 'Retail' , country_code as country , cast(arrg_id as String) as arrg_id from table_A
union
select Business as 'Retail' , country_code as, 'NA' as arrg_id from table_b

i was wondering if this would be the best approach or it will disturb the integrity of the data , would need some advices on this.

Upvotes: 0

Views: 2406

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

The rule of thumb for taking a union between two (or more) tables is that the types of columns in both halves of the union should always be the same. Presumably here, the arrg_id in the A table is numeric. This means that if you want to union this value with the string literal 'NA', then you would either need the cast in your example above, or, you would need to union arrg_id with another numeric value (e.g. perhaps -1). So, the query you suggested with the cast to string is correct.

Upvotes: 2

Related Questions