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 …
Tag: oracle11g
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
Update column values with result from group values (MIN) of linked table
I have two tables Table 1 (main) and Table 2,connected with T1. I need to delete double entries in Table 1. TO do that before any delete in T1 I must delete or update the data in T2. The option chosen is to update T2 data to point with entries not to be deleted in T1. SQL Problem : I
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 find which rows are being overlap while finding overlap b/w the dates
How can we find the between the overlap lap b/w the dates . overlap means when start date and end date are within same range for below example row 1 has no over lap. Row 2to 5 can be considered as one set of over lap as there start date and end are over lap with themselves Row 6 &
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
How to copy all records and add some changes in the same table in OracleDB
I would like to copy all records into the same table with some changes. Is someone tell me a good way? I tried to this. but Not Ended error happen. How can I fixt it? ORA-00933:”SQL command not properly ended” 00933. 00000 – “SQL command not properly ended” *Cause: *Action: Answer