If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.
Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.
I need a query which will fetch only 3 rows for a day. For eg if today is 18-jan-2012, then i have to show rows whose deadline date is 18-jan-2011, 19-jan-2011 and 20-jan-2011. And on 18-Jan-2011, after 10.50 a.m it should not consider deadline date 18-jan-2011, instead it show deadline date is 21-jan-2011.
This deadlinedate will not have all dates, only exchange working dates, so at one time we should show only the probable 3 deadline dates..
I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:
(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER ) OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))
If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.
There are over 1500 records, but this query does not return any row. If i change rownum >= 100 to rownum <= 100 it returns first hundred records though... What is wrong here?
A B 1000 B C 2000 A D 3000 C A 3000 B A 4000 A B 5000 A B 1000 B C 2000 C A 3000 B A 4000 A B 5000 A C 10000
Dynamically I am giving user name A. I want to retrieve the data how much money user B taken from user A and how much money user B given to A lly for C and D.
CONDITION: I want to retrieve the records of 2 way transaction that means user A sent money to user B and user B must sent money to user A. I don't want one way transaction records i.e user A sent money to D but user A didn't get money from user D like this records.
OUTPUT LIKE:
USER MONEY_TAKEN_FROM_A MONEY_GIVEN_TO_A B 12000 8000 C 10000 6000
I need function to pick the record from DB random manner.For example, say we have 500 records and input value to the function is 5 means, it should display the records randomly between 1 to 5
I wrote the function witch returns some information.
function get_cust_info (v_msisdn integer) RETURN sys_refcursor IS curs sys_refcursor; BEGIN open curs for 'select first_name, last_name, street, town from the_table where MSISDN = :1' using v_msisdn; RETURN curs; end
How to call this function to write result into table? I just want to write function which returns more Varchar.
I have to write a file using the UTL_FILE that needs to look like the one below. I am getting the righ results but I am getting duplicates, If I have more than one records retrieve in the Invoice_data_cur cursor(for the same pidm) I got the right results, but I got duplicate data, in another words if I retrieve 3 records from the Invoice_ data_cur, I got the same record write to the file 3 times, but the right results. If I only retrieve 1 record in Invoice_ data_ cur , I only get the results of the in invoice_detail_charges_cur I don't get the results of the invoice_ detail_ payments_ cur:
H xxxxxxxxxxxx I 00399999 10/02/201000000000005000000000000500Jane Smith D 00366899 Current charges Fall 201010/02/2010Study Abroad Fee - Semester 0000000001200 D 00366899Current charges Fall 201010/02/2010France School Abroad 0000000011200 [code]....
I have a big database, I want to retrieve its records in several tries; first I use "select * from dbname where rownum <1000" but for 2'th try how can I get next 1000 records but not previous records?
11 rows selected. I am not able to understand what is the difference between the two queries! Why is the second query able to do what the first query cannot?
Is there any Oracle query I can run to determine the "number of days since the last backup"? SQL Server provides this data but I cant seem to find the equivalent for Oracle. Looks like there may be some information like this via RMAN tables and if so I want to create the simplest query possible to obtain that information.
I am trying to get the last 7 days of record from today date, this query runs every night and I always want the last 7 days. Example - today is 3/13/2013 so I want record from 3/7/2013 to 3/13/2013 and tomorrow it would be 3/8/2013 to 3/14/2013
I have a problem with a query. I have a table employee with data as
emp_id date day working_ind 1 01-Jan-2011 Mon Y 1 02-Jan-2011 Tue Y 1 03-Jan-2011 Wed Y 1 04-Jan-2011 Thu Y 1 05-Jan-2011 Fri Y 1 06-Jan-2011 Sat N 1 07-Jan-2011 Sun N 1 09-Jan-2011 Tue Y
Sundays/ Monday/ any public holiday the working_ind will be N. If the emp is absent on one day then there will be no record entered in the table (e.g. 8th jan there is no record). Each table has only one year data.
I need to retrieve for all employees when they worked for 30 consecutive days without being absent which does not include sat/ Sunday / holidays.
Its like: -- i need to order by emp_id and date -- get oly the data with working_ind as Y -- make sure that i get 30 consecutive days (from what ever i get above) where no days data is missing
I tried using lag and inner join but it does not seem to be working.
We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.
We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.
We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below
Number of Working days in March is 31 - 3 + 1 = 29
Allowance A1 = (300 * 29 )/31
Number of Working days in April is 5 ( this also needs to find logical I am guess ) Allowance A2 = (300 * 5 )/30
Then A1 + A2.
The A(n) would be the total allowance where provided the number of month across.
I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.
I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
I have been trying to construct a query in Oracle that allows me to do the following:
For example if I have the data below:
EmpNo DOB SickDays Alex 445 15/06/1985 7 Tom 778 22/08/1981 4 James 992 07/10/1978 5
I need to write and a query to lists the employee number and the amount of days sick that they have had and also add a column that compares the number of sick days to the average number of suck days by ALL employees.
I can calculate the average sick days etc, but It wont see to allow me to find the difference between that and the amount of sick days that each person has had. I have tried this many ways and have not been able to come up with a solution.
I need a query to retrieve a particular string from all the columns and all tables in a specified schema.
For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.
CREATE TABLE MAT (matrl varchar2(100), date_man date, weight number(10) );
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700); INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);