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\nWITH inv AS (\n  SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'inv' AS type UNION ALL\n  SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'inv' AS type \n), prof AS (\n  SELECT'001' AS company,'abc' AS vendor,800.00 AS transaction,'prof' AS type UNION ALL\n  SELECT'002' AS company,'efg' AS vendor,23.4 AS transaction,'prof' AS type \n)\nSELECT inv, prof\nFROM inv FULL JOIN prof\nUSING (company, vendor, transaction)\n

result :

Row inv.company inv.vendor  inv.transaction inv.type    prof.company    prof.vendor prof.transaction    prof.type    \n1     001           abc         800.0             inv         001             abc           800.0               prof     \n2     002           efg         23.4              inv         002             efg           23.4                prof     \n

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

Bozhack-miller

posted on