I have an interesting issue where I need to create a unique identifier based on match groups for a set of data. This is is based on multiple criteria, but generally what I need to happen is to take this input: SOURCE_ID MATCH_ID PHONE 1 1 (999)9999999 1 2 (999)9999999 2 1 (999)9999999 213710 707187 (001)2548987 213710 759263 (100)8348243 213705
Tag: snowflake-cloud-data-platform
Snowflake SQL – Format Phone Number to 9 digits
I have a column with phone numbers in varchar, currently looks something like this. Because there is no consistent format, I don’t think substring works. (956) 444-3399 964-293-4321 (929)293-1234 (919)2991234 How do I remove all brackets, spaces and dashes and have the query return just the digits, in Snowflake? The desired output: 9564443399 9642934321 9292931234 9192991234 Answer You can use
Variable in dateadd function snowflake
I have a query like this: It gives this error: Single row subquery returns more than one row. Is there a way to pass a variable in the 3rd argument of dateadd function? Because my cte will return many min_date based on the group by clause. TIA Answer Yes, sub-select in SELECT need to only return one row, you have
Javascript print SQL first item in ResultSet without comma, then all others with comma
Current output: Desired output: How can I do this in javascript within the sql stored procedure? I would like to not have to execute the query more than once because the actual query is computationally intensive. Answer you can add boolean check if this first or not:
Latest value of compared date range? (SQL/Snowflake)
I have values in Table-A like: And values in Table-B like: And I want to join the two tables such that I see the most recent cumulative sum of values in Table-B as-of the Date in Table-A for a given Patient. How would I join these two tables by date to accomplish this? Answer First step seems like a basic
Unable to find error in the query – Snowflake
Error: SQL compilation error: error line 105 at position 8 invalid identifier ‘INTENT’. There are two CTE’s, and then the select statement. First CTE works fine if run separately, the error is in the second CTE. I am not sure what is causing the syntax error. Thanks in advance. Answer Snowflake treats unquoted identifiers as if they were uppercase. While
Create table in snowflake – Syntax
I am trying to create a table and insert rows into it. But it keeps giving me this syntax error. ERROR : SQL compilation error: syntax error line 3 at position 1 unexpected ‘EventName’. syntax Any help would be appreciated. Answer You need to type those columns, and you are missing a comma after the int of EnventTypeID and your
How to add all text in a column into one cell in Snowflake
So I have data as follows: All I would like to do is make a new table which gathers all text into one cell as follows: So all values of tags are in one cell for each ID. Order does not matter, but they must be separated by commas. Answer how about this :
Invalid group by expression error when using any_value with max and window function in Snowflake
I was given a query and I am attempting to modify it in order to get the most recent version of each COMP_ID. The original query: I then attempted to use a window function to grab only the highest version for each comp_id. This is the modified query: When attempting to compile the below error is given: SQL compilation error:
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