CODEWITHSUNDEEP
google-bigquery
bcascone
bcascone

Reputation: 139

Case statement based on zero records returned.

I need to write a case statement that will work on zero records being returned. something like: 

 SElect 
  Case 
      when ( 
               Select *
               from Latest_LI
                    Except distinct
               SELECT *
               FROM PRIOR_LI
             )-- This case will return zero records
       = null 
       Then 
          1
        WHEN 
          ( 
               Select *
               from Latest_LI
                    Intersect distinct
               SELECT *
               FROM PRIOR_LI
             )-- This case will return zero records
        > 0
        Then
          2
        END

In SQL I would assign a variable and set it to a 0 or 1 and write an IF statement that would make the correct selection.

My concern is that the case statement is only for selecting rows from the table, I want to use it for a full select based on the outcome.

Any help is appreciated.

Upvotes: 1

Views: 406

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Your question is a little generic - so as my answer :o)

Below example is for BigQuery Standard SQL 

#standardSQL
SELECT CASE 
  WHEN ( 
    SELECT COUNT(1) FROM (
      SELECT * FROM `project.dataset.Latest_LI`
      EXCEPT DISTINCT
      SELECT * FROM `project.dataset.PRIOR_LI`
    )
  ) = 0 THEN 1
  WHEN ( 
    SELECT COUNT(1) FROM (
      SELECT * FROM `project.dataset.Latest_LI`
      INTERSECT DISTINCT
      SELECT * FROM `project.dataset.PRIOR_LI`
    )
  ) > 0 THEN 2
END

or slightly refactored version 

#standardSQL
SELECT 
  CASE 
    (SELECT COUNT(1) FROM (
      SELECT * FROM `project.dataset.Latest_LI`
      INTERSECT DISTINCT
      SELECT * FROM `project.dataset.PRIOR_LI`
    ))
  WHEN 0 THEN 1
  ELSE 2
END

Hope you got an idea

Upvotes: 4

Related Questions