rmondell_gbq
rmondell_gbq

Reputation: 9

How to SELECT _TABLE_SUFFIX or the like to differentiate data

Querying multiple tables using UNION ALL, and the various tables do not have a field that differentiates the data from each table and so the idea is to populate a field with the table name or other identifier in order to differentiate the data based on what table it originated from.

Select _TABLE_SUFFIX

SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `1_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `2_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `3_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `4_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `5_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `7_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
  Posting_Date, 
  Entry_No_,
  Document_No_, 
  G_L_Account_No_,
  Global_Dimension_1_Code,
  Global_Dimension_2_Code,
  Description,
  Amount
FROM
  `8_G_L_Entry`
  WHERE
  Posting_Date > '2019-03-31'
  AND G_L_Account_No_ >'399999'
ORDER BY
  Entry_No_ ASC

Selects the table name from each table queried or other method to differentiate the data...

Upvotes: 0

Views: 2000

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59375

_table_suffix will only work when you have a * expansion:

SELECT _table_suffix suffix, COUNT(*) c
FROM `fh-bigquery.stackoverflow_archive_questions.q*`
GROUP BY 1 
ORDER BY 1 DESC 

enter image description here

But when doing an UNION you'll need to manually 'tag' each table:

SELECT table, COUNT(*) c
FROM (
  SELECT *, '201703' table
  FROM `fh-bigquery.stackoverflow_archive_questions.q201703` 
  UNION ALL
  SELECT *, '201706' table
  FROM `fh-bigquery.stackoverflow_archive_questions.q201706` 
  UNION ALL
  SELECT *, '201709' table
  FROM `fh-bigquery.stackoverflow_archive_questions.q201709` 
)
GROUP BY 1 
ORDER BY 1 DESC 

enter image description here

Upvotes: 3

Related Questions