I need to split a string like “abc” into individual records, like “a”, “b”, “c”. This should be easy in Snowflake: SPLIT(str, delimiter) But if the delimiter is null, or an empty string I get the full str, and not characters as I expected. Answer Update: SQL UDF…
Tag: sql
Using sql to recursively generate values depending on keys and column values
I have a table like this: store item value store1 item1 2 store1 item2 3 I want to use it to create the following table: store item value store1 item1 0 store1 item1 1 store1 item1 2 store1 …
Performance difference with Where condition in subquery/cte
Is there a performance difference for applying the where condition to a subquery data source compared to applying it at the joined statement? Is there a difference between these in performance? Let’s say I have two hive tables A and B which are both partitioned on the field date. Is that query’s p…
Finding banks with higher assets in previous quarter SQL?
For each bank I need to find all dates with higher asset compared to its previous dates (quarter). (For instance a bank (id: 123) has asset 10,000 in 3/31/02 and asset 20,000 in 6/30/02. Then bank id (123), asset value (20,000) and date (6/30/02) should be recorded.) Report the first 10 observation of output …
SQL query for descending order by sum of values
I have an Apex Oracle app to do. This is the app description: A touristic destination is defined by its name and description. Each touristic destination has a number of bookings, defined by start date, end date, description, price as in the following example: Touristic destination: Hotel Sunrise, Hawaii Start…
SUM over datedifference?
Lets say I have the following records with column datetime dtTime quantity 2020-12-10 19:21:52.293 1 2020-12-10 19:21:52.323 2 2020-12-10 19:21:53.293 1 2020-12-10 19:21:58.293 1 2020-12-10 19:…
Count Male and Female who scored greater than average marks
This question was asked in a practice Test, and I am new to SQL. Question was to determine the average marks scored by the male and female students. Also, you are required to determine the count of …
Error while writing to array plsql how to fix? Extend doesn’t work also
so I am trying to write to an array in PL/SQL, and I always get the subscript outside of limit error. I’ve seen similar posts and implemented everything based on those answers, I can’t seem to find what I’m doing wrong. The line giving the error is “arr_quartosLivres(counter) := q.id;&…
Get previous months first and last date
I need to query last month data from a database. So I need the first and last date of previous month to be able to make the “between” SQL query. First date starts always from 1 of course, …
Select rows by condition without groupby in SQL?
I have SQL table like below. Type Code 1 11111 2 11111 1 22222 2 22222 1 33333 2 33333 And would like to get below result. Type_1 Code_1 Type_2 Code_2 1 11111 2 11111 1 22222 2 22222 1 …