CODEWITHSUNDEEP
sqlgoogle-bigquerywith-statement
whands
whands

Reputation: 21

SQL multiple query using with - in Bigquery

I'm trying to figure out how to make a single request including several queries in order to generate one view including all my queries results.

Here the example :


WITH Query_1 AS
(
SELECT
dimension1,
dimension2,
dimension3,
dimension4,
dimension5,

CASE WHEN STARTS_WITH (dimension1, "N_") THEN '1' ELSE '0' END AS qadimension1,
CASE WHEN dimension2 IS NOT NULL THEN dimension2 ELSE '0' END AS qadimension2,
CASE WHEN dimension3 IS NOT NULL THEN dimension3 ELSE '0' END AS qadimension3,
CASE WHEN dimension4 IS NOT NULL THEN dimension4 ELSE '0' END AS qadimension4,
FROM `XXXXX.XXXXX.first_view`
WHERE dimension5='2021' AND dimension1 IS NOT NULL),

Query_2 AS (
SELECT
dimension1,
qadimension1,
dimension6,

CASE WHEN qadimension1 = dimension6 THEN '1' ELSE '0' END AS QA_check1,
FROM `XXXXX.XXXXX.first_view` , `XXXXX.XXXXX.second_view`
WHERE qadimension1 != '0'),


Query_3 AS (
SELECT
dimension1,
qadimension2,
dimension7,

CASE WHEN qadimension2 = dimension7 THEN '1' ELSE '0' END AS QA_check2,
FROM `XXXXX.XXXXX.first_view` , `XXXXX.XXXXX.third_view`
WHERE qadimension2 != '0')



SELECT 
dimension1,
dimension2,
dimension3,
dimension4,
dimension5,
dimension6,
dimension7,

qadimension1,
qadimension2,
qadimension3,
qadimension4,

QA_check1,
QA_check2,

FROM
Query_1,
Query_2,
Query_3

WHERE FULL JOIN dimension1;

still have an error : Syntax error: Unexpected keyword FULL at [242:7]

Many thanks for your help

Upvotes: 0

Views: 2222

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL

Try below - note no changes for all CTEs, so below just final select statement 

SELECT 
  dimension1,
  dimension2,
  dimension3,
  dimension4,
  dimension5,
  dimension6,
  dimension7,

  q2.qadimension1,
  q3.qadimension2,
  qadimension3,
  qadimension4,

  QA_check1,
  QA_check2

FROM Query_1
LEFT JOIN Query_2 q2 USING(dimension1)
LEFT JOIN Query_3 q3 USING(dimension1)

Upvotes: 2

Related Questions