Skip to content
Advertisement

Tag: oracle11g

oracle sql max value

i have a requirement, i need to find max only one val from each department,i need only one value even 2 person have same max value drop table tst; create table tst(val number,dept varchar2(20),name …

not in in select query

I have similar query in my project. I have been told to find other way to remove not equal to, because index is not used if you do so. I have no other filter condition, can I do some work around to fool optimizer? Answer I have been told to find other way to remove not equal to, is generally

Why to use Correlated Subqueries?

As far as I’ve seen, correlated subqueries can be re-written using multiple-column subqueries or joins. And they usually perform better than correlated subqueries. So in which possible scenarios a correlated subquery can be a better option or the only option? (I use Oracle Database) Answer Oracle has a good optimizer, but correlated subqueries are sometimes the most efficient way to

How to calculate total time from date column in Oracle

I have a table where i have some log like this. and output is like below But I want to have one more column which will give me total time like below I have tried to minus both time but not getting proper result. The output i am getting is below Answer Don’t convert the dates to strings. That makes

Oracle: Id Not in another table

I have following two tables, airport and flying I would like to get airport_id where flying_id present in flying table but not present in airport table. I rote the following query using nested select query. Is there anyway I can improve without writing nested? Answer There are two ways. LEFT JOIN NOT EXISTS

List of Record type matching issue Oracle

I want to check if a workspace is not available in all_workspace table then create new workspace. My requirement is if p_workspace is not matching the list of value of v_workspace then it should create new one. It gives me error after creation. If I run that workspace individually then its working. but when I am passing through block with

How to search for a month that is input by the user

I am working on some homework and have been stuck on this for a week. I have tried using TO_CHAR, MONTH(search), and EXTRACT(MONTH from…) and they all end up with either identifier ‘JAN'(the month I am searching for) is not declared, or expression is of the wrong type. This assignment is to display all the rows for pledges made in

Advertisement