How To Display Result From 2 Queries - UNION ALL

Oct 21, 2012

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].....

View 3 Replies


ADVERTISEMENT

UNION ALL Vs 2 Queries

Nov 12, 2012

is there any difference between

- 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)?

Will 2nd option be faster or similar to 1st?

View 1 Replies View Related

SQL & PL/SQL :: Combine 2 Queries Into 1 Without Using UNION

Jun 5, 2012

SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........

View 5 Replies View Related

SQL & PL/SQL :: XML - Union Of 2 Select Queries

Jan 15, 2013

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"

View 4 Replies View Related

SQL & PL/SQL :: Join Two Queries Together To Get Result

Dec 12, 2011

How can i join two quires together to get result.

My requirement is:

First i want to select Table as we do

Select * from tab;

Then i want to describe the table as we do

Desc WO;

How can we join these two queries to have result.

View 21 Replies View Related

PL/SQL :: How SQL Queries Act In Back End To Give The Result

Jul 22, 2013

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.

View 1 Replies View Related

Reports & Discoverer :: SQL Union All - Query Block Has Incorrect Number Of Result Columns

Feb 1, 2012

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

(Select distinct c.contact_code

Following is the SQL query

Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......

View 1 Replies View Related

SQL & PL/SQL :: Queries Returning Same Result With Different Access Path To Table?

Feb 7, 2012

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

[code]...

29 rows selected.
SQL> >

View 8 Replies View Related

PL/SQL :: Combining Result Of Three Queries To Form A Single Table

May 12, 2013

I have three select queries. Each of them returns a single column. I want the result of these queries into a single table..

I tried this way..

select * from
(first select),(second select),(third select);

this gives duplicate rows...

View 4 Replies View Related

SQL & PL/SQL :: Using Varchar Parameter In Queries With Number Column Can Result In Performance Degrade

Jul 4, 2013

I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.

Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;

Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.

View 7 Replies View Related

SQL & PL/SQL :: How To Display Result

Aug 7, 2012

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].....

View 1 Replies View Related

SQL & PL/SQL :: How To Display Result Horizontally

Feb 11, 2013

1.Requirement....

deptno ename
++++++++++++++++++++++++++++++
10 a,d,e,g
20 b,c,j
30 k,l,o

2.How to swap the column values ? ex. col2 into col1 and col1 into col2

3.What is the major Diff. b/w Right outer join & Left outer join ?

View 3 Replies View Related

SQL & PL/SQL :: Display Result Of Sql For Loop

May 7, 2013

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

[Code]....

View 3 Replies View Related

Display Result Column 10610 In Second Row?

Mar 28, 2012

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 ?

View 4 Replies View Related

Forms :: Display Item Not Showing Result?

Oct 8, 2010

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

ORA-00904: "ITEM6": invalid identifier

View 2 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

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

View 6 Replies View Related

SQL & PL/SQL :: Is It Better To Use UNION (or) UNION ALL With DISTINCT

Apr 22, 2010

Is it better to use UNION (or) UNION ALL with DISTINCT,Which one will improve performance.

Query1:

SELECT deptno FROM emp
UNION
SELECT deptno FROM dept

Query2:

SELECT DISTINCT * FROM(SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept)

I mean to say query1 is better (or) query2 is better,which query improves the performance.

View 1 Replies View Related

SQL & PL/SQL :: Union Run In Parallel

Sep 30, 2010

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?

View 13 Replies View Related

SQL & PL/SQL :: Union Condition?

Aug 23, 2012

I am facing an issue in union clause

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';

[CODE]

View 4 Replies View Related

SQL & PL/SQL :: Query Without Using A UNION?

Aug 17, 2011

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".

SQL

select b.customer_id,
b.first_name,
b.last_name

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Using 2 Union Operator

Jun 28, 2010

I have an query i.e.

I want 3 lines input in 1 line using 2 union operator like

Input:-
'i love playing
football and
volleyball'

i want the output like:-

"i love playing football and volleyball"

solve query using 2 union operator?

View 9 Replies View Related

SQL & PL/SQL :: Union Of Two Tables?

Jul 1, 2010

how can i get a union of two pl/sql tables. ??

View 2 Replies View Related

PL/SQL :: Unpivot Or Union

Dec 11, 2012

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:

Name 1, Phone 1
Name 2, Phone 2.

View 5 Replies View Related

PL/SQL :: Order By With Union?

Aug 24, 2012

I have the following sql

select htf.escape_sc(su.sukey) a, htf.escape_sc(su.sukey) b
from udm_su su, udm_lde lde
where su.ldeid = lde.ldeid
and su.sukey in (select su_generic
 
[code]...

View 3 Replies View Related

Alternative To Union Of Two Different Table?

Dec 21, 2010

Is there any alternative to Union or Union all of two different table. Example would be great.

View 5 Replies View Related

SQL & PL/SQL :: Two Requests Instead Of Union All In Function?

May 24, 2011

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?

FUNCTION f_LintOVRDUAH
(mydate date,
cid number )

[Code]....

View 18 Replies View Related

SQL & PL/SQL :: Union Query For Oracle

Feb 29, 2012

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]....

View 7 Replies View Related

SQL & PL/SQL :: Query Performance In Union

Aug 10, 2010

which one is better?

unloading 5 tables of same structure using a ETL tool then merging the data

using Union operator to unload 5 tables then do transformations in ETL tool

View 4 Replies View Related

SQL & PL/SQL :: Query For Eliminating Union

Apr 24, 2011

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

View 20 Replies View Related

SQL & PL/SQL :: UNION While Selecting From Dual?

Sep 19, 2013

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

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved