06 January 2019 · Bozhack-miller ·       Add to Favorites   Report

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 January 2019

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied