I have a column where most values are numeric but a few contain letters or special characters: Records 90000 5200 unknown approximately 25 areas TBC 5000 (approx) I would like to remove any non-numeric entries and replace them with null like this: Records 90000 5200 NULL NULL NULL NULL I tried to remove non-numerical characters first, then change the data
Tag: snowflake-cloud-data-platform
Call stored procedure in Snowflake query
I have a stored procedure, called SP_CALC_BUCKET, defined. I can call it like so: and this works, and returns the result I expect. However, if I try to call this stored procedure for each row returned from a SELECT statement: I get the following error: Unknown user-defined function SP_CALC_BUCKET Clearly, the Syntax is informing Snowflake that it should look for
Snowflake cross join + lateral flatten
I have a semistructured column that I would like to left lateral join after a cross join. In the above, I would expect the original cross join to expand the result count from 1 to 3; and the left lateral join should not reduce the number of rows returned. The result I obtain is unexpected: COL A SEQ KEY PATH
dates from the DATEDIFF()
I am using the query in Snowflake: It gives me no. of days as: days start_date end_date 14 2022-09-03 2022-09-17 28 2022-08-19 2022-09-16 but I need to find the dates for the days instead of just the no. of days i.e I want to see those all 14 dates instead of just no. of days. can anyone help. Answer so
Can we use parameters in case statement in snowflake sql stored procedure
I am working on a stored procedure in snowflake using sql language which expects three parameters. I need to execute different sql queries based on the parameter value of the third parameter. For eg, if the 3rd parameter is A, then execute sql_query1. If 3rd parameter is B, then execute sql_query2 and so on. Is there a way to do
Right join to an incremental date table not working in SQL
I have two tables. The first is a date_incremental table just holing dates from 1900-01-01 to current in yyyy-mm-dd format. The second table has user behavior. Here are the tables in visual form: date_incremental The user table Now, I want to get a table output where no date gaps exist between the users purchases, i.e. display the history subsequently. If
error: its says legacy_id is an invalid identifier
if I try * it fetches all the data but when I mention any of the col names it says it’s invalid. Answer Most likely it was quoted during table creation and should be accessed as such: In Python: Double-quoted Identifiers If an object is created using a double-quoted identifier, when referenced in a query or any other SQL statement,
How to reference variables in Snowflake SQL script
I’m writing a script to create 5 tables, but cannot seem to get the SQL DDL part of the statement to work. I get a syntax error ” unexpected ‘table’ “, and I’ve tried the SQL statement with execute immediate as well. What am I doing wrong? Answer To quote the documentation: If you are using the variable as the
SQL Query to use a Case Statement within and Aggregate Function
I have a query below that I need to get a count where Incident Records = 0. The first level is a summary of the records it grabs to calculate totals. I tried using COUNT(INCIDENTS!=’0′) but it apparently is not working though it does not error out. How would I get this count? This is in Snowflake if that is
String length mismatch not giving data in another column
I’ve a column location in my table. I’ve used it as a common column to join two tables 1.Factory 2.Inventory Like Factory.location = inventory.location But I’m having a problem.. For example Location in factory table just has 09 where inventory table has 009 doesn’t match but for three digit numbers it’s matching eg: 115 = 115, 999=999. But in the