updated question — I have a table that contains the following columns: I need to calculate the balance based on the update date. In some cases there can be the same update_date for two different records. When I have this, I want to grab the lower value of the balance. This is the query I have so far: In…
Tag: snowflake-schema
Pythonic way to optimize SQL VIEW count to extract information schema metadata from Snowflake
I have 12 VIEW tables in Snowflake and I would like to extract TABLE_NAME,CREATED,LAST_ALTERED from Snowflakes INFORMATION Schema for View tables, and also want to get row count for each 12 VIEW tables, along with metadata for Base table mentioned in below code.I was wondering if there is way get row count us…
How to convert 1900-01-01 to null in snowflake
My SQL query output looks like this. ID UPC 1 2021-05-01 2 1900-01-01 UPC is a date column. How do I convert 1900-01-01 to null? I tried following instructions from this, but I am getting SQL compilation error: error line 1 at position 22 invalid identifier ‘DATE’ Answer Using NULLIF: Returns NULL…
regex to find “null” values from a column
I have a column JSON_DATA. The values within this column have an object type. It contains a json. This query works fine: This is how the data might look like: Some of the values within this field are “null”. I want to search all occurrences of quoted “nulls” and replace/regex_replace t…
concatenate date + time to make timestamp
I am using dbt and snowflake to parse a json.. Currently, I parse two cols, date and time separately. Now, I want to concatenate both and assign the type timestampto them I tried this:: However, the col “REQUIRED_TIMESTAMP” is just always empty in my final table. What else can I try? Answer I assu…
How to calculate difference in dates for column with smallest value
I have a table that contains 3 column dates: CREATED, CLOSED and EXPIRED. I need to calculate the difference in date between either CLOSED or EXPIRED and CREATED. I need to select the SMALLER value between CLOSED and EXPIRED and then calculate the difference to CREATED. If they have the same date, I need to s…
How to grab certain value found in a string column?
I have a column that contains several different values. This is not in JSON format. It is a string that is separated into different sections. I need to grab everything that is found under ID only. In the examples below, I only want to grab the word: “syntax” and “village” The above doe…
Join on Id in Variant (Array) Snowflake
I have two tables t1 and t2 created as follows: Create Tables Goal I am looking to join t2 to t1 if the id of the record exists in the t1 variant array. What I’ve Tried I came across the ARRAY_CONTAINS function which looked perfect. But with the following I am receiving no results: How Do I Get This? An…
Parametrizing values to a snowflake query from another table
I’m trying to parametrize an update query reading values from a snowflake table as below. Snowflake query : srcTable : The values of col_name in above table – franchise, acct_type & pref_lang are actual columns of the tgtTable which Im trying to update based on old_val and new_val of srcTable …
Not able to resolve “Numeric Value ‘-‘ is not recognized” error in Snowflake SQL Query
I’m working on converting the sql query below over to Snowflake, and got the error “Numeric Value ‘-‘ is not recognized” but it did not have a line number. I assume it’s in reference to the various “-1” instances throughout the query but there’s no line re…