Getting error in if else statement ( big query) ----Syntax error: Expected ")" but got keyword THEN at [9:5]

Question

I'm scripting this query on Big Query. This is the query where I get the syntax error . I'm not understanding what is wrong with this? Ideally both the queries will run. One query will run when true and the other will be 0, union both will have result of true.

SELECT *
FROM (
  SELECT *
FROM 
    IF ((SELECT 'current_date = First_day_current_month' AS test_0
        FROM `IUMPR_Migration_POC.Diesel-sixth-container`
        WHERE test_0) IS TRUE
    )
    THEN
         (WITH Diesel_current_month_m AS (
                SELECT * 
                EXCEPT (Field_1)
                FROM `IUMPR_Migration_POC.Diesel_current_month_m`
            ),
            first_cond_macro AS (
                SELECT *
                FROM `IUMPR_Migration_POC.diese_current_qvx`
                UNION ALL
                SELECT *
                FROM Diesel_current_month_m
            ),
            date_first_con AS (
                SELECT *,
                    FORMAT_DATE('%m', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Month_current,
                    FORMAT_DATE('%Y', CURRENT_DATE()) AS Year_file,
                    FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Current_month_Name,
                    SUBSTR(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), 1, 3) AS Month_SF,
                    CONCAT(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), '-', SUBSTR(FORMAT_DATE('%Y', CURRENT_DATE()), 3, 4)) AS Month_Year
                FROM first_cond_macro
            ),
            date_second_con AS (
                SELECT *,
                    FORMAT_DATE('%m', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Month_current,
                    FORMAT_DATE('%Y', CURRENT_DATE()) AS Year_file,
                    FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Current_month_Name,
                    SUBSTR(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), 1, 3) AS Month_SF,
                    CONCAT(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), '-', SUBSTR(FORMAT_DATE('%Y', CURRENT_DATE()), 3, 4)) AS Month_Year
                FROM `IUMPR_Migration_POC.consolidated_diesel_qvx`
            ),
            Flag_data_exist AS (
                SELECT *, 'Exist' AS Flag_data
                FROM date_first_con
                INNER JOIN date_second_con
                ON date_first_con.VIN = date_second_con.VIN
            ) ,
            Flag_data_new AS (
                SELECT *, 'New' AS Flag_data
                FROM date_first_con
                LEFT JOIN date_second_con
                ON date_first_con.VIN = date_second_con.VIN
                WHERE date_second_con.VIN IS NULL
            ),
            Flag_data_old AS (
                SELECT *,'Old' AS Flag_data
                FROM date_first_con
                RIGHT JOIN date_second_con
                ON date_first_con.VIN = date_second_con.VIN
                WHERE date_first_con.VIN IS NULL
            ),
            macro_first_container_result AS (
                SELECT * FROM (SELECT * FROM Flag_data_exist
                UNION ALL
                SELECT * FROM Flag_data_new
                UNION ALL
                SELECT * FROM Flag_data_old
                )
            )
        )
    ELSE
        (WITH Diesel_current_month_m2 AS(
  SELECT *,
  FORMAT_DATE('%Y-%m-%d', DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)) AS Date_Time_next,
  FORMAT_DATE('%Y-%m-%d', CURRENT_DATE()) AS Date_time_today
  FROM `IUMPR_Migration_POC.Diesel_current _month_m`
),
filter_date AS (
  SELECT * FROM Diesel_current_month_m2
  WHERE Date_time_today != Date_Time_next
), 
Diesel_cur_mnth AS (
SELECT * ,Null as dumy_col1,Null as Date_Time_next,Null as Date_time_today
  FROM `IUMPR_Migration_POC.diese_current_qvx`
 UNION ALL
SELECT * FROM filter_date
),
date_first_con1 AS (
  SELECT *,
    FORMAT_DATE('%m', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Month_current,
    FORMAT_DATE('%Y', CURRENT_DATE()) AS Year_file,
    FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Current_month_Name,
    SUBSTR(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), 1, 3) AS Month_SF,
   CONCAT(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), '-', SUBSTR(FORMAT_DATE('%Y', CURRENT_DATE()), 3, 4)) AS Month_Year
   FROM Diesel_cur_mnth
),
date_second_con2 AS (
SELECT *,
    FORMAT_DATE('%m', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Month_current,
    FORMAT_DATE('%Y', CURRENT_DATE()) AS Year_file,
    FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS Current_month_Name,
    SUBSTR(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), 1, 3) AS Month_SF,
   CONCAT(FORMAT_DATE('%B', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)), '-', SUBSTR(FORMAT_DATE('%Y', CURRENT_DATE()), 3, 4)) AS Month_Year
    FROM `IUMPR_Migration_POC.consolidated_diesel_qvx`
),
Flag_data_exist AS (
  SELECT *, 'Exist' AS Flag_data
  FROM date_first_con1
  INNER JOIN date_second_con2 
  ON date_first_con1.VIN = date_second_con2 .VIN
),
Flag_data_new AS (
  SELECT *, 'New' AS Flag_data
  FROM date_first_con1
  LEFT JOIN date_second_con2 
  ON date_first_con1.VIN = date_second_con2 .VIN
  WHERE date_second_con2.VIN IS NULL
),
Flag_data_old AS (
  SELECT *, 'Old' AS Flag_data
  FROM date_first_con1
  RIGHT JOIN date_second_con2 
  ON date_first_con1.VIN = date_second_con2 .VIN
  WHERE date_first_con1.VIN IS NULL
),
macro_sec_container_result AS (
  SELECT * FROM (SELECT * FROM Flag_data_exist
  UNION ALL
SELECT * FROM Flag_data_new
 UNION ALL
SELECT * FROM Flag_data_old
)
),
macro_result AS (
  SELECT * FROM macro_first_container_result
UNION ALL
SELECT * FROM macro_sec_container_result
)
    )
