Duplicate column names in the result are not supported in big query

Consider the below example:

#standardSQL
WITH inv AS (
  SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'inv' AS type UNION ALL
  SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'inv' AS type 
), prof AS (
  SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'prof' AS type UNION ALL
  SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'prof' AS type 
)
SELECT inv.*, prof
FROM inv FULL JOIN prof
USING (company, vendor, transaction)

Instead of SELECT inv.*, prof.* which obviously ends up with Duplicate column names in the result are not supported. ... use SELECT inv, prof as shown below

#standardSQL
WITH inv AS (
  SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'inv' AS type UNION ALL
  SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'inv' AS type 
), prof AS (
  SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'prof' AS type UNION ALL
  SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'prof' AS type 
)
SELECT inv, prof
FROM inv FULL JOIN prof
USING (company, vendor, transaction)

result :

Row inv.company inv.vendor  inv.transaction inv.type    prof.company    prof.vendor prof.transaction    prof.type    
1     001           abc         800.0             inv         001             abc           800.0               prof     
2     002           efg         23.4              inv         002             efg           23.4                prof     

As you can see resulted row now has two structs/records - each holding respective entry from respective table

Bozhack-miller

posted on 06 Jan 19