Skip to content

Execution plan too expensive case when exists

I have the below query, but when I execute it runs forever. My execution plan shows a huge cost which I cannot locate. How could I optimize this query? I have tried some hints but none of them have worked 🙁 Kindly let me know if any additional information needed. Answer Use window functions: Not only is the q…

Summing field in other rows conditionally

I have table in the form like below: Pilot Leg Duration Takeoff John 1 60 9:00:00 John 2 60 9:00:00 John 3 30 9:00:00 Paul 1 60 12:00:00 Paul 2 30 12:00:00 Paul 3 30 12:00:00 Paul 4 60 12:00:00 And I am trying to figure out is a query to get the following: Pilot Leg Duration Takeoff LegStart John

ORA-01722: invalid number – Everything seems a digit

I have the below query: However, when I execute it, the below error appears: But, when trying to find what was the entry in col2 that was causing issues, I get no results: Aby hints on how to keep searching for the col2 culprit will be hugely appreciated. Answer This expression is not going to find mixed digi…

How to achieve conditional array aggregate?

For simplicity I’ll use the following patient and appointment tables. What I want is one report that contains the following data. So what I need to do is: Get the patient names from patient table aggregate all appointment statuses of the patient aggregate all appointment times of appointments that have …

JPA Subquery fails

Does anyone know why it fails? It does not give syntax problems but it gives me the error “missing expression (ORA-00936)” The problem seems to be in :user IN uc.users Answer I think the last bit is incorrect. This should work: So, the whole query becomes:

Finding date-streak in SQL

I got my sqlite table with entries having a DateTime field. I want to check if there are entries with n consecutive days. Like a streak. Now I could just query all entries ordered by the date-field and iterate over them and check by myself. I am just wondering if there is some more efficient way to do this. L…