I am trying to fix an array in a dataset. Currently, I have a data set that has a reference number to multiple different uuids. What I would like to do is flatten this out in Snowflake to make it so the reference number has separate row for each uuid. For example Should end up looking like: I just started
Tag: sql
Amend Report to use financial years and quarters
I need to amend this existing code to group by financial year (starts 1 July) and quarters, I realize that it is the below part of the query that requires amending but I can’t get it to work The full query is Sample data Lyaout as per below but with finacial yaer ^ qtrs shown instead of months Answer Be…
How to Count Times an ID Appears in a Column in a Different Table
Apologies in advance: I’m sure this is relatively easy and has been asked ad nauseam, but I just can’t quite come up with the proper search. Basically, I’m trying to take a list of IDs queryed from …
Where clause with multiple conditions in SQL DB2
Desired Output: Query: Any help is greatly appreciated Answer Try this: dbfiddle example
How to do an exact match on multiple columns and show with the latest date?
I want to only show one occurrences of an exact match of TempString1, TempString2, and TempString3 with the latest date. How can I do an exact match of three fields and only display the row that has the latest date? Table OUTPUT: Answer You can try this using ROW_NUMBER() function. The ROW_NUMBER() is a windo…
Standard SQL – How to count frequency of values in array
I get the following Table with the query underneath: For each of the values in the trafic_medium column (e.g.: cpc, referral, organic, etc.) I am trying to figure out how often each value occurred in the array, so preferably add a new column ‘count’ that shows ho often that value occurred? I’…
Why is my query failing with SQLCODE = -420?
Can I get some help with this? I’ll copy the code as well as the error. I looked up the error code (DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=BOOLEAN) but I’m not understanding what doesn’t meet the requirements of the function? I’m just trying to pull these tables, rename …
SQL: Trying to select records 7 days before date
I have a table with a date field called oppo_installdate. This is a date in the future, and I basically want to select records where this date is 7 or fewer days from the current date. I have tried to do this with the query below but it is older returning dates from 2019 as well, and I’m not sure
T-SQL Equivalent of regular expression ‘b’
I’m in the process of converting a CLR function that utilizes regex to a SQL function. I know that SQL Server does not fully support regex, but i just need one case to work which is a word search. Search Field Value: {“Id”:1234, “Title”: “The quick brown”} Regex Patte…
TypeORM – How to remove last added row?
I’m trying to remove only the last added row. Instead, of the last one, it removes all rows. I was thinking about adding where corresponding to the newest date but I feel it’s not a good approach to solve this problem. Answer Use a subquery to query the last insert row first, then delete this row …