I have this table Movie with columns defined as (CustomerId, MovieId, Name) and I want to fetch these columns by concatenating them into a string in the position they are defined in Snowflake, by ordering through ORDINAL_POSITION. So I found that using LISTAGG() get’s me pretty close to what I want. By …
Tag: snowflake-cloud-data-platform
Got invalid expression error , while inserting JSON data into VARIANT column
Trying to insert multiple rows into a snowflake table Have tried replacing ‘$$’ with single quotes Getting error: SQL compilation error: Invalid expression [PARSE_JSON(‘{“def”:1}’)] in VALUES clause What am I doing wrong? Answer You cannot use PARSE_JSON in the values secti…
How to recursively calculate rows based on previous rows in Snowflake?
I’m trying to build a performance score on each player in my table and use this to run some analysis. I’ve tried to use the Elo ranking using the player’s score in the game vs themselves in the prior game. The game is a time-trial race so the faster they complete the better their performance…
Snowflake Regular Expression
I have this string in Snowflake column: I need to get names in this format regardless of the number of company names: “SpecTra, Signal Capital Partners”. In other words, I need to extract company names and concatenate them. I have tried this : and regexp_substr() function, but did not get the desi…
Summing cumulative views in an sql table by date Snowflake
I have a sql table with date, reference number and views as a column, I am trying to add up the views for each row based on the date grouped by the reference number. I have data for 3 days, lets say the 27, 28, and 29 of March, I have multiple reference numbers but lets take 1 reference number:
Trying to create procedure in snowflake and getting error while passing value to another procedure
Below procedure to get the value from the metadata table and pass its value into another procedure. Give the error message: Execution error in store procedure GET_RESULTS: SQL compilation error: error line 1 at position 16 invalid indetifier ‘STBL1’ At Statement.execute, line 18 position 26 Answer…
How can I remove characters in a string after a specific special character in snowflake sql?
I have a url column in my database. I want to remove all characters after ? . select regexp_replace(page_url,’?(.*)’, ”) as clean_url from demo But I’m getting the following error: Invalid regular expression: ‘?(.*)’, no argument for repetition operator: ? Answer Using LEFT…
Create hierarchical summary of XML nodes
Is there a way of using SQL or Python (or another language) to return the node-tree for an XML document? In the XML document example below, the employee node appears twice but the second time it has more nodes within it… same with the address node. Is there a way to return the structure (without values …
How to return the count of a table created and dropped within a SQL stored procedure in Snowflake?
I am trying to use a variable to store the count in a temporary table created within a stored procedure in Snowflake so that I can include the value in the return statement. When I try to do a select count(*) from the table I get SQL compilation error: Object ‘CDP_SMS.DOMAIN_CANONICAL.TEMP_DELTA_MANUFAC…
Converting day datetime to timestamp in Snowflake
I have a datetime data in a table in Snowflake and I want to convert it into a timestamp The output I want is: The methods I tried: etc.. None of the above conversions worked Answer using the correct formatting tokens, your valid datetime strings can be parsed. Depending if you what to have or not have timezo…