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]...
I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY ACCOUNTING 8750 RESEARCH 10875 SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.
I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:
----- EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace'); CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S); --Created SEQUENCE andTRIGGER FOR rdf_network_trace id [code]....
when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.
I'm trying to create a function that simply returns the current database name (e.g: select db_unique_name FROM v$database ) from a function but when I compile it comes up with :
Error(9,5): PL/SQL: SQL Statement ignored Error(9,44): PL/SQL: ORA-00942: table or view does not exist
I am compiling and running this as the SYSTEM user and I do think that I need to set privledges/roles, etc to allow this (since I have read that using synonyms in functions/procedures requires permissions...but I cannot seem to find anything that tells me exactly what role/priveledge I need to grant/allow to let this happen.
Firstly, I have created 3 mv log on 3 table(target,target extension,brand). Secondly, I created the first mv and its log. Lastly, I created the second mv from the first mv. This time I used the pivot function, but it cannot work now.
--1 create mv log
drop MATERIALIZED VIEW LOG ON target; drop MATERIALIZED VIEW LOG ON targetextension; drop MATERIALIZED VIEW LOG ON brand; CREATE MATERIALIZED VIEW LOG ON target with rowid, sequence(id); CREATE MATERIALIZED VIEW LOG ON targetextension with rowid, sequence(targetid,brandid,EmailPermission,NumberOfAllOrders); CREATE MATERIALIZED VIEW LOG ON brand with rowid, sequence(brandid, brandname);
--2 create the first mv and it's log
drop MATERIALIZED VIEW mv_target1; CREATE MATERIALIZED VIEW mv_target1 REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/1440 AS select t1.rowid t1_rowid, t2.rowid t2_rowid, t3.rowid t3_rowid, t1.id targetid, t3.brandname, [code].....
--3 create the second mv drop MATERIALIZED VIEW mv_target2; CREATE MATERIALIZED VIEW mv_target2 REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/1440 AS select * from mv_target1 pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in ('XXX' XXX,'YYY' YYY ,'ZZZ' ZZZ));
Now, Here is a problem, it throws "ORA-12015: cannot create a fast refresh materialized view from a complex query". Then I used dbms_mview.explain_mview to see the reason, and it tell me the following
REFRESH_FAST_AFTER_INSERT "inline view or subquery in FROM list not supported for this type MV"
declare lv_sqltext varchar2(4000); begin execute immediate 'truncate table mv_capabilities_table'; lv_sqltext := 'select * from mv_target1 pivot(max(EmailPermission) EmailPermission, max(NumberOfAllOrders) NumberOfAllOrders for brandName in (''XXX'' XXX,''YYY'' YYY ,''ZZZ'' ZZZ))'; dbms_mview.explain_mview(lv_sqltext,'nested=>TRUE'); commit; end; /
A.Create object to get the salary hike of an employee on the basis of completion of years in an organization.
Conditions:- Create two tables with name as "Employee_yourname" and "Employee_Hike_Yourname". "Employee_yourname" can have columns like EMPID, NAME, SALARY, DATE_OF_JOIN. "Employee_Hike_Yourname" will have 3 more columns for new salary, Current salary and completed period in organization. Consider the employees who have completed minimum one year in organization. Salary hike for employee with tenure >= 1yr and < 2yrs should be 10% of current salary. If tenure is greater than or equal to two years and less than 3 years then salary hike should be 20% of current salary. For employees having tenure more than 3 years hike should be 30% % of current salary
I have a created data block using employees table so am trying to validate item salary using max_salary and min_salary from table jobs how to do this kind of validation.
[How can i validate input number into a Field of type char in oracle form?]
The above result give me minimum salary but total number of JOB holders, You can see only one SALEMAN getting 1000 but count show total number of SALESMAN. Similarly 3 MANAGERS are getting minimum and same salary but count show total number of MANAGERS.
My question is how can i get number of person on min salary?
I need to obtain the top 2 departments in terms of total salary.
I have the following tables (Dno corresponds to DeptNo). I've found tutorials for obtaining the salaries of the top three employees, and summing the salaries for each dept. is easy, but I can't seem to combine the two.
DNo Fname Salary 2 Tom 10000 3 Mike 20000 2 Harry 30000
DeptNo DeptName 1 Administration 2 Special 3 Finance
I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.
How do i check in Table B whether it is converted correctly into words taking input or reference from table A
Consider below example:-
Table A Table B $125 Dollar One Hundred twenty Five only $45,542 Dollar Forty Five Thousand Five Forty Two Only $145.56 Dollar One Forty Five and fifty six cents Only $145,253 $35,256.65 $560,250.67
/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */ CREATE PROCEDURE p_get_name ( p_empno IN OUT NUMBER, p_name OUT VARCHAR2, p_err OUT NUMBER [code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
