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 the
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 using below code for 12 VIEW tables or
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 if expr1 is equal to expr2, otherwise returns
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 them with proper NULL values. Desired output result: I tried
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 assume JSON_DATA:”Required_Collect_Time_From”:time has a typo in here,
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 select EXPIRED. In the example
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 does not work since this is not a
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? Any
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 in the above Snowflake query. But I’m running into SQL
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 referenced so it’s unclear what’s causing the problem. If it’s the various references to