CODEWITHSUNDEEP
google-bigquery
bcascone
bcascone

Reputation: 139

Using a case statement as an if statement

I am attempting to create an IF statement in BigQuery. I have built a concept that will work but it does not select the data from a table, I can only get it to display 1 or 0

Example: 

SELECT --AS STRUCT 
 CASE
 WHEN (
       Select Count(1) FROM (  --  If the records are the same, then return = 0, if the records are not the same then > 1
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Prior_Filtered` 
                                Except Distinct
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest_Filtered` 
                           )
      )= 0 
  THEN 
(Select * from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest`)  -- This Does not 
work  Scalar subquery cannot have more than one column unless using SELECT AS 
STRUCT to build STRUCT values at [16:4] END




 SELECT --AS STRUCT 
CASE
 WHEN (
       Select Count(1) FROM (  --  If the records are the same, then return = 0, if the records are not the same then > 1
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Prior_Filtered` 
                                Except Distinct
                              Select Distinct ESCO, SOURCE, LDCTEXT, STATUS,DDR_DATE, TempF, HeatingDegreeDays, DecaTherms
                              from `gas-ddr.gas_ddr_outbound.LexingtonDDRsOutbound_onchange_Latest_Filtered` 
                           )
      )= 0 
  THEN 1 --- This does work

Else 0 END

How can I Get this query to return results from an existing table?

Upvotes: 0

Views: 633

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

You question is still a little generic, so my answer same as well - and just mimic your use case at extend I can reverse engineer it from your comments

So, in below code - project.dataset.yourtable mimics your table ; whereas project.dataset.yourtable_Prior_Filtered and project.dataset.yourtable_Latest_Filtered mimic your respective views 

#standardSQL
WITH `project.dataset.yourtable` AS (
  SELECT 'aaa' cols, 'prior' filter UNION ALL  
  SELECT 'bbb' cols, 'latest' filter 
), `project.dataset.yourtable_Prior_Filtered` AS (
  SELECT cols FROM `project.dataset.yourtable` WHERE filter = 'prior'
), `project.dataset.yourtable_Latest_Filtered` AS (
  SELECT cols FROM `project.dataset.yourtable` WHERE filter = 'latest'
), check AS (
  SELECT COUNT(1) > 0 changed FROM (
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Latest_Filtered`
    EXCEPT DISTINCT
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Prior_Filtered`
  )
)
SELECT t.* FROM `project.dataset.yourtable` t
CROSS JOIN check WHERE check.changed

the result is 

Row cols    filter   
1   aaa     prior    
2   bbb     latest

if you changed your table to 

WITH `project.dataset.yourtable` AS (
  SELECT 'aaa' cols, 'prior' filter UNION ALL  
  SELECT 'aaa' cols, 'latest' filter 
) ......

the result will be 

Row cols    filter   
Query returned zero records.

I hope this gives you right direction

Added more explanations:

I can be wrong - but per your question - it looks like you have one table project.dataset.yourtable and two views project.dataset.yourtable_Prior_Filtered and project.dataset.yourtable_Latest_Filtered which present state of your table prior and after some event

So, first three CTE in the answer above just mimic those table and views which you described in your question. They are here so you can see concept and can play with it without any extra work before adjusting this to your real use-case.

For your real use-case you should omit them and use your real table and views names and whatever columns the have.
So the query for you to play with is: 

#standardSQL
WITH check AS (
  SELECT COUNT(1) > 0 changed FROM (
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Latest_Filtered`
    EXCEPT DISTINCT
    SELECT DISTINCT cols FROM `project.dataset.yourtable_Prior_Filtered`
  )
)
SELECT t.* FROM `project.dataset.yourtable` t
CROSS JOIN check WHERE check.changed

It should be a very simple IF statement in any language.
Unfortunately NO! it cannot be done with just simple IF and if you see it fit you can submit a feature request to BigQuery team for whatever you think makes sense

Upvotes: 2

Related Questions