I need to generate a single excel file which each department details in seperate sheets of the excel file, each sheet should ne named with department no (ex :Dept_10, Dept_20,Dept_30) and the main excel should be named as Emp_details.
Header row and total row should be in bold.I have created 3 individual excels thru toad and moved them to single excel and renamed the sheets.
Can we programatically do this thru SQL or PL/SQL, does Oracle provide any build packages for excel.As excel files cannot be uploaded,I am unable to upload the excel file I generated manually.
select to_char(empno) empno,ename,job,mgr,hiredate, nvl(sal,0) sal,nvl(comm,0) comm,nvl(sal,0) + nvl(comm,0) "Total Income" ,deptno from emp where deptno = 10 union all select 'Total',null,null,null,null,sum(nvl(sal,0)),sum(nvl(comm,0)),sum(nvl(sal,0))+sum(nvl(comm,0)),null from emp where deptno = 10;
I need the effective dates (start and end) of marital status changes in sequential order, without duplicate rows over the same time frame. (Per_all_people_f table only). For example below, I only need the items that are in bold. I am very new to pl/sql and cannot figure out how to do this.
When I do this in sql with Min date and max date; the 1st and 2nd blocks are correct, the 3rd block has wrong end date and 4th block is entirely missing as the 'M' is already counted for in block 1 even though it occurred after other status changes.
Example of the rows and what I need in BOLD below: So no gaps in time and it captures the effective date range for that particular marital status; I need to get: 1st block 'S' 10/23/2000 - 4/12/2004 2nd block 'M' 4/13/2004 - 10/1/2006 3rd block 'D' 10/2/2006 - 5/23/2007 4th block 'M' 5/24/2007 - 12/31/4712
Actual data in table I do get on a query with no restrictions:
490 *10/23/2000* *4/12/2004* 0 US S F 490 *4/13/2004* *10/1/2006* 0 US M F 490 *10/2/2006* 2/12/2007 0 US D F 490 2/13/2007 *5/23/2007* 0 US D F 490 *5/24/2007* 10/7/2010 0 US M F 490 10/8/2010 11/15/2012 0 US M F 490 11/16/2012 *12/31/4712* 0 US M F
I have a query where I'm trying to get one row for a student that is their academic program. A view PS_NTSR_PRGPLN_VW returns all active programs. This student has three.The query is pretty straightforward but I'm not getting any rows, and I think it is because I'n not using MAX(pe.effdt) correctly.
select * from PS_NTSR_PRGPLN_VW h where h.emplid = 'xxxxxx' AND h.institution = 'ABCD' AND h.acad_plan_type = 'MAJ' AND h.primary_indicator = 'Y'
[code]....
I've had rows where I have the same plan_sequence so I do need to use MAX(pe.effdt), but when I do I get zero results.
would like to get the rate for my report table base. The search for the rate first will be base on product_class_id, year and place_id, second if the report row match the product_class_id and year but not place_id it should get the default rate (in my rate table the place_id is null).
Last if the report row doesn't match any key the result should be zero.This is my result query, how can I do this query? report_id product_class_id place_id fy rate 1 1 1 2012 15 2 1 2 2011 6 3 1 3 2011 7 4 2 2 2012 18 5 2 5 2011 2 6 3 1 2012 0 7 4 1 2012 0
I try to do a function by don't know how to handle the default place_id (null)
Quote: A.2.2 Writing Backup Scripts for Disk and Tape Scenarios
As in the disk-only scenarios, the backup scripts in this section are categorized based on database workload. as stated very clearly it depends on the workload, more precisely the rate of block change. The size of the database can be found out based on formula from
[URL]....
so how would I know the rate of block change in order to know which script is suitable for me? I try to find out the rate of block change for the database based on change tracking file but based on
[URL].....
Quote:
The size of the change tracking file is proportional to the size of the database and the number of enabled threads of redo. The size is not related to the frequency of updates to the database. So how do I determine the rate of change? can the rate of block change based on size of archive logs?
I have the following information with me:
starting from 5/10/2011 0101 ending 5/18/2011 1114
average size of each file 27,942,770,176/1644 =16996818.841849148418491484184915
average size of each day's log = 27,942,770,176/9.5 =2941344229.0526315789473684210526 about 3G
If I have a database size of 92G, based on the archive log size of about 3G per day, can I conclude that a change of 3G/92G is considered as few block change?
I have employee interface table something like this.
emp_idemp_name Job_title supervisor_name 1AJ Engineer BJ 2CK Analyst ND 3BJ Manager TR 5TR VP IT JD 6ND S Manager MD 7MD VP Telecom SK 8SK VP Eng JR
I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy.
I am a Java person but since my app uses the Oracle DB I am to do this task.
I am trying to track an employee's department changes by adding a Department change counter which is set to 1 whenever the department changes otherwise it is set to a 0. How can I accomplish this update of the counter? Below is the table creation sql and sample data for insert.
CREATE TABLE EMPL_DEPT (EMPLID VARCHAR2(11) NOT NULL, EFFDT DATE, EFFSEQ SMALLINT NOT NULL, EMPL_RCD SMALLINT NOT NULL, DEPT_ID VARCHAR2(12) NOT NULL, [code]..........
I WANT to RETRIEVE all the INFORMATION of DEPT ALONG with TWO EMPLOYEES of each DEPARTMENT.is any OTHER WAY to DO THIS due to performance in Oracle 10g
I used Scott schema.I take 2 blocks ,one block having Deptno,Job and also 1st block is a non database block and Another Block Empno,ename,job,sal,deptno, is a database block,my question is when i enter Deptno,Job then Display Employees Details and also Department name
I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary) from employee e join department d on e.deptno=d.deptno where d.deptname=upper('finance') group by d.deptno, d.deptname; [code]...
Using the HR schema, I'm trying to write a query which will display the names and salaries of those employees who earn the highest salaries in their respective departments.
I wrote the query:
SELECT first_name, last_name, salary FROM employees WHERE salary = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);
However, this query does not execute correctly but I'm not sure why?
I would like to create a hierarchical tree with Department number as a node and all the employees (only employee names) under that department as shown below:
[-]Department - 10 KING JAMES ----- [-]Department - 20 MARY | | and so on...
I have created a hierarchical tree 'HT_DEPTNO' under block 'BL_EMP'. I also created a Record Group 'RG_HTREE' with query as shown below:
SELECT 1, LEVEL, E.ENAME, D.DEPTNO||' - '||D.DNAME DEPARTMENT, D.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO START WITH E.DEPTNO = 10 CONNECT BY PRIOR E.EMPNO=E.MGR
Department EmpName ____________________________________ Sales empname1 Sales empname2 Marketing empname3 Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total ______________________ Sales 2 Marketing 1 Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM ( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee FROM Employer A, EmployeeInfo B WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2') GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
I have an Oracle 10g database, on the App Serv I have an image file that has 20,000 .jpg files that has an id number as each image name.I have successfully queryed the image file and posted one image to my web page matching the image id number. sample:
the &1 is the matching id number that is input from the user.My task now is to select multiple images using a department field in the spriden table to pull the needed id numbers.I have not been successful in the proper format to pass the id number to the <img src field.
In oracle query can i want find out how many day wise count for a year days (for example how may sundays, mondays, tuesdays, wednesdays ,thursdays,fridays,saturdays) in a given year (we can give the start day of the year and the end day of a year).
example ---------- jan sun-5 mon-4 tue-5 wed-5 thu-5 fri-4 sat-5 feb ------------do--------------------------------- like this for all 12 months at a single query.
select in1.empid,in1.atttime,out.atttime from (select empid ,atttime from attend where status=1 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) in1, (select empid,atttime from attend where status =0 and to_char(atttime,'mmyyyy')='022012' order by empid,atttime desc) out where in1.empid=out.empid and in1.empid='02256' order by in1.atttime,out.atttime
But this query do .one value relation with all column.means first february in time with all out time.
If a particular department has 10 employees it should have data upto emp_name_10,if department has only 5 employess it should data upto emp_name_5 and so on.I came up with below approach, in this approach I need to create new table to store the data in row wise.
In my actual requirement 4 tables needs to be joined and 2 of the tables are very large.Is there any other approach without creating a new table, something within pl/sql.
drop table emp_dept; create table emp_dept(deptno number,dept_name varchar2(100),emp_name varchar2(100),hire_date date,seq_cnt number,total_cnt number); insert into emp_dept