I have a PL-SQL table with a structure as shown in the example below: I have customers (customer_number) with insurance cover start and stop dates (cover_start_date and cover_stop_date). I also have dates of accidents for those customers (accident_date). These customers may have more than one row in the table…
Tag: sql
How to calculate the percentage of a nested table architecture in mysql
My current tables architecture is the following: Card Table ( Parent table: Card) Each card has one or many lists (Second child lists) Each list has many tasks (third child tasks) Whenever the user fill a task it will be filled in a separate table called (user_tasks) So if you want to imagine it it’s li…
Postgres get all elements where value matches from jsonb array
I think I am trying something simple, but after hours I just can’t get it to work. I have a table which contains a tags column, which is a jsonb array and looks like this: I now want to write a query which returns the full object to me, when the name matches the search string. So far I came
finding if a line starts with a specified string in a clob and then extract
I have a CLOB column that I want to search for a line that starts with ‘305’ then extract something from that line, some of my rows will have multiple lines that start with ‘305’ or ‘305 somewhere in the entire cell, so I’d only want to find the first line where it starts w…
Query slow in Web App, but fast in command line
I am need to retrieve records from two tables which are linked by a third. Session content doesn’t contain any information I need besides the keys to link the other two tables. I am currently using these joins: It only takes 50 ms in SQLite DB browser, but takes multiple seconds of buffering when I call…
Using Over Partition in sql compared with group by
Given the table creation code bellow, is there an alternative method(s) to display the same result to using group by and count(*)? And what’s the difference in this case? Answer This query puts the total for each colour on each row: Before window functions, a typical solution would be a correlated subqu…
Oracle error ORA-02270: no matching unique or primary key for this column-list
I got an error while creating this table. Here are the course and slide tables: What should I do ? Answer The foreign key should be in one definition, like this:
Merge lines over timespan in SCD2 table
I’m having the following table sourced from a SCD2 table. From this source table, I have selected only a few columns, which reults in several lines looking exactly similar. I want to remove the unnecessary lines, those that contain the same data, and have the ValidFrom column showing the first value and…
What is the most appropriate logic to add rows in table
I have following table structure (simplified to make essential things clear), which lists Top 3 bank customers in each category of loan and branch of the bank. SNO column is rank of the customer, value of which is up to 3. Loan Category SNO Branch Customer Name Amount Home Loan 1 abc Piyush 10000 Home Loan 2 …
Oracle SQL revenue YTD computation
I want to write an oracle SQL query to compute monthly YTD revenue (cumulative sum) for all possible combinations of the given dimensions. There are also some months where there are no transactions and hence no revenue, in this case the previous month YTD revenue must be displayed for that dimension combinati…