Advertisements I am trying to create a function in Snowflake that returns to me the rows matching a condition, but it only works when there is only one row matching the condition. I am tried to create a function in Snowflake such as: CREATE OR REPLACE FUNCTION test (field_value STRING) RETURNS STRING AS $$ SELECT… Read More Snowflake function unable to return multiple rows
Advertisements I have the following piece of SQL code: select date, session_id, article_id, category from table The outcome is now something like this: date session_id article_id category 01-05 124 xyz animals 01-05 124 xyz "" 01-05 124 xyz null 01-05 456 qwert sports 01-05 456 qwert "" 01-05 456 qwert sports I want to have… Read More Pick best filled value of multiple occurences of value combination in SQL
Advertisements I can generate a table with the following structure – in actuality, this is more than 50,000 rows long – but below example is a simplified representation: Row Account Number Date 1 1001 2011-01-10 2 1001 2011-02-01 3 1001 2011-02-20 4 1001 2011-02-22 5 2001 2011-04-11 6 2001 2012-01-01 7 2001 2012-01-30 8 2001… Read More Select rows based on desired date difference from first row by group – SQL
Advertisements I’ve recently been experimenting with Snowflake’s JavaScript stored procedures and encountered an unexpected behavior. I created a simple stored procedure as outlined in the Snowflake documentation here. Here’s the stored procedure I created: create procedure broken() returns varchar not null language javascript as $$ var result = ""; try { snowflake.execute( {sqlText: "Invalid Command!;"}… Read More Snowflake JavaScript Stored Procedure Returns Success Despite Expected Failure
Advertisements I have two tables: one with the order time of a specific procedure for a group of patients, with some patients having the same type of order (with a different order ID) more than once per stay:PATIENT_ORDERS: INDEX_ID VISIT_ID PATIENT_ID ORDER_ID ORDER_TIME 1 10 1 100 6/1/22 8:19AM 2 10 1 101 6/5/22 4:30PM… Read More SQL select most recent timestamp before order
Advertisements I got stuck – I have a json file with a few nested json objects and arrays and I can’t figure out how to query this data properly. { "dim":[ "ENT1", "ENT2", "ENT3", "ENT4" ], "col":[ [ "ST1", "ST2", "ST3" ] ], "row":[ { "head":[ "AC1" ], "data":[ "1", "2", "3" ] }, {… Read More Snowflake/SQL – Nested json objects and arrays
Advertisements COUNTRY STORE_ID ORDER_DATE DE 990003975 2023-01-24 FR 990004963 2023-04-11 FR 990005204 2023-06-15 FR 990005204 2023-06-10 FR 990005204 2023-06-07 JP 990005210 2023-01-08 I have data presented like the table above. I need a way to add a column that shows me the difference in days between the dates of the same STORE_ID, ordering them in… Read More SQL-Snowflake – How to count the days between dates in 2 or more rows wtih WHERE condition
Advertisements I am trying to return only values from a field that have the first letter between A-Z, I have tried using the below but this only does one letter at a time. UPPER(LEFT(AD_ADDR,1)) like ‘A%’ The other characters will be 0-10, which I want to exclude but are formatted as strings, so using IS_INTEGER… Read More SQL only return values where first letter is between A-Z
Advertisements I am trying to pass Company_name as parameter, it being called twice in the SQL Query. First it is being replaced by value but for second instance value is getting passed. UPPER(COMPANY_NAME)=UPPER(”Company_name”) . CREATE OR REPLACE PROCEDURE DEVELOPMENT.PUBLIC.PDL_SKILLS_DATA_SP(Company_name VARCHAR) returns varchar LANGUAGE SQL as $$ DECLARE query STRING; date_str STRING; time_str STRING; BEGIN SELECT… Read More How to pass parameter in snowflake stored procedure
Advertisements i have tried with sequence in snowflake but i need my first values need to start with 0000001 and end with increment as of RecordID 0000001 0000002 0000003 0000004 0000005 0000006 0000007 0000008 0000009 0000010 0049443 –like this …but i am not able to achieve by sequence in snowflake? drop TABLE foo CREATE OR… Read More snowflake sql i need to generate column with 0000001 and end with as increment by 0000001