Google Big Query: Need help converting Postgres query to Google big query

0
votes

I am trying to get this postgres query:

COALESCE(
NULLIF(drug_exposure_start_date + (INTERVAL '1 day' * days_supply), drug_exposure_start_date)

Into big query. Here is what I think should work:

SELECT 
COALESCE (
NULLIF(date_add(drug_exposure_start_date, INTERVAL (1 day * days_supply)), drug_exposure_start_date))
from `x`

This is the error message that I get:

Error: Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [3:53].

Any help would be greatly appreciated, thank you.

2
sqlgoogle-bigquery
I am so sorry, I thought I already marked your answer as correct. I did it now. Thank you for the help you provided me. I really appreciate it. – user9807750

2 Answers

0
votes

I think you just need a small modification so that the expression uses INTERVAL <days> DAY:

SELECT
  COALESCE(
    NULLIF(date_add(drug_exposure_start_date, INTERVAL days_supply DAY),
           drug_exposure_start_date))
from x

You can read more about the syntax for DATE_ADD and other functions in the documentation.

0
votes
#standardSQL
SELECT COALESCE(NULLIF(DATE_ADD(drug_exposure_start_date, INTERVAL days_supply DAY), drug_exposure_start_date)) 
FROM x