I am trying to display the results from 2 queries, one is supposed to display the count of the employees, per department, who win over the average of the entire company and the other one is supposed to display the count of the employees, per department, who win under the average of the entire company.
I used a UNION ALL, but all it does is merge the results from the ones that win over and under the average into one row, is there a way to separate them? I tried assigning names to each salary using AS but it only displays the first I put in.
sql
Original
- sql Code
(
SELECT DE.DEPARTMENT_NAME, COUNT (EM.EMPLOYEE_ID) AS MAYORES
FROM DEPARTMENTS DE, EMPLOYEES EM
WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID
AND EM.SALARY > (SELECT AVG(EM.SALARY) FROM EMPLOYEES EM)
GROUP BY DE.DEPARTMENT_NAME
[code].....
- returning from the procedure 2 ref cursors containing result set of 2 queries - returning from the procedure 1 ref cursor containing result set of that 2 queries as one (with UNION ALL)?
How I can get the union result of 2 queries and put them in xml result, but I want each query to be in seperate xml element. I don t want to put 1 single xmlelement and do a From then construct a virtual table uniting the 2 subqueries
I mean I don t want something like the following:
(Select XMLAGG( XMLELEMENT("credit",
[Code]...
Except the 2nd alternative is not working I get error message: "SQL command not properly ended"
When we are running a query it is giving us the result based on the conditions .But to know what exactly is happening in the database when we are running a query against it and how finally it returns the result.
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
Following are 2 queries, which return same results as far as the input parameter is NOT NULL
select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id); select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id);
However the execution plan differs a lot The FTS cost or rows accessed count also varies what could be the reason? The PX_CJQ is simply select * from dba_objects and has index on object_id which anyway is not being used in this case
variable x number exec :x:= 28 Query - 1 *************** *************** SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where decode(:x,null,1,object_id) = NVL(:x,object_id); SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
[code]...
Query - 2 *************** *************** SQL> >select /*+ gather_plan_statistics */ * from PX_CJQ where object_id = NVL(:x,object_id); SQL> >select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT
I need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.
declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record ( name varchar2(20), sal number(6) [code].....
I have the below PL/SQL Block in which it iterates through the id stored in local table and passes this value to the select statement which runs on a remote DB.
In the loop the select statement is executed for each id in local table.Now i am trying to display the output of the execute statement which does not work.
let me know how can i show the output of this and store in a file or display on screen.
server output on DECLARE v_sql VARCHAR2(9000) := NULL; BEGIN
QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;
NAME DOJ QUAL EMPID ---------- --------------- ---------- ----- sam 21-mar-2012 mca sony 03-jan-2000 mba 10610 maya 21-mar-2012 m.arch sonna 18-mar-2012 mis
[code]...
i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?
am facing a Problem in creating display items .First I create a display item and name it ITEM6This Display item will show Department name of employees .I create a Block Level Trigger to invoke this display item .
WHEN-VALIDATE-ITEM select dname into :ITEM6 FROM DEPT where :EMP.DEPTNO=DEPT.DEPTNO;
In Property Palette I edit following Data Type : char canvas :canvas4
Then I save ,compile and Run it successfully .But when i press Execute Query button . It shows me Following error message .
SELECT ROWID,ITEM6,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM scott.emp
our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
I have a query which has 5 unions, each clause of the union takes 1 hr to run and query results come back in 5 hrs, Is there any way I can make these clause to run in parallel?
Below is my query Issue faced is that based on union I want to limit the result and does not want second query to give duplicate result as both queries deal with same table
SELECT TAB1.ID, TAB1.CRNCY_CODE, TAB2.SCHEME_CODE, DECODE( 'INFENG',CAST( '' AS VARCHAR(20)), NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC), TAB1.SUB_HEAD_CODE, DECODE( 'INFENG' ,CAST( '' AS VARCHAR(20)),NVL(TAB1.ALT1_SUB_HEAD_DESC , TAB1.SUB_HEAD_DESC),TAB1.SUB_HEAD_DESC)
[code]...
select null ID,null crncy_code,TAB2.SCHEME_CODE , NVL(TAB2.ALT1_SCHEME_DESC,TAB2.SCHEME_DESC),TAB2.SCHEME_DESC,null SUB_HEAD_CODE from TAB2 where TAB2.SCHEME_TYPE ='SCHEME1';
I'm just trying to see if there is another way of doing this query without using a UNION. The only way I can see is using a UNION but maybe I'm missing something or a way to do it without a UNION.
Result:
Select any customer within the user_states table who lives in "MO" Select any customer within the user_cars table who lives in "MO" and has a "White" car Select any customer within the user_plates table who lives in "MO" and has a plate of "A" or "B" Join to the user_names table to display the customers name
So, the result would be any customer who lives in "MO" who owns a "White" car and any customer who lives in "MO" who has a plate of "A" or "B".
Cant find any artice on what is better to use unpivot or union all. I can achive same results using either but cant decide what would be better. For example a table has columns:
Name 1, Phone1, Name 2, Phone 2. The result I want would be:
I've got function with combined sql statement (union all), how do I split it into two sql requests? This is to avoid time increasing of sql executing. IMHO good solution is to write two functions, how do you think?
I am new to SQL Oracle programming and have a question on a union query. I am trying to get results (example) for the following:
Org # Full_date Run_date 5 2/20/2012 2/20/2012 5 2/21/2012 2/21/2012 5 2/22/2012 null (there is not a record for this) 6 2/20/2012 2/20/2012 6 2/21/2012 null 6 2/22/2012 2/22/2012 7 2/20/2012 2/20/2012 7 2/21/2012 2/21/2012 7 2/22/2012 2/22/2012
The dw_time table would have the listing of all dates, (Full_date) and the dw_capacity_daily table would have the run_date. Here is my
select * from ( select distinct a.Organization_Nbr, d.full_date from CMBHS_DW.DW_ORGANIZATION a, cmbhs_DW.DW_Organization_Identifier b, cmbhs_DW.DW_Contract c, cmbhs_dw.dw_time d where a.ORGANIZATION_NBR = b.ORGANIZATION_NBR [code]....
I have one scenario in which i want to write the sql,but not able to write correct qry,
tables 1-emp 2-emp_hist 3-dept 4-dept_hist.
i want to retrieve data from the emp and hist able based on some conditions,but if data is not present in emp and dept tables.then fetch data from emp_hist and dept_hist.I have written below qry which is working fine,but my prob is i want to provide my emp_id only one place. else i have to change my java code.
select * from emp,dept where emp.emp_id=dept.dept_id and emp_id=5 union select * from emp_hist,dept_hist where emp_hist.emp_id=dept_hist.dept_id and emp_id=5
I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
SQL> set heading off; 1 select '#ENCODING WINDOWS-1252' from dual 2 union 3 select ' ' from dual 4 union 5 select 'Language Section EN-US' from dual 6 union 7 select ' ' from dual 8 union 9* select 'Catalog Section Title Date Source' from dual SQL> /
#ENCODING WINDOWS-1252 Catalog Section Title Date Source Language Section EN-US
- - - - - - - - - - - - - - - - -
Desired Output: #ENCODING WINDOWS-1252 Language Section EN-US Catalog Section Title Date Source
