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.
implementing UNPIVOT in..I am using Oracle 11g Release 11.2.0.1.0
OS: windows
Table Structure
CREATE TABLE "EMPLOYEE_ROLE"
("EMPLOYEE_ID" NUMBER(10,0),
"MANAGER_1" VARCHAR2(10 BYTE),
"MANAGER_2" VARCHAR2(10 BYTE),
"MANAGER_3" VARCHAR2(10 BYTE),
"MANAGER_4" VARCHAR2(10 BYTE),
[code]....
EMPLOYEE_ID MANAGER_1 MANAGER_2 MANAGER_3 MANAGER_4 MANAGER_5 ROLE_1 ROLE_2 ROLE_3 ROLE_4 ROLE_5 1345 John Mike Ram Kumar null DBA Sql Dev PLSQL Admin null
1 rows selected
I want a output like this
EMPLOYEE_ID MANAGER ROLE1
1345 John DBA
1345 Mike SQL DEV
1345 Ram PLSQL
1345 Kumar Admin
*I have tried with UNPIVOT *
SELECT
EMPLOYEE_ID
,MANAGER
FROM
EMPLOYEE_ROLE
UNPIVOT( MANAGER
FOR col1
in (MANAGER_1,
MANAGER_2,
MANAGER_3,
MANAGER_4,
MANAGER_5
))
i am able to get manager and role in two different queries
EMPLOYEE_ID MANAGER
1345 John
1345 Mike
1345 Ram
1345 Kumar
4 rows selected
SELECT
EMPLOYEE_ID
,ROLE1
FROM
EMPLOYEE_ROLE
UNPIVOT( ROLE1
FOR col1
in (ROLE_1,
ROLE_2,
ROLE_3,
ROLE_4,
ROLE_5
))
[code]....
Is it possible to add two unpivots in a single UNPIVOT query.
I want to be able to generate unpivot for 1 row of data dynamically.what i am trying to do is based on this linkI just need to be able to pass the table name in format : SCHEMA_NAME.TABLE_NAME and the query should unpivot the data for 1 row. (Will always be 1 row, just need to transpose the column names and the values for 1 row of data)
I made the below query so far. But I am getting an error and can't figure out a way to fix it.pass any table name from your database in the define step
define TAB_NAME='SCOTT.EMPLOYEE'
WITH sel_col AS
(
SELECT DECODE (data_type,
[code]...
ERROR at line 33:
ORA-00904: : invalid identifier
Its the last line of the code. The same seems to work in the select part but not in the IN part.
P.S: I cannot do PL/SQL - as i simply dont have access.
I am not sure if we can do this as i tried many solution for pivoting/unpivot My data looks like from table when i do Select * from table_name
col1 col2 col3 col4 col5 col6 col7 col8
20130320_0938 C11 1416 98 93 30 30 32
20130320_0938 C22 26 92 73 50 50 65
20130320_0938 C33 86 95 81 63 63 76
I want the data to look like
[code]...
explain the difference in the VALUE column when DESCRIPTION = 'dept' when the query is run with and without the order by clause.
SELECT * FROM
(
SELECT * FROM emp
--ORDER BY job
)UNPIVOT (VALUE FOR DESCRIPTION IN (sal AS 'salary', deptno AS 'dept'));
am running an oracle 11gR2 Database.
I have a table of the structure..
CREATE TABLE "RP_RESOLUTION_MASTER"
( "RM_ID" NUMBER,
"SR_ID" NUMBER,
"REQUEST_STATUS" VARCHAR2(200 BYTE),
[code]....
But I get an error
ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 - "expression must have same datatype as corresponding expression" *Cause:
*Action: Error at Line: 3 Column: 51
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.
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?
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 RelatedI 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]
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]....
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?
how can i get a union of two pl/sql tables. ??
View 2 Replies View RelatedI 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]...
Is there any alternative to Union or Union all of two different table. Example would be great.
View 5 Replies View Related 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]....
SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........
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]....
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
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"
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
I am writing a query and I did get it to work but it shouldn't be this hard, I feel like I am doing something wrong, and there has to be a more elegent solution.
This query works:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
UNION ALL
[Code]...
I feel like I should be able to execute the query like this: This query doesn't work:
SELECT a.d
FROM (
SELECT S_ID a, LOOKUP_DESC d, S_CODE f
FROM SSS
JOIN LOOKUP ON S_CODE LIKE LOOKUP_CODE
[Code]...
-- not some huge query
I have a requirement in SQL that I have to number each row. Hence I thought of using ROWNUM. But the sql query I'm using uses UNION operator. Hence I used like this
select a,b,rownum as 'field1' from table1
union
select c,d,1 as 'field1' from table2
Will the above query solve my purpose?
select to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate, 'HH24:MI AM') "TIME", sum(bytes)/1024/1024/1024 "TOTAL_GB"
from dba_data_files
UNION ALL
select to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate, 'HH24:MI AM') "TIME", sum(bytes)/1024/1024/1024 "USED_GB"
from dba_segments
[/HTML]
I like to get the output from the queries above.
DATE TIME TOTAL_GB USED_GB %USED
3/5/2013 06:00 AM 3151.24316 2330.40381 73.95
3/5/2013 12:00 PM 3151.24316 2347.41551 74.49
3/5/2013 06:00 PM 3151.24316 2349.76498 74.56
.....
[/HTML]
how I can format.
I'm combining three queries using the UNION operator.
for ex:
select 'a' from DUAL
union
select 'd' from DUAL
[Code]....
I would like to see the same order as
a
d
b
c
but it is showing as
a
b
c
d
is it possible without using ORDER BY clause?
I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?
Let say i have view that contain salaries of 2011
Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.
if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.
I am using oracle RAC 11g R2
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].....
We have very large table having data more than 1000 millions rows. We divide this table into four physical tables say A, B, C and D. The physical horizontal partition of data of this original table is done based upon their business policy.
Each partitioned table has contained data of particular business entity. Further each table has partition and sub partitions based upon business rule.
We have to retrieve data from all these tables as follows:
select a1, a2, a3, a4, a5, a6
from A
where < logical filter condition>
union all
select b1, b2, b3, b4, b5, b6
[code].....
We observed that above each query block execute in serial one after another and individual each query block capable to process data in parallel from respective table.
How does this above query able to execute each query block in parallel?
I am an Oracle newbie.
We have 2 fact tables and one lookup table in this structure:
FACTTABLE1 (C1_ID, C2, SALE)
FACTTABLE2 (C1_ID, C3, SALE)
LOOKUPTABLE (C1_ID, C1_NAME)
The DBAs have built 2 Materialized Views, which aggregates data in the fact tables at column C1 level
MAT_VIEW1 :SELECT C1_ID, SUM(SALE) SALES from FACTTABLE1 join LOOKUPTABLE on C1_ID
MAT_VIEW2: SELECT C1_ID, SUM(SALE) SALE from FACTTABLE2 join LOOKUPTABLE on C1_ID
We are using an old BI tool that can ONLY generate Inline Views in these formats.
CASE1:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE2:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, null C2_ID, C3_ID, SALE from FACTTABLE2)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE3:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1
union all
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE2
)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
Oracle 11g rewrites Case 1 and Case 2 to use the correct materialized views. But for case 3, it goes to the base fact tables 1 and 2. Is there a way to make oracle use the MVs even if there is a UNION ALL in the inline view? There is a 1:M Foreign Key relationship between LOOKUPTABLE.C1_ID and the 2 fact tables.