END IF
 );

I've tried if else and case when statements also, but getting syntax error always.

`select * from IF (` this simply isn't permissible syntax you cannot "from" an "if" so the problems start there. For `WITH Diesel_current_month_m AS` you cannot open a common table expression (CTE) inside a case expression either. A `case expression` simply provides (evaluates to) a single value - it is NOT a way to hold multiple rows (which is what a CTE can do). — Paul Maxwell, Jul 12 '23 at 07:14
"sql" is not a database, please specify which database you actually use because the SQL syntax required varies between databases. — Paul Maxwell, Jul 12 '23 at 07:19

score 1 · Answer 1 · answered Jul 12 '23 at 07:40

I assume due to the presence of backticks in the code that you use MySQL or a derivative of it. If this is incorrect please add a tag to your question with the correct dbms.

If you need some form of "flow control" in MySQL you need more than just a select query, e.g.

DELIMITER //

CREATE PROCEDURE your_procedure (
    IN condition_1 INT,
    IN condition_2 INT
)
BEGIN
    IF condition_1 = 1 THEN
        -- SQL statements for condition 1
    ELSEIF condition_2 = 1 THEN
        -- SQL statements for condition 2
    END IF;
END //

DELIMITER ;

So you cannot open you query with:

SELECT *
FROM (
  SELECT *
FROM 
    IF (

In almost all SQL databases a case expression is NOT a "flow control" construct at all (exception, Oracle does allow case/when in its procedural SQL language extension). So you cannot do this either:

CASE(WHEN X = Y THEN (with abc as (select...

This is invalid:

            SELECT * 
            EXCEPT (Field_1)
            FROM `IUMPR_Migration_POC.Diesel_current_month_m`

Please read the answers Exclude a column using SELECT * [except columnA] FROM tableA?

In addition do NOT rely on "select *" in your queries - which you seem to be doing often - especially in several unions. This is a recipe for bugs to creep in because if anyone alters any table your code could fall apart. Specify the columns you need in each select clause.

Getting error in if else statement ( big query) ----Syntax error: Expected ")" but got keyword THEN at [9:5]

1 Answers1