SQL if statement to select items form different tables-CodePudding

I am creating a new table joining 3 different tables. The problem is that I have some data that I want to select for other_info divided into two different tables. table_1 has preference over table_2, but it is possible that in table_1 are missing values. So, I want to select the value of box if it's not empty from table_1 and select it from table_2 if the value in table_1 does not exist.

This is the code I have very simplified, but I think it's enough to see what I want to do. I've written an IF ... ELSE statement inside a with, and this is the error I get:

Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword IF at [26:5]

Besides, I've tried different things inside the conditional of the if, but none of them is what I expect. Here is the code:

CREATE OR REPLACE TABLE `new_table`
PARTITION BY
  Current_date
AS (
  WITH info AS (
    SELECT
      Date AS Day,
      Box,
    FROM
      `table_1`
  ),
  other_info AS (
    IF (...)
        BEGIN{
        SELECT
          Date AS Day,
          Box
        FROM
          `table_1`}
        END
    ELSE
        BEGIN{
        SELECT
          Date AS Day,
          Box
        FROM
          `table_2`}
        END
  )
  SELECT
    Date
    Box
    Box_description
  FROM
    `table_3`
    LEFT JOIN info(Day)
    LEFT JOIN other_info(Day)
)

CodePudding user response：

You're not going to be able to embed an IF within a CTE or a Create-Table-As.

An alternative structure can be to union two queries with mutually exclusive WHERE clauses... (Such that only one of the two queries ever returns anything.)

For example, if the code below, something is checked for being NULL or NOT NULL, and so only one of the two can ever return data.

WITH
  info AS
(
  SELECT
    Date AS Day,
    Box,
  FROM
    `table_1`
),
  other_info AS
(
  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
  THIS BIT
  --------------------------------------------------------------------------------
  SELECT
    Date AS Day,
    Box
  FROM
    `table_1`
  WHERE
    (SELECT MAX(x) FROM y) IS NULL

  UNION ALL

  SELECT
    Date AS Day,
    Box
  FROM
    `table_2`
  WHERE
    (SELECT MAX(x) FROM y) IS NOT NULL
  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
)
SELECT
  Date
  Box
  Box_description
FROM
  `table_3`
  LEFT JOIN info(Day)
  LEFT JOIN other_info(Day)

CodePudding user response：

In stead of the if..., you could do something like this (in MySQL):

SELECT *
FROM table1

UNION ALL

SELECT *
FROM table2 WHERE `date` NOT IN (SELECT `date` FROM table1)

I am not sure (as in: I did not test), but I do think this is also possible in google-bigquery

see: DBFIDDLE