SQL & PL/SQL :: To Update The Names Of Employees By Concatenating With A For DEPT 20
Aug 21, 2012
I want to update the names of employees by concatenating with A for DEPT 20. For that I have written the following PL/SQL block. But instead of one A the ename are concatenating with five AAAAA s.
DECLARE
TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE;
l_emp_arr lt_emp_arr;
BEGIN
SELECT deptno
BULK COLLECT INTO l_emp_arr
[code].......
I want to update the names of employees by concatnating with A for DEPT 20. For that I have writteh the following PL/SQL block. But instead of one A the ename are concatinating with five AAAAA s.
DECLARE TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE; l_emp_arr lt_emp_arr; BEGIN SELECT deptno
I want TO find out FIRST two employee joined IN A particular department WITH department information.THE relation IS basically FROM THE scott SCHEMA.I tried LIKE AS follows. IS there ANY other way FOR best PERFORMANCE.
SELECT deptno,dname,loc, Max(Decode(rn, 1, hiredate))hiredate1, Max(Decode(rn, 1, ename)) employee1, Max(Decode(rn, 2, hiredate))hiredate2, Max(Decode(rn, 2, ename)) employee2 FROM (SELECT d.deptno,dname,loc,hiredate,ename,Row_number() over(PARTITION BY e.deptno ORDER BY hiredate) rn FROM dept d, emp e WHERE d.deptno = e.deptno(+)) GROUP BY deptno,dname,loc;
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 am trying to update of job_id column of employees table for employee number 205 two times one after another. First time job_id column of employees table for employee number 205 is updated with new job_id. But second time job_id column of employees for employee number 205 table can not be updated. Oracle returns the following errors
HR:orcl > update employees set JOB_ID='AC_MGR' where employee_id=205; update employees set JOB_ID='AC_MGR' where employee_id=205 * ERROR at line 1: ORA-00001: unique constraint (HR.JHIST_EMP_ID_ST_DATE_PK) violated ORA-06512: at "HR.ADD_JOB_HISTORY", line 10 ORA-06512: at "HR.UPDATE_JOB_HISTORY", line 2 ORA-04088: error during execution of trigger 'HR.UPDATE_JOB_HISTORY'
As there is composite primary key using employee_id and start_date column.how to update same employee job_id twice.AS we can see from job_history table, the record for employee 200 is as follow
So how can i do the same for employee 205 without changing hire_date after first job_id update. Since for every update of job_id fires trigger. To insert row in job_history table employee id and start_date must be unique each time. HERE hire_date of employees table is used as start_date of job_id table. how it was possible for employee id 200 to change job_id twice?
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
What is the structure and the values of the default EMP and DEPT table in oracle 9i....i accidentally dropped those 2 tables .... and i don't remember the structure of it.
table was defined as below & indexes are also created on name /dept columns, data is also available :
create table test ( name varchar2(10), version NUMBER(12), dept varchar2(10) [code].....
Now the requirement is that the parition keys has to be changed to 'dept' from the existing 'version' . How to accomplish this without any implication on the indexes and other constraints.
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
The pipe separator needs to appear only when values found in addr1 or addr2 or addr3.
WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod dod Road,' addr2,'' addr3 FROM dual UNION ALL SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL SELECT '' addr1,'' addr2,'' addr3 FROM dual) select addr1||'|'||addr2||'|'||addr3 address from address;
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.
select Account_Number, Date_Time, concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified from table where Account_Number = xxxx
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty, a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':' || b.mnfst_qty FROM vw_acao_critical a JOIN vw_acao_critical b ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr [code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
EMPLOYEES __________________________________________ EMPID NAME MANAGERID ------------------------------------------ 34 Amy 17 Ben 34 5 Chris 34 10 Don 5 ...
How can we use SQL to get all employees under one manager , either direct or indirect
I only can think the following SQL to get the first direct employee.
select e1.empid from employees e1, employees e2 where e1.managerid = e2.empid and e2.empid = '34';
Perhaps we could put it in PL/SQL procedure to do recursive call?
I borrowed some code fragments from other posts and put together a sql query. I think there is a better method in other posts but I couldn't get them working.
I'm trying to get the count of hired employee(s) using the EMP table. I want from the first hire to the last and ALL in between. If there were no hires in that window, I want 0.
WITH minmax AS (SELECT MIN(last_day(hiredate)) fmonth, MAX(last_day(hiredate)) lmonth FROM emp), cal AS (SELECT add_months(fmonth, LEVEL - 1) mnth FROM minmax CONNECT BY LEVEL <= months_between(lmonth, fmonth) + 1), vals AS (SELECT extract(YEAR FROM mnth) YEAR, extract(MONTH FROM mnth) MONTH FROM cal), data AS (SELECT extract(YEAR FROM hiredate) YEAR, extract(MONTH FROM hiredate) MONTH, COUNT(*) hired_cnt [code]...
Assign employees to their jobs in consideration the maximum number of employees to each jobs is 5 employee plus each job has own the maximum number of employees
we need the maximum number of employees for each job 5 to be variable when need to change this maximum for certain job , change this number from database (form the from of job ) not form code )
tables emp emp_no name manager hiredate salary
job job_no job
you can add tables or attributes to tables to complete you business.